Vlookup contained within VBA code.

[galvinpaddy]

Good morning Happy Campers.

I have experience with both VBA (basic) & Vlookups (good), but would like to know how to combine them.

I get the vlookup & how it works (reference cell, data range, column # & the exact/near match)

Please see attached
SHEET1
Range - A:F contains the range named 'Data' used in J:O
Range - J:O contains the cells with Vlookup manually entered
Range - Q:V contains the range where i would like VBA to do the calculation, and enter only the values from the result instead of the formula.

SHEET2
Range - A:F contains the range named 'Data1' used in J:O
Range - J:O contains the cells with Vlookup manually entered
Range - Q:V contains the range where i would like VBA to do the calculation, and enter only the values from the result instead of the formula.

I would also like to know how to write the code so it would do the calculation on both sheets, upon calculating the results, it would enter the value only.
Then, to build on that i would need to know how to get the code to ignore cells that contained information, and focus only on blank cells.
Both sheets can potentially be hundreds of rows long.

The reasoning behind my question is i have a very large workbook with thousands of rows across 5 tabs, and i would like to reduce the amount of calculations each time an entry is made, the answer to the above wont solve my issue, but will greatly improve my understanding of how to do it? (i may also be back with more questions lol)

Kind Regards
galvinpaddy

[JapanDave]

What about something like this in each worksheet module?

Please Login or Register  to view this content.

[galvinpaddy]

Hi JapanDave,
Having tested your code i find it works, however if i enter info into any cell it then runs the code and produces a Runtime error '91', (i also cant then delete that entry as i get the same error again.) the error message also reads Object variable not set, which highlights the line

Please Login or Register  to view this content.

in yellow.

If possible, can you help me with understanding how to assign this code to a button instead of a change?

regards.

[galvinpaddy]

The button i can do, its just the coding i need support on.