Excel search for "text" and the formula Topic is solved

[Jazzman]

Hi guys,

I have this command to search for in excel but it search for formula (for example =Data!$F15) rather than text that the cell contain ( for example "John")

Code: Select all

LookFor := % Clipboard
if (Row := WB.Range("A:C").Find(LookFor).Row)

[Mod Edit: [code][/code] tags added]

Anyone knows how to set parameter to search for the text?

Thank you very much in advance.

[TLM]

You may not need arguments after -4163 ( constant: xlValues ) but try this:

Code: Select all

LookFor := Clipboard ; removed %
if ( Row := WB.Range("A:C").Find(LookFor,WB.Cells(1),-4163,2,1,1,0,0).Row )

htms

[Jazzman]

You may not need arguments after -4163 ( constant: xlValues ) but try this:

Code: Select all

LookFor := Clipboard ; removed %
if ( Row := WB.Range("A:C").Find(LookFor,WB.Cells(1),-4163,2,1,1,0,0).Row )

htms

Thank you so much, that works great, I was googling for long time trying to figure it out.

[sinkfaze]

Just to further clarify on TLM's solution, if you don't specify where you want to look in the LookIn parameter of Find(), Excel will check everything; if a cell in the range has a formula, it's going to search the text of the formula and the result of the formula (the value). If you want it to search only the values then you have to explicitly restrict it with the xlValues constant (-4163).

[TLM]

omg @sinkfaze



I actually wasn't aware that the lookin param was optional, ty
#TheMoreYouKnow