Simplify macro, make it dynamic

Hi

I have a table in the range A4:W40. I recorded a macro that enters a VLOOKUP formula into cells U4, V4 and W4 then fills them down to row 22. I need to change this macro because sometimes I need the macro to fill down to row, 30, sometimes to row 20. It all depends on the values contained in column A. The first few rows in column A will all contain the text 'SG', the rest will be 'AU'. I only need the macro to fill down where the value in column A is SG.

The macro:

Please Login or Register  to view this content.

Also, I'm sure that this macro is probably a little convoluted and could be a little more elegant.

If anyone can offer any help with this, I'd be very appreciative.

Cheers!

Hi Millz

Thanks very much for your help!

The macro works almost perfectly but it fills down one row too far. For example, I've got SG entries down to row 22 but it fills down the formula to row 23. I tested it by changing around entries in column A and each time, it filled down one row too far. Do you know why this might be happening?

Again, really appreciate your input.

Cheers

Works perfectly, Millz! Really appreciate it, mate. Thank you!

Millz is way ahead of me but since I had it ready I thought I might as well give you another option (no looping).

Please Login or Register  to view this content.

Thanks Jolivanes! Both work really well.

Good luck with either one