VBA Lookup

[Matt]

Have searched through the forum but cannot find an answer to my problem. I have a range of cells in sheet1, lets say Range("A5:A15"). I want to replace the values with the results from a lookup.

I want to match the values from sheets("Sheet1").range("A5:A15") to sheets("Sheet2").range("E33:E47") and replace the values from sheets("Sheet1").range("A5:A15") with sheets("Sheet2").range("H33:H47").

Is this possible and if so, please can you offer some guidance on how to achieve.

Many Thanks

Matt

 

[Jim North]

Hi Matt,

Yes... and no. If you want to use a formula, the "replace" will need to occur in another column... for example B5:B15. If you use a macro (VBA subprocedure) then a to replace the values in A5:A15 would be possible... option 1 is diffently the easiest.

Put this in B5:B15 to get your formula answer:
=IF(ISNA(VLOOKUP($A5,Sheet2!$E$5:F$15,2,FALSE)),"",VLOOKUP($A5,Sheet2!$E$5:F$15,2,FALSE))

 

[Matt]

Thanks for your help. Unfortunately, I need to go the VBA route as I am setting up an automated spreadsheet. Using the formula that I have below is not going to work as the formula replaces the cell value. Any
ideas how to structure the following code to overwrite a cell value with a lookup result. Hope someone can help.

thanks

Matt


For Each Cell In myrange

Cell.Formula = "=IF(ISNA(VLOOKUP(cell.value,$E$33:H$47,4,FALSE)),"""",VLOOKUP(cell.value,$E$33:H$47,4,FALSE))"

Next

 

[Jim North]

This should be a good start:

<pre>

Sub ReplaceCell()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim Sell As Range
Dim LastRow As Long
Dim Rng As Range
Dim C As Range
Dim firstaddress As String

' turn off screen updating
Application.ScreenUpdating = False

' point to the worksheets
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")

' point to Sheet1
wks1.Activate

' get the number of rows used on Sheet1
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

' point to the data from Sheet 1
Set Rng = Range("A5:A" & LastRow)

For Each Sell In Rng
' find the value in sheet 2 column E
With wks2.Range("E:E")
Set C = .Find(Sell.Value, LookIn:=xlValues)
If Not C Is Nothing Then
firstaddress = C.Address
Do
Loop While Not C Is Nothing And C.Address <> firstaddress
End If
End With

' if found replace the value with what is in column H
If C Is Nothing Then
' do nothing
Else
' replace the value
wks1.Range("A" & Sell.Row).Value = wks2.Range("H" & C.Row).Value

End If

Next Sell

'point to cell A1 on Sheet1
'CutCopyMode = False
wks1.Activate
Range("A1").Select
Application.ScreenUpdating = True

End Sub

</pre>

 

[Matt]

Spot on Jim, that's perfect, thanks!