validating a word against a list of other words in excel

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Hi besafe,

unfortunately I don't know what your list looks like, you could use several approaches. The easiest of which would likely use pos = InStr(start, string, substring).

So say you have a list of word such as "Hello", "World", "I", "was" and "here", I would simply add the all together with a delimiter,

e.g. myString = "," & "Hello" & "," & "World" & "," & "I" & "," & "was" & "," & "here" & ","

and I am looking for "was"

Then my Instr call would look like

pos = Instr(1, myString, ",was,")

which should return 15

as 15 > 0 this meas that the ",was, " was found and 15 is the start position of the match in the string.
Note: I added the comma delimiters before and after to make sure I'm not simply finding part of the string

e.g. if the string was ",I,am,a,swashbuckler," we need to ensure that we don't match on "swashbuckler".

If you have multiple cells each with it's own string and you wish to match against any of those strings then you could simply check each and match against your string.

e.g. I'm looking for a string in column 1, rows 5 -> 9

Sub forBeSafe()
myString = "Hello"
For i = 5 To 9
If Sheet1.Cells(i, 1).Value = myString Then
MsgBox "Found " & Chr(34) & myString & Chr(34) & " at Cell (" & i & ",1)"
Exit Sub
End If
Next i
End Sub

The above are Case sensitive, you caould UCase everything (both side of the If =) to make it case insensitive
i.e.
:
:
If UCase(Sheet1.Cells(i, 1).Value) = UCase(myString) Then
:
:

Cheers,
Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

hello Tom,

thanks for the answer, to give some more insight i will show you how my sheet looks like:
sheet1:
A B
xx name
xx name

and so on.

i then want to make a script which checks a list which looks like:

xxx yyy name
xxx yyy name

i was wondering if it is possible to make a script which looks at the name and then checks if the name on the list match one of the names in my sheet, and if it matches then it should go and look on the line below until it finds a name which isn't on my list.

i hope this makes sense and that you would look at this question again

thank beforehand :D

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Hi besafe,

so maybe something like this?

Before:                                        After:
                 

