CODEWITHSUNDEEP
excelvbaevaluate
Mats Olsson
Mats Olsson

Reputation: 101

VBA Evaluate function with string arguments

I have this function working ( It returns the row where the text DK001 sits in the ID range)

Found = Application.Evaluate("=IF(ID=""DK001"",ROW(ID),""x"")")

I would like to feed the searchcriteria (e.g. DK001) as a string, like

Found = Application.Evaluate("=IF(ID=SearchString,ROW(ID),""x"")")

I fail in creating a string that is accepted as a search criteria. I need your help on this! What am I doing wrong?

This Evaluate function is haunting me ....

What if I now wanted to send a value (not a string) to the function?

Found = Application.Evaluate("=IF(ID=1,ROW(ID),""x"")")

The above works!

But if I want this to be a variable like

Found = Application.Evaluate("=IF(ID=MyValue,ROW(ID),""x"")")

What then?

Upvotes: 4

Views: 17254

Answers (3)

Sancarn
Sancarn

Reputation: 2824

With stdVBA (an open source library largely maintained by myself) you can use stdLambda to accomplish this as follows:

set lambda = stdLambda.Create("if id.value = $1 then id.row else ""x""").bindGlobal("id",range("ID"))
'later...
Found = lambda("DK001")

Upvotes: 0

izzymo
izzymo

Reputation: 936

Could you try

Application.Evaluate("=IF(ID="" & searchsrtring & "",ROW(ID),""x"")")

Upvotes: -1

Alex K.
Alex K.

Reputation: 175776

Double " to include them as literals:

SearchString = "DK001"
Found = Application.Evaluate(""=IF(ID=""" & SearchString & """,ROW(ID),""x"")")

Upvotes: 6

Related Questions