Page 1 of 1
Excel search for "text" and the formula
Posted: 08 Jan 2020, 06:30
by 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)
Anyone knows how to set parameter to search for the text?
Thank you very much in advance.
Re: Excel search for "text" and the formula Topic is solved
Posted: 08 Jan 2020, 08:21
by 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
Re: Excel search for "text" and the formula
Posted: 08 Jan 2020, 08:37
by Jazzman
TLM wrote: ↑08 Jan 2020, 08:21
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.
Re: Excel search for "text" and the formula
Posted: 08 Jan 2020, 09:03
by 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).
Re: Excel search for "text" and the formula
Posted: 08 Jan 2020, 09:35
by TLM
omg
@sinkfaze 
I actually wasn't aware that the
lookin param was optional, ty
#TheMoreYouKnow
