Replacing cell contents with the lookup value of that cell

[rossross]

I'm at a point where i'm bringing in two columns with values (city and county) and i've got a lookup sheet where the value of the state - Say Michigan (or MI) is 45 for example and the county is 13. I'm inputting MI in to the cell but would like for it to somehow reflect 45. I'm wondering if i can replace this with a function (not sure how to do that) or just write a formula in another column (would rather not do this). maybe write the formula in the column and then paste over the current column and delete the vlookup column?

any advice on this?

i'd post code but the only code i've got thus far is copy paste from sheet to sheet. and that's working fine.

the number of rows will change but the columns should always been the same two.

 

[Joe4]

You could use a Worksheet_Change event (placed in the Sheet module in VBA) that when you place a value in certain cells, it automatically replaces them with the value from the other sheet.
One way to do this is by having the code place the vlookup formula in the cell, and then immediately replace it with the hard-coded value.

If you need help doing this, please provide us with the following:
- The name of the destination sheet
- The range address of the cells that you want to apply this automation to
- The VLOOKUP formula that returns the value from the other sheet (or the name of the sheet and a picture of the data layout, being sure to include column and row numbers)

 

[rossross]

You could use a Worksheet_Change event (placed in the Sheet module in VBA) that when you place a value in certain cells, it automatically replaces them with the value from the other sheet.
One way to do this is by having the code place the vlookup formula in the cell, and then immediately replace it with the hard-coded value.

If you need help doing this, please provide us with the following:
- The name of the destination sheet
- The range address of the cells that you want to apply this automation to
- The VLOOKUP formula that returns the value from the other sheet (or the name of the sheet and a picture of the data layout, being sure to include column and row numbers)

definitely need help

destination sheet "selector"

needs to apply to range "C" and range "D" from row 2 down to whatever the final count is

standard vlookup for the values in C would be =vlookup(c2,'Data'!$I$2:$M$75884,4,FALSE) and D would be the same except D2 and ,5,false...

moreover, the 75884 might not be set always so i was planning to just make it $I:$M

 

[Joe4]

Go to your "Selector" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor box that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim col As Long
    Dim x

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if column C or D updated
    col = Target.Column
    If (col = 3) Or (col = 4) Then
        x = Chr(34) & Target.Value & Chr(34)
        Application.EnableEvents = False
        Select Case Target.Column
'           If column C updated
            Case 3
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[6]:C[10],4,FALSE)"
'           If column D updated
            Case 4
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[5]:C[9],5,FALSE)"
        End Select
'       Hard-code value
        Target.Value = Target.Value
        Application.EnableEvents = True
    End If

End Sub

This code should automatically run as you enter values in columns C and D on your "Selector" sheet.

 

[rossross]

Go to your "Selector" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor box that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim col As Long
    Dim x

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if column C or D updated
    col = Target.Column
    If (col = 3) Or (col = 4) Then
        x = Chr(34) & Target.Value & Chr(34)
        Application.EnableEvents = False
        Select Case Target.Column
'           If column C updated
            Case 3
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[6]:C[10],4,FALSE)"
'           If column D updated
            Case 4
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[5]:C[9],5,FALSE)"
        End Select
'       Hard-code value
        Target.Value = Target.Value
        Application.EnableEvents = True
    End If

End Sub

This code should automatically run as you enter values in columns C and D on your "Selector" sheet.

it didn't run automatically, unfortunately. can this be done with application.worksheetfunction or does it need to be R1C1?

 

[rossross]

Go to your "Selector" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor box that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim col As Long
    Dim x

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if column C or D updated
    col = Target.Column
    If (col = 3) Or (col = 4) Then
        x = Chr(34) & Target.Value & Chr(34)
        Application.EnableEvents = False
        Select Case Target.Column
'           If column C updated
            Case 3
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[6]:C[10],4,FALSE)"
'           If column D updated
            Case 4
                Target.FormulaR1C1 = "=VLOOKUP(" & x & ",Data!C[5]:C[9],5,FALSE)"
        End Select
'       Hard-code value
        Target.Value = Target.Value
        Application.EnableEvents = True
    End If

End Sub

This code should automatically run as you enter values in columns C and D on your "Selector" sheet.

it didn't run at all, unfortunately. can this be done with application.worksheetfunction or does it need to be R1C1? also, "if more than one is updated," it exits, does that sound exactly as it reads? meaning multiple rows won't update? because the example i'm working on now has 2900 rows for each column

 

