Vlookup and VBA

M

moremeba

Hi all, i would really appreciate if someone could help me.
I have a problem that i cannot get around using vlookup and wa
wondering if this could be solved by using code?
i have a spreadsheet that has unique asset n0's in column A
I also have a second spreadsheet that is system generated monthly tha
has the same asset n0s but also has a whole bunch of other data that i
related to each assest. example: 1 asset n0 will also have 10 columns o
other data related to it.
What i was trying to acheive using vlookup was extract the related dat
from the monthly sheet, unfortunatly i now understand that vlookup wil
only let me reference 1 col_ind_num, where as i need to ref at leas
several.
Can this be acheived using code?
Any help greatly appreciated.

Moremeb
 
B

Bob Phillips

Use multiple VLOOKUPS

=VLOOKUP(A2,Sheet2!A:M,2,False)

=VLOOKUP(A2,Sheet2!A:M,3,False)

etc.
 
J

Joel

You could still do this with vlookup but you would need a formula for each
colum of data you need to retrieve.

the code below will use a dialog box to open the monthly report. You will
need to shange the sheet names in the code below to match the names in you
workbooks. I also gave examples of how to get and write the data from
diffferent columns. You will need to change the columns as required.

Sub GetMonthlData()

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls", _
Title:="Select Monthly Data Report")
If fileToOpen = False Then
MsgBox ("Cannot open file - Exiting macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=fileToOpen)
Set MonthlySht = bk.Sheets("Sheet1")
Set DestSht = ThisWorkbook.Sheets("Sheet1")

With DestSht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
AssetNo = .Range("A" & RowCount)

'lookup asset number in monthly report
With MonthlySht
Set c = .Columns("A").Find(what:=AssetNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find AssetNo : " & AssetNo)
Else
Data1 = .Range("B" & c.Row)
Data2 = .Range("G" & c.Row)
Data3 = .Range("H" & c.Row)
Data4 = .Range("K" & c.Row)
Data5 = .Range("Z" & c.Row)

With DestSht
.Range("B" & RowCount) = Data1
.Range("C" & RowCount) = Data2
.Range("D" & RowCount) = Data3
.Range("E" & RowCount) = Data4
.Range("F" & RowCount) = Data5

End With
End If
End With

RowCount = RowCount + 1
Loop

bk.Close savechanges:=False
End With
End Sub
 
M

moremeba

Hi Joel. Many thanks for taking the time to help with my problem.
Your code is spot on and works very well.

I really appreciate your help.

Moremeb
 
Top