excel find the value of the source cell when using vlookup

Question

I am trying to create a workbook that allows me to pull data from a source workbook to create a "snapshot" of various metrics on testing data. I accomplished this with vLookUp.

My goal however; is that I would like to create an update button and field so that I can modify cells on the snap shot and update them on the source worksheet. (all in the same workbook)

I have tried to use an update button with a Marco, but it only updates one cell (not the variable range of cells that could be pulled from the named list using vLookUp.) I have not used VBA or Marcos in Excel in probably 4 years, so I may be missing something simple. I have been doing some refresh training, but haven't been able to figure it out.

I tried to just modify a Macro I recorded:

  Range("G8").Select
    Selection.Copy`enter code here`
    Sheets("Dashboard").Select
    Range("K3").Select
    ActiveSheet.Paste

WITH

Sub Update_Click()
    Dim varRange As Range
    varRange = "=VLOOKUP(C5,Dashboard!A3:W57,11,FALSE)"
    Range("G8").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("varRange").Select
    ActiveSheet.Paste
End Sub

Another alternative would be to create a new column that could identify the column and row of source cell that vLookUp is referencing.(not the named list cell, I need the cell that is being referenced in the table array of vLookUp) Then I should be able to use that reference cell with the original macro (I think)

I have tried using =CELL("col",VLOOKUP(C5,Dashboard!A3:W57,11,FALSE)) but it gives an error.

Initial code was off: Selection.Copy Sheets("Dashboard").Select — Mikeyg229
try MATCH funciton. It will return row / column of matched data in range. — Scott Holtzman
also Dim varRange as Range makes no sense with varRange = "=VLOOKUP(C5,Dashboard!A3:W57,11,FALSE)". You cannot use a Range object in this way, — Scott Holtzman
Set varRange = Sheets("Dashboard").Range("W" & Application.Match(ActiveSheet.Range("C5"),Sheets("Dashboard").Range("A3:A57"),0)) Then the rest in one line ActiveSheet.Range("G8").Copy varRange. You probably will want to put a check in that the value exists if you are not using data validation on C5. — Scott Craner
Sorry the long line should be Set varRange = Sheets("Dashboard").Range("W" & Application.Match(ActiveSheet.Range("C5"),Sheets("Dashboard").Range("A:A"),0)‌) — Scott Craner

Sgdva Sgdva · Accepted Answer · 2016-06-24T14:49:47

You should do a find of the value found by your formula

Sub Update_Click()
    Dim LookedValue As String
    Dim varRange As Range
    LookedValue = Evaluate("=VLOOKUP(C5,Dashboard!A3:W57,11,FALSE)")
    Set varRange = Range("A3:W57").Find(LookedValue)
    Range("G8").Copy Destination:=Sheets("Dashboard").Range(varRange.Address)
End Sub

OT: I think you are a little confused, you should search how range works; "Evaluate" -the function used in lookedvalue- tips and how to avoid select.

excel find the value of the source cell when using vlookup

1 Answers