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
– Mats Olsson, Commented Feb 27, 2015 at 17:13

Alex K. · Accepted Answer · 2015-02-27 14:20:40Z

6

Double " to include them as literals:

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

answered Feb 27, 2015 at 14:20

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mats Olsson Over a year ago

Thanks! It works! I was trying to do the same, but inside the search string, but that failed. Appreciate your help and quick answer!

Sancarn · Accepted Answer · 2021-11-10 14:52:56Z

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, 2021 at 14:52

answered Apr 9, 2021 at 15:10

Sancarn

2,93426 silver badges54 bronze badges

Comments

izzymo · Accepted Answer · 2015-02-27 14:27:14Z

-1

Could you try

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

answered Feb 27, 2015 at 14:27

izzymo

9361 gold badge10 silver badges14 bronze badges

2 Comments

Mats Olsson Over a year ago

Will try this too, wait!

Mats Olsson Over a year ago

Nope! Get a Error 2029 back on this. But Alex suggestion worked. Thanks to you too for trying to help out!

Collectives™ on Stack Overflow

VBA Evaluate function with string arguments

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related