find question

[Gary Keramidas]

does it make sense to do a find within a find to determine some value?

i have a formula that uses index/match, but with almost 100k formulas, the calc is too long.

so basically in code, i find a value from ws1 column d (1), on ws2 column e, to get the value in column f(745).

then i use another find to find ws1 column O(331) & the found value above(745), on ws2 columns A(331) and B(745) and use some values from columns H-J to populate back on ws1.

hopefully you can see what i mean.

just not sure about the find within a find.

i have code that does it, no need to write me any code, just looking for other ideas.

 

[Guest]

Hi,

I'm not sure that I never found using Find within find methode,
For me is better using array methode to find same data or doing same work
for several process or times also I use stored template in the XLA file to
populate values

That will save memory for Excel while processing.

 

[Guest]

what I do is to use the find. the perform a test using an IF to determine if
my other conditions are met. Something like this

set c = myrange.find(what:="abc",lookin:=xlvalues)
if not c is nothing then
if cells(c.row,"C") = "Hello" then
'enter more code here
end if
end if

 

[Guest]

If your tables are sorted (or can be sorted), a possible approach (which I
believe I originally read in a book by Rob Bovey, Stephen Bullen, John Green,
and Robert Rosenberg) is to use approximate match lookups. For example:

=IF(VLOOKUP(ws1!D1, ws2!e:f, 1) = ws1!D1, VLOOKUP(ws1!D1, ws2!e:f, 2), "")

would check for an exact match before returning the value in column F. Even
though you perform 2 approximate match lookups, it's still quite a bit faster
than one exact match lookup.