Nested Excel Functions in VBA

[danbrawl93]

Hello!

I am struggling to use the following nested function (written as if it was in a cell) in VBA:

=Iserror(Search("Hello",A1,1))

Essentially, I want the formula to produce FALSE if the cell contains the word "Hello" (there may be other words). This would be in a dynamic loop and the VBA I have tried is...

For n = z To y
If Evaluate("Iserror(search(""Hello"", cells(n,1), 1))") = True Then
Do stuff and end loop etc.

I have tried using WorksheetFunction but to no avail. I also cannot get the following to work, so I think the issue lies with how I am nesting the functions?

Range("A3").Value = Evaluate("sum(""I4:I6"",product(""K4:K6""))")

Please help, both with general syntax and the code highlighted in bold text!

Thanks

 

[FormR]

Hi welcome to the board!

For n = z To y
If Evaluate("Iserror(search(""Hello"", cells(n,1), 1))") = True Then
Do stuff and end loop etc.

Might be better to use the native VBA function for this, i.e.

For n = z To y
    If InStr(1, Cells(n, 1).Value, "hello", vbTextCompare) = 0 Then
        'Cell does not contain hello
    End If
Next n

Range("A3").Value = Evaluate("sum(""I4:I6"",product(""K4:K6""))")

For this you could try:

Range("A3").Value = Evaluate("sum(I4:I6,product(K4:K6))")

 

[Tetra201]

If you insist on using Evaluate, try this:

If Evaluate("Iserror(Search(""Hello""," [COLOR=#ff0000]& Cells(n, 1).Address &[/COLOR] ", 1))") = True Then ...

 

[danbrawl93]

Sorry, I was a little confusing in my question - I meant to find the cell containing "Hello" (i.e. ISERROR would produce FALSE). Your solutions were both excellent though, thanks so much!! I will definitely be using this forum from now on.

 

[FormR]

Glad we could help!

I meant to find the cell containing "Hello" (i.e. ISERROR would produce FALSE).

In that case with the instr() test you would use:

If InStr(1, Cells(n, 1).Value, "hello", vbTextCompare) > 0 Then

 

[Tetra201]

... I meant to find the cell containing "Hello" (i.e. ISERROR would produce FALSE).

The Range.Find method works faster than checking cells one by one. Here is an example of using it:

Sub Test()
    Set c = [A1:A222].Find("Hello", [A222], xlValues, xlWhole)
    If Not c Is Nothing Then MsgBox c.Address
End Sub