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]

Try manually running this little macro and try again:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

If this still does not work, please explain to me exactly how you are putting the data on the "Selector" sheet.
Are you doing a Copy/Paste or some other method?
If Copy/Paste, what range are you pasting to?

 

[rossross]

Try manually running this little macro and try again:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

If this still does not work, please explain to me exactly how you are putting the data on the "Selector" sheet.
Are you doing a Copy/Paste or some other method?
If Copy/Paste, what range are you pasting to?

okay something is going on in my transition stage that disables the events. because going through each step and commanding enabling events makes it work. unfortunately i realized i missed a point in my original description and had to manually change the code to get it to look up the column D values (F:M instead of I) but hey column C worked.

this is what i'm using to bring in the values initially.

Sub getpool()

Dim sel As Worksheet
Dim tcol As Long
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim max As Long


Set sel = ThisWorkbook.Worksheets("Selector")
Set tape = ThisWorkbook.Worksheets("Tape")

lrow = tape.Cells(tape.Rows.Count, 1).End(xlUp).Row
max = 8

Application.ScreenUpdating = False

For i = 1 To max

If i = 1 Then
    tcol = 1 'sets col A to A
ElseIf i = 2 Then
    tcol = 8 'sets col B to H
ElseIf i > 8 Then
    tcol = i 'if more are needed
Else
    tcol = i - 1 'sets C to B
End If

For j = 8 To lrow

sel.Cells(j, tcol).Offset(-6) = tape.Cells(j, i)
Next j
Next i

'drag formulas next

Application.ScreenUpdating = True

End Sub

i looked through my entire module and i've got no mention of disabling events. the only instance of that is the worksheet event you had written but i wouldn't think that interferes with the deliberate operation, no?

 

[Joe4]

I would recommend putting a break point in at the top of the Worksheet_Change event procedure, then step through your "getpool" code line-by-line, and watch what happens, and see if/when it hits the Worksheet_Change event procedure.

 

[rossross]

I would recommend putting a break point in at the top of the Worksheet_Change event procedure, then step through your "getpool" code line-by-line, and watch what happens, and see if/when it hits the Worksheet_Change event procedure.

want to know the best part about all of this? the values i need to substitute in are already provided cell by cell where i couldn't see it and didn't know about it so don't even need to do this. but i sincerely appreciate your help and i've got this in my back pocket now in case i need to use something similar in the future.