Target.address and VLOOKUP with a range of cells

[adamchater]

Hi, I have the following code, which I know is very inefficient and I want to make it much better.

I have a column of dynamic values in cells AO9 to AO104, and what I need to happen is that when one of the cells in that range is clicked on, Excel performs a VLOOKUP against the value in the cell, copies the results of the VLOOKUP to a cell on another sheet, then activates that sheet.

This is achieved currently by using the code below, although the example given only covers cells AO9 and AO10. Each five lines of code would be repeated for all the cells between AO9 to AO104 (I haven't actually done this yet as I know this not a good way to achieve this), and the only thing which would change is that in the second line the range value is increased by 1 each time. The formula that's being referenced in the second line is just a VLOOKUP, which looks up the value of the cell that was clicked on in a range called ConsData2 and returns the value in the 70th column. This value is then copied to another sheet, in cell W19, and that sheet activated.

If Target.address = "$AO$9" Then
    CurrentConsultant = Worksheets("Formulas").Range("B256").Value
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

If Target.address = "$AO$10" Then
    CurrentConsultant = Worksheets("Formulas").Range("B257").Value
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

I know there must be a much more efficient way of doing this, without repeating the section of code for each cell, and also without doing a VLOOKUP for each cell separately too, but I cannot work out how to do this.

Could someone please kindly advise me how to make this better?

 

[adamchater]

Just to add, any solution needs to work in Excel 2010 please, as that's what most of our users are using, with only a few using Excel 365.

 

[RoryA]

Something like:

If Not Intersect(Target, Range("AO9:AO104")) Is Nothing then
    CurrentConsultant = Worksheets("Formulas").Range("B" & Target.Row + 247).Value
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

 

[adamchater]

Something like:

If Not Intersect(Target, Range("AO9:AO104")) Is Nothing then
    CurrentConsultant = Worksheets("Formulas").Range("B" & Target.Row + 247).Value
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

Thank you, that would work, but ideally the VLOOKUP part could be dealt with as part of this too. So instead of the second line referencing the VLOOKUP on the Formulas sheet, is it possible to code the VLOOKUP too please?

 

[RoryA]

Probably, but I have no idea what your VLOOKUP formula is...

 

[adamchater]

Probably, but I have no idea what your VLOOKUP formula is...

Ah yes, that's a good point! I did hint at it in my original post, but here's what actually is:

=VLOOKUP(Specialities!AO9,ConsData2,70,FALSE)

It looks up the cell that was clicked on against a range called ConsData2, and returns the 70th column in that range.

 

[RoryA]

Try this:

If Not Intersect(Target, Range("AO9:AO104")) Is Nothing then
    CurrentConsultant = Application.VLOOKUP(Target, Application.Range("ConsData2"), 70, FALSE)
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

 

[adamchater]

Try this:

If Not Intersect(Target, Range("AO9:AO104")) Is Nothing then
    CurrentConsultant = Application.VLOOKUP(Target, Application.Range(ConsData2), 70, FALSE)
    Worksheets("Consultants").Range("W19") = CurrentConsultant
    Sheets("Consultants").Activate
End If

Thank you, this worked, although I had to put quote marks around the name of the range (ConsData) for it to work. Many thanks for all your help with this.

 

[RoryA]

Oops - yes I missed those when amending the formula. I've added them to my post for posterity