Fix Formula in Vlookup part

[NatalieW12]

Hi All,

This is something that I was working on yesterday and Jindon helped me with his code, so this is still based on his code. I was initially only after the max range, I thought now I would try it on the Vlookup part, but I am having trouble working this out. The “Set SCR = Range("B20:D20")” is to take into account the max column (B) and the two lookup columns (C and D). I have omitted the 2nd lookup code as it is basically the same as the 1st lookup part.

I use similar code for other data so I am trying to set it up so that I only have to change the LastRow (LR), FirstCell (FR) and the Second Cell (SCR). Is it worth it, probably not, will it work, I don’t know but I thought I would give it a go anyway.



This the max formula I get in “E6” when I run the code
=MAX(B20:B500)

This is what I get (WRONG) in cell “F6”
=(VLOOKUP(MAX(B20:B500), B20:B500,2,FALSE))

when it should be this - “B20:D500”
=(VLOOKUP(MAX(B20:B500), B20:D500,2,FALSE))

Please Login or Register  to view this content.

Thanks
Nat

[jindon]

Try change to

Please Login or Register  to view this content.

[NatalieW12]

Thank you Jindon,

I was close, but not close enough - I should have picked that up.

Nat

[mohadin]

Hi
Or may

Please Login or Register  to view this content.

[NatalieW12]

Thanks mohadin, this works as well

[jindon]

Thanks mohadin, this works as well

No, it is just an accidentally...
should be

Please Login or Register  to view this content.