VBA Lookup

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Feb 16, 2002
Messages
212
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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>
 

Forum statistics

Threads
1,144,291
Messages
5,723,530
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top