Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and VBA
Hi all, i would really appreciate if someone could help me. I have a problem that i cannot get around using vlookup and was 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 that has the same asset n0s but also has a whole bunch of other data that is related to each assest. example: 1 asset n0 will also have 10 columns of other data related to it. What i was trying to acheive using vlookup was extract the related data from the monthly sheet, unfortunatly i now understand that vlookup will only let me reference 1 col_ind_num, where as i need to ref at least several. Can this be acheived using code? Any help greatly appreciated. Moremeba -- moremeba ------------------------------------------------------------------------ moremeba's Profile: http://www.thecodecage.com/forumz/member.php?userid=666 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124959 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and VBA
Use multiple VLOOKUPS
=VLOOKUP(A2,Sheet2!A:M,2,False) =VLOOKUP(A2,Sheet2!A:M,3,False) etc. -- __________________________________ HTH Bob "moremeba" wrote in message ... Hi all, i would really appreciate if someone could help me. I have a problem that i cannot get around using vlookup and was 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 that has the same asset n0s but also has a whole bunch of other data that is related to each assest. example: 1 asset n0 will also have 10 columns of other data related to it. What i was trying to acheive using vlookup was extract the related data from the monthly sheet, unfortunatly i now understand that vlookup will only let me reference 1 col_ind_num, where as i need to ref at least several. Can this be acheived using code? Any help greatly appreciated. Moremeba -- moremeba ------------------------------------------------------------------------ moremeba's Profile: http://www.thecodecage.com/forumz/member.php?userid=666 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124959 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and VBA
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 "moremeba" wrote: Hi all, i would really appreciate if someone could help me. I have a problem that i cannot get around using vlookup and was 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 that has the same asset n0s but also has a whole bunch of other data that is related to each assest. example: 1 asset n0 will also have 10 columns of other data related to it. What i was trying to acheive using vlookup was extract the related data from the monthly sheet, unfortunatly i now understand that vlookup will only let me reference 1 col_ind_num, where as i need to ref at least several. Can this be acheived using code? Any help greatly appreciated. Moremeba -- moremeba ------------------------------------------------------------------------ moremeba's Profile: http://www.thecodecage.com/forumz/member.php?userid=666 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124959 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and VBA
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. Moremeba -- moremeba ------------------------------------------------------------------------ moremeba's Profile: http://www.thecodecage.com/forumz/member.php?userid=666 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124959 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |