Using Evaluate to complete a range of cells

[merry_fay]

I have a column of data with about 30,000 rows in it.

I need to do a lookup based on 2 criteria so I've come up with this:

Range("P2").Value = Evaluate("INDEX(Mapping!$C:$C,MATCH($G2 & $J2,Mapping!$A:$A&Mapping!$B:$B,0))")

Great

But now I need to work out how to copy it down to the rest of the rows.

I can do the P2:P -whatever the final row is bit, but I need to make G2 & J2 change according to the row it's on.

Something like:

Range("P2:P"&lst_row).Value = Evaluate("INDEX(Mapping!$C:$C,MATCH($G" & row " & $J" & row",Mapping!$A:$A&Mapping!$B:$B,0))")

I don't want to do a loop as it would take too long with the volume of data, & there's already huge amounts going on with the code.


Thanks

 

[sheetspread]

Try row()

 

[merry_fay]

Being an array, using a generic row reference kept coming back with #value, so I tried this:

Range("P2:P" & lst_row).Value = Evaluate("INDEX(Mapping!$C:$C,MATCH($G2:$G" & lst_row & " & $J2:$J" & lst_row & ",Mapping!$A:$A&Mapping!$B:$B,0))")

but I just get the answer to row 2 repeated for every row.
I've tried using RC references too but get the #value with them as well.

 

[sheetspread]

If you're really against using a loop, then try adding the formula without evaluate, then convert the range to values en masse.

 

[merry_fay]

ADVERTISEMENT

Will I need to use .FormulaArray for that as it's an array formula?
I'm just worried about how long it's going to take to run or calculate. This bit will be affecting, in the end 60,000 cells -a pretty big loop or huge amounts of calculations!

Thanks

 

[sheetspread]

Probably, and yes it might take a while. If performance is a major concern then consider changing your data structure, search method, and/or code (e.g. include a collection or dictionary).

 

[merry_fay]

ADVERTISEMENT

I can add an extra column to join the lookup values so it's only a straight lookup, but I'm open to suggestions if you know of any other ways of looking up the data -my knowledge is limited to what I've come across so far & my vba is pretty basic!!

Thanks

 

[RoryA]

INDEX doesn't always play well with arrays - you could use:

    lst_row = 31
    vdata = Evaluate("INDEX(MATCH($G2:$G" & lst_row & " & $J2:$J" & lst_row & ",Mapping!$A:$A&Mapping!$B:$B,0),)")
    Range("P2:P" & lst_row).Value = Application.Index(Sheets("Mapping").Range("C:C"), vdata, 0)

It would help if you can avoid using entire column references in the formula though.

 

[sheetspread]

I can add an extra column to join the lookup values so it's only a straight lookup, but I'm open to suggestions if you know of any other ways of looking up the data -my knowledge is limited to what I've come across so far & my vba is pretty basic!!

If Post #4 isn't working either, then instead of using a non-array formula followed by a sendkeys ctrl-shift-enter (which I'm told is a poor technique)*, you could add a helper column. Many people avoid these in favor of overstuffed formulas, but the benefits are obvious. A more advanced VBA solution would be the scripting dictionary where you assign a key and value in the code, derived from your worksheet, similar to a lookup or index/match table.

Rory's idea sounds good though, so it may be enough for you. (and yes adding a lastrow variable is preferable to just B:B, C:C etc)



*You can also convert a nonarray formula to an array with .formulaarray (I think)

 

[merry_fay]

Thanks Rory, unfortunately it's only working in the instances where column J doesn't have a value though. Where both G & J have a value, I'm getting #N/A.

That's interesting on the columns for formula. I always thought it was best practice to reference a whole column in case some idiot deletes lots of rows. I guess in the code it doesn't matter, but for general spreadsheet use, is it that much quicker it's worth the risk?


sheetspread, I agree on the array formulae, I usually avoid them altogether & join columns, I'm just trying to be clever & try something new & different in vba.
Unfortunately I know nothing about the scripting dictionary. Can you recommend any good websites where I can read up on it?

I'll try post #4 now -I tried a loop & gave up after 10 minutes!!

Thanks