Here is my macro:
Sub test()
    'Find the last row in the spreadsheet
    LastRow = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
    'MsgBox LastRow

    'Code to retrieve the strings you are interested in from Rumba..
    Dim MyString(2) As String
    MyString(0) = "BeSafe"
    MyString(1) = "David"
    MyString(2) = "Tom"

    Dim FoundMatchList() As String
    ReDim FoundMatchList(LastRow)

    'Code to find matches of each strings in the MyString array
    For i = 0 To UBound(MyString)
        For j = 1 To LastRow
            If UCase(Sheet1.Cells(j, 2).Value) = UCase(MyString(i)) Then
                'MsgBox "Found Match: " & MyString(i)
                FoundMatch = 1
                FoundMatchList(j - 1) = MyString(i)
            ElseIf j = LastRow Then
                'MsgBox MyString(i) & " not found"
            End If
        Next j
    Next i

    'Insert a column between Column 1 and Column 2 if a match was found
    If FoundMatch = 1 Then
        FoundMatchList(0) = "MatchFound"
        Columns(2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        'Insert matches
        For i = 1 To LastRow
            Sheet1.Cells(i, 2).Value = FoundMatchList(i - 1)
        Next i
        'AutoFit new Column
        Sheet1.Columns(2).EntireColumn.AutoFit
    End If
End Sub

Cheers,
Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Hi besafe,

so maybe something like this?

Before:                                        After:
                 

Here is my macro:
Sub test()
    'Find the last row in the spreadsheet
    LastRow = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
    'MsgBox LastRow

    'Code to retrieve the strings you are interested in from Rumba..
    Dim MyString(2) As String
    MyString(0) = "BeSafe"
    MyString(1) = "David"
    MyString(2) = "Tom"

    Dim FoundMatchList() As String
    ReDim FoundMatchList(LastRow)

    'Code to find matches of each strings in the MyString array
    For i = 0 To UBound(MyString)
        For j = 1 To LastRow
            If UCase(Sheet1.Cells(j, 2).Value) = UCase(MyString(i)) Then
                'MsgBox "Found Match: " & MyString(i)
                FoundMatch = 1
                FoundMatchList(j - 1) = MyString(i)
            ElseIf j = LastRow Then
                'MsgBox MyString(i) & " not found"
            End If
        Next j
    Next i

    'Insert a column between Column 1 and Column 2 if a match was found
    If FoundMatch = 1 Then
        FoundMatchList(0) = "MatchFound"
        Columns(2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
        'Insert matches
        For i = 1 To LastRow
            Sheet1.Cells(i, 2).Value = FoundMatchList(i - 1)
        Next i
        'AutoFit new Column
        Sheet1.Columns(2).EntireColumn.AutoFit
    End If
End Sub

Cheers,
Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

hello Tom,

thanks for all the help, however i think that i didn't explain my intention with this porbably. if the text that my code finds matches the text in my sheet then i would like it to jump down one row and read agian until it finds something that doesn't match anything in my sheet.

when it finds something which doens't match then i should copy that word and write it some other place

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Hi besafe,

so like this?

Sub test()
'Find the last row in the spreadsheet
LastRow = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
'MsgBox LastRow

'Code to retrieve the strings you are interested in from Rumba..
Dim MyString(2) As String
MyString(0) = "BeSafe"
MyString(1) = "David"
MyString(2) = "Tom"

Dim FoundMatchList() As String
ReDim FoundMatchList(LastRow - 1)

'Code to find matches of each strings in the MyString array
For i = 0 To UBound(MyString)
For j = 1 To LastRow
If UCase(Sheet1.Cells(j, 2).Value) = UCase(MyString(i)) Then
'MsgBox "Found Match: " & MyString(i)
FoundMatchList(j - 1) = MyString(i)
ElseIf j = LastRow Then
'MsgBox MyString(i) & " not found"
End If
Next j
Next i

'Variable to flag is we have any cells with no match.
Dim NoMatch As Boolean
NoMatch = False

'Variable to flag if we have added a column.
Dim ColAdded As Boolean
ColAdded = False

'Set the Column Title
FoundMatchList(0) = "Not Found"

'Identify the no matches and note in our FoundMatchList() array
For i = 1 To UBound(FoundMatchList)
If FoundMatchList(i) = "" Then
If NoMatch = False Then
Columns(2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
NoMatch = True
ColAdded = True
End If
FoundMatchList(i) = "No Match"
End If
Next i

'Add "No Match" flags to the spreadsheet
If ColAdded = True Then
For i = 1 To LastRow
If FoundMatchList(i - 1) = "No Match" then
Sheet1.Cells(i, 2).Value = FoundMatchList(i - 1)
End If
Next i
'AutoFit new Column
Sheet1.Columns(2).EntireColumn.AutoFit
End If
End Sub

Cheers,
Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

hello tom,

is it possible to reverse this function?

normally i been using:

MyString = "KONKMHL,COGNINZ,AAR26GS,ARHKDEJ,KOLKEVP,PAKKJDA,ARHKMAK,VIBKJYF,ARHKKSL,ARHKMAD,PAKKLOO,ARHKHEK,KOLKGUG,PAKKSUJ,ARHKMBA,ARCKJEK,AUTO1HL,AUTO1GR,KOMPBKA,BALKSLO,MASKINE,KONB ,KONL ,KONK "
for i = 11 to 16
emreadscreen status13,7,i,30
if InStr(1, MyString, status13) = 0 then
emreadscreen opret,7,i,30

this is to check and make sure that the word i find is not on the list, however now im in the situation that i need to do the exact opposite.

i want to make sure that the word that it is reading is on the list. i tried to do as seen below:

MyString = "KONKMHL,COGNINZ,AAR26GS,ARHKDEJ,KOLKEVP,PAKKJDA,ARHKMAK,VIBKJYF,ARHKKSL,ARHKMAD,PAKKLOO,ARHKHEK,KOLKGUG,PAKKSUJ,ARHKMBA,ARCKJEK,AUTO1HL,AUTO1GR,KOMPBKA,BALKSLO,MASKINE,KONB ,KONL ,KONK "
for i = 11 to 16
emreadscreen status13,7,i,30
if InStr(1, MyString, status13) = 1 then
emreadscreen opret,7,i,30

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Hi besafe,

Instr will return the start position of the sub-string which you are looking for, and 0 is the sub-string is not in the big long string.

If you change your

if InStr(1, MyString, status13) = 1 then

to
if InStr(1, MyString, status13) > 0 then

this will trigger if the sub-string is in the big long one..

or you could have used an "else"

if InStr(1, MyString, status13) = 0 then
Msgbox "Boo!!"
else
emreadscreen opret,7,i,30

:-)

Cheers,
Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

thanks tom, unfurtunately i couldn't get it to work for me however i solved it another way so everything is fine :D

if possible can you maybe help me with another problems which i am now dealing with?

i want to make sure that the logfile which i am making i being saved with the date and time of the day that the script is done working, do you have any idea of how to make it work? i tried a couple of things but everything was way of...

hope to hear from you, thanks beforehand

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

so something like this?

Sub Main
Dim MyString as String
MyString = Cstr(Now)

For i = 1 to Len(MyString)
If Mid(MyString, i, 1) = "/" then
MyString = Left(MyString, i-1) & "." & Right(MyString, Len(MyString)-i)
ElseIf Mid(MyString, i, 1) = ":" then
MyString = Left(MyString, i-1) & "_" & Right(MyString, Len(MyString)-i)
End If
Next i
FileCopy "c:\\temp\\tom.txt", "c:\\Temp\\" & myString & ".log"
End Sub

That should give you the Date with a "." seperator following by a space and then the time with an "_" seperator and then a .log extension.

e.g.
01.06.2018 12_36_54.log

Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

so something like this?

Sub Main
Dim MyString as String
MyString = Cstr(Now)

For i = 1 to Len(MyString)
If Mid(MyString, i, 1) = "/" then
MyString = Left(MyString, i-1) & "." & Right(MyString, Len(MyString)-i)
ElseIf Mid(MyString, i, 1) = ":" then
MyString = Left(MyString, i-1) & "_" & Right(MyString, Len(MyString)-i)
End If
Next i
FileCopy "c:\\temp\\tom.txt", "c:\\Temp\\" & myString & ".log"
End Sub

That should give you the Date with a "." seperator following by a space and then the time with an "_" seperator and then a .log extension.

e.g.
01.06.2018 12_36_54.log

Tom

Hello,

 

is it possible to validate a word that you find, with a list of words in an excel sheet?

 

if it is possible can you then maybe show how the code should look?

 

i will be gratefull for any help that i can get.

#Rumba

Sub Main
Dim MyString as String
MyString = Format(Now, "yyyy-mm-dd-hh-nn")
FileCopy "c:\\temp\\tom.txt", "c:\\Temp\\" & myString & ".log"
End Sub

Will do it in a simple way as well the files will be easily sorted by name which will give you date sorting