VBA Evaluate function with string arguments

Question

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?

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? — Mats Olsson, Feb 27 '15 at 17:13

score 6 · Accepted Answer · answered Feb 27 '15 at 14:20

6

Double " to include them as literals:

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

answered Feb 27 '15 at 14:20

Alex K.

Thanks! It works! I was trying to do the same, but inside the search string, but that failed. Appreciate your help and quick answer! – Mats Olsson Feb 27 '15 at 14:36

Sancarn · Answer 2 · 2021-11-10T14:52:56.957

0

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")

edited Nov 10 '21 at 14:52

answered Apr 09 '21 at 15:10

Sancarn

score -1 · Answer 3 · answered Feb 27 '15 at 14:27

-1

Could you try

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

answered Feb 27 '15 at 14:27

izzymo

Will try this too, wait! – Mats Olsson Feb 27 '15 at 14:38
Nope! Get a Error 2029 back on this. But Alex suggestion worked. Thanks to you too for trying to help out! – Mats Olsson Feb 27 '15 at 14:41

3 Answers3