[Joe4]

it didn't run automatically, unfortunately

Then you most likely placed it in the wrong place. It NEEDS to be placed in the Sheet module that you want it to run against.
If you place it in a General/Standard Module, or any other module, it will not work automatically.
If you follow the exact instructions I provided on where to add it, that will ensure it goes there.

can this be done with application.worksheetfunction or does it need to be R1C1?

You could, but what difference does it really make?
That is only a temporary step and it gets overwritten by the value of the function.

"if more than one is updated," it exits, does that sound exactly as it reads? meaning multiple rows won't update? because the example i'm working on now has 2900 rows for each column

It just means "one at a time". Would you be updating multiple cells in columns C or D at once (i.e. like Copy/Pasting values into multiple cells in columns C or D in one step)?
Or, are you manually entering values in columns C and D, one at a time.

 

[rossross]

Then you most likely placed it in the wrong place. It NEEDS to be placed in the Sheet module that you want it to run against.
If you place it in a General/Standard Module, or any other module, it will not work automatically.
If you follow the exact instructions I provided on where to add it, that will ensure it goes there.


You could, but what difference does it really make?
That is only a temporary step and it gets overwritten by the value of the function.


It just means "one at a time". Would you be updating multiple cells in columns C or D at once (i.e. like Copy/Pasting values into multiple cells in columns C or D in one step)?
Or, are you manually entering values in columns C and D, one at a time.

no3 - i've got a sub that pulls from source sheet to this target sheet so it inserts/copies all the rows and columns i need at once
no1 - i placed it in the sheet module
no2 - i don't quite understand the R1C1 (what does C[6]:C[10] mean for example) so i figured it'd be easier on my end to use worksheetfunction to work it out if i need to

 

[Joe4]

i've got a sub that pulls from source sheet to this target sheet so it inserts/copies all the rows and columns i need at once

OK, that is an important detail you did not mention initially.
From your initial question, it sounded like you were manually entering the values in manually.

I'm inputting MI in to the cell but would like for it to somehow reflect 45

If you are using Copy/Paste to do multiple cells, the code would not work, as it is quitting if multiple cells are being updated.
Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim col As Long
    Dim x As String
    Dim lrng As Range
    
'   See if any updates made to columns C or D
    Set rng = Intersect(Target, Range("C:D"))
    
'   Exit if no cells updated in columns C or D
    If rng Is Nothing Then Exit Sub
    
'   Set lookup range
    Set lrng = Sheets("Data").Columns("I:M")
    
    Application.EnableEvents = False
    
'   Loop through all updated cells
    For Each cell In rng
'       Set variables
        col = cell.Column
        x = cell.Value
        Select Case col
'           If column C updated
            Case 3
                cell.Value = Application.WorksheetFunction.VLookup(x, lrng, 4, False)
'           If column D updated
            Case 4
                cell.Value = Application.WorksheetFunction.VLookup(x, lrng, 5, False)
        End Select
    Next cell

    Application.EnableEvents = True

End Sub

 

[rossross]

OK, that is an important detail you did not mention initially.
From your initial question, it sounded like you were manually entering the values in manually.


If you are using Copy/Paste to do multiple cells, the code would not work, as it is quitting if multiple cells are being updated.
Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim col As Long
    Dim x As String
    Dim lrng As Range
   
'   See if any updates made to columns C or D
    Set rng = Intersect(Target, Range("C:D"))
   
'   Exit if no cells updated in columns C or D
    If rng Is Nothing Then Exit Sub
   
'   Set lookup range
    Set lrng = Sheets("Data").Columns("I:M")
   
    Application.EnableEvents = False
   
'   Loop through all updated cells
    For Each cell In rng
'       Set variables
        col = cell.Column
        x = cell.Value
        Select Case col
'           If column C updated
            Case 3
                cell.Value = Application.WorksheetFunction.VLookup(x, lrng, 4, False)
'           If column D updated
            Case 4
                cell.Value = Application.WorksheetFunction.VLookup(x, lrng, 5, False)
        End Select
    Next cell

    Application.EnableEvents = True

End Sub

thankfully not manually. still not running though unfortunately. not sure what i'm doing wrong but i'm doing everything you've got listed here. i'll keep workshopping it and do some research. also thanks for switching to worksheetfunction!