[RESOLVED] Help needed with InStr function

[Mekinnik]

I am trying to search within 'retval' for any of the values list in the column range A1:A1145. Can anyone help with this, I cannot put my finger on what it is I am doing wrong. Here is my function:

Public Function AcrNimTst(ByVal retval As String) As Boolean
    '************************************************
    'Function Name: AcrNimTst
    'Returns true or false if 'password' meets the business rule.
    'Description: This class tests the string 'password' against
    'the word exclusion database business rule
    'Parameter is 'password' as string
    '************************************************
Dim Olist As Workbook
Dim WrkSht()
Dim i As Integer

WrkSht = Olist.Sheet(2).Range("A1:A1145")
For i = 1 To 1145
If InStr(1, retval, WrkSht(i), vbTextCompare) > 0 Then
AcrNimTst = False
ElseIf InStr(1, retval, WrkSht(i)) = 0 Then
AcrNimTst = True
End If
Next i
End Function

[HanneSThEGreaT]

It looks like you have swapped the String1 and String2 parameters of the InStr function as stated here, it should be :

Public Function AcrNimTst(ByVal retval As String) As Boolean
    '************************************************
    'Function Name: AcrNimTst
    'Returns true or false if 'password' meets the business rule.
    'Description: This class tests the string 'password' against
    'the word exclusion database business rule
    'Parameter is 'password' as string
    '************************************************
Dim Olist As Workbook
Dim WrkSht()
Dim i As Integer

WrkSht = Olist.Sheet(2).Range("A1:A1145")
For i = 1 To 1145
If InStr(1, WrkSht(i), retval, vbTextCompare) > 0 Then
AcrNimTst = False
ElseIf InStr(1, WrkSht(i), retval) = 0 Then
AcrNimTst = True
End If
Next i
End Function

What puzzles me though is your If ElseIf statement, why is it set up like that, couldn't you just do :

Public Function AcrNimTst(ByVal retval As String) As Boolean
    '************************************************
    'Function Name: AcrNimTst
    'Returns true or false if 'password' meets the business rule.
    'Description: This class tests the string 'password' against
    'the word exclusion database business rule
    'Parameter is 'password' as string
    '************************************************
Dim Olist As Workbook
Dim WrkSht()
Dim i As Integer

WrkSht = Olist.Sheet(2).Range("A1:A1145")
For i = 1 To 1145
If InStr(1, retval, WrkSht(i), vbTextCompare) > 0 Then
AcrNimTst = False
Else
AcrNimTst = True
End If
Next i
End Function

[Mekinnik]

Godd point on the Else statement, I did not think of that. The issue I am having is with the following line.

WrkSht = Olist.Sheet(2).Range("A1:A1145")

When I step through the code, it throws a 'Run-Time error 91' Object variable or with block variable not set.

[HanneSThEGreaT]

Isn't WrkSht supposed to declared like this :

Dim WrkSht As Worksheet

¿

[Mekinnik]

That does not help, still get the same error. Here is an overview, I am trying to search within 'retval' for any of the values listed in column A rows 1 to 1145. If one is found, the function will then return false.

This is what I believe the line should look like:

If InStr(1, .RANGE(A1:A1145).VALUE, retval, vbTextCompare) > 0 Then

Or something like that. The problem I am facing is how to reference the range within the InStr function.

[Mekinnik]

Thank you for your help. I solved it with the following code:

Dim arrWords(1 To 1145)
Dim i As Integer

For i = 1 To 1145
arrWords(i) = Sheets(2).Range("A" & i + 1).Value
If InStr(1, password, arrWords(i), vbTextCompare) > 0 Then 'need to make search not case sensitive
AcrNimTst = False
Else
AcrNimTst = True
End If
Next i
End Function

[HanneSThEGreaT]

Glad you got it solved
Good work!

[Mekinnik]

I was to quick on marking this resolved. Another issue has come up. How can I make the InStr function not case sensitive. I am looking to search within 'retval' which is a random string for any form of the three letter words that are listed in the column range. So within the column listing is the word 'run', I want the InStr function (if possible) to search for all forms of the word 'run'(Run,rUn,ruN,run,RuN,RUn,rUN) within 'retval'.

If InStr(1, retval, arrWords(i), vbTextCompare) > 0

Any thoughts on how to accomplish this?

[dglienna]

convert the word to lowercase first, then compare to your lower case word

[DataMiser]

The vbtextcompare option makes it ignore case. The line of code in post #8 should work fine.

[HanneSThEGreaT]

For performance purposes, converting the string to find to Lowercase first, before using InStr, will perform faster. So, I'd do it like :

Dim arrWords(1 To 1145)
Dim i As Integer

For i = 1 To 1145
arrWords(i) = LCase$(Sheets(2).Range("A" & i + 1).Value)
If InStr(1, password, arrWords(i), vbTextCompare) > 0 Then 'need to make search not case sensitive
AcrNimTst = False
Else
AcrNimTst = True
End If
Next i
End Function