-
May 12th, 2009, 10:03 AM
#1
[RESOLVED] Help needed with InStr function
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:
Code:
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
-
May 12th, 2009, 10:21 AM
#2
Re: Help needed with InStr function
It looks like you have swapped the String1 and String2 parameters of the InStr function as stated here, it should be :
Code:
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 :
Code:
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
-
May 12th, 2009, 10:47 AM
#3
Re: Help needed with InStr function
Godd point on the Else statement, I did not think of that. The issue I am having is with the following line.
Code:
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.
-
May 12th, 2009, 10:53 AM
#4
Re: Help needed with InStr function
Isn't WrkSht supposed to declared like this :
Code:
Dim WrkSht As Worksheet
¿
-
May 12th, 2009, 11:06 AM
#5
Re: Help needed with InStr function
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:
Code:
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.
-
May 12th, 2009, 11:17 AM
#6
Re: Help needed with InStr function
Thank you for your help. I solved it with the following code:
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
-
May 12th, 2009, 11:25 AM
#7
Re: Help needed with InStr function
Glad you got it solved
Good work!
-
May 12th, 2009, 12:20 PM
#8
Re: [RESOLVED] Help needed with InStr function
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'.
Code:
If InStr(1, retval, arrWords(i), vbTextCompare) > 0
Any thoughts on how to accomplish this?
-
May 12th, 2009, 09:39 PM
#9
Re: [RESOLVED] Help needed with InStr function
convert the word to lowercase first, then compare to your lower case word
-
May 13th, 2009, 02:30 AM
#10
Re: [RESOLVED] Help needed with InStr function
The vbtextcompare option makes it ignore case. The line of code in post #8 should work fine.
-
May 13th, 2009, 03:18 AM
#11
Re: [RESOLVED] Help needed with InStr function
For performance purposes, converting the string to find to Lowercase first, before using InStr, will perform faster. So, I'd do it like :
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|