if statement condition?

[vbaNewby]

Hello all, I have the following if statement:

If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell & " ", " " & "MyText" & " ")) = True _
then
     someVariable = 1

The isNumber function returns true or false. I know this should return true but it is not catching the condition in the if statment.

Any ideas?

Edit: Also tried to output the value of the function with a msgbox and got back nothing.

 

[bensonsearch]

hi,
try putting the worksheet function into a variable

dim asd as variant
 
asd = Application.WorksheetFunction.Search(" " & ActiveCell & " ", " " & "MyText" & " ")
'can test here by
'msgbox(asd)
 
if asd = "true" then
 
someVariable = 1end if

 

[vbaNewby]

hi,
try putting the worksheet function into a variable

dim asd as variant
 
asd = Application.WorksheetFunction.Search(" " & ActiveCell & " ", " " & "MyText" & " ")
'can test here by
'msgbox(asd)
 
if asd = "true" then
 
someVariable = 1end if

Worked like a charm benson. Thank you so much. One more question, I run a bunch of these checks, is there a way to not have to create a ton of variables and then an if statment to check them....?

So for example, I had this:

 If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell & " ", " " & "W" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "AWord" & " ")) = True _        
              Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "AnotherWord" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word3" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word4" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word5" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word6" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word7" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word8" & " ")) = True _
                Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word9" & " ")) = True _
           '     Or Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & ActiveCell.Value & " ", " " & "Word10" & " ")) = True _

And I would have to turn it into this:

Dim a,b,c,d,e,f,g,h,i,j As Variant
' then set all the variables to Application.WorksheetFunction.....
' then write a long if statment again.
if a or b or c or d or f or g or h or i or j then
      myVar = 1
end if

Wondering if there is an easier way to do this without so much repetition in the code and creating a bunch of variables like that.

Thanks so much for the reply!

 

[bensonsearch]

Hi,
not really any way i can think of cutting it down.

im sure someone here could but i find it better to have it long so you know what is happening.

could use an array, setting each value manually then use the below for the if statement

dim count as long
 
count = 0
 
do until count = 11
if myarray(count) = "true" then
'do stuff
else
'other
end if
count = count + 1
loop

 

[Marcelo Branco]

Maybe something like

Sub test1()
    Dim arr As Variant, found As Long
 
    arr = Array("AWord", "AnotherWord", "Word3", "Word4", "Word5", _
        "Word6", "Word7", "Word8", "Word9", "Word10", "Word11")
 
    On Error Resume Next
    found = Application.Match(ActiveCell.Value, arr, 0)
    On Error GoTo 0
 
    If found <> 0 Then
        MsgBox found
        'Do your stuff
    Else
        MsgBox "Not Found"
        'Do another stuff
    End If
 
End Sub

M.