Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Hi all, i have struck a blank and must have this finished by morning !!
need to go down column A of workbookA and look up the value of the cells in A in workbookB, if found place the order number from column G in the column AM of the workbookA. I have tried various options from this site but just cannot get it to work. Any help is gratefully accepted. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Perhaps use VLOOKUP function in column AM of workbookA. Check help for more
details. Be sure to Adjust the range for workbookB to whatever your actual range is. =VLOOKUP(A1,[WorkbookB.xls]Sheet1!$A$1:$G$15,7,FALSE) Then copy the formula down column AM. To hardcode the values, select column AM, click copy, then Edit/Paste Special and select values option. To avoid #N/A for items that have no match, use =IF(ISNUMBER(MATCH(A1,[WorkbookB.xls]Sheet1!$A$1:$A$15,0)),VLOOKUP(A1,[WorkbookB.xls]Sheet1!$A$1:$G$15,7,FALSE), "") "Les Stout" wrote: Hi all, i have struck a blank and must have this finished by morning !! need to go down column A of workbookA and look up the value of the cells in A in workbookB, if found place the order number from column G in the column AM of the workbookA. I have tried various options from this site but just cannot get it to work. Any help is gratefully accepted. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Thanks JMB but i need to do this with code, automatically...
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Hi Les -
I'd suggest the following formula. The easiest way to build the formula is to initially have both workbooks open. 1. Open both workbooks. 2. Copy the following formula to cell AM2 of WorkbookA and adjust: a. the workbook name b. the sheet name c. the address of the reference range A2:G2739 (keep the '$' symbols in, but adjust the row number to suit). 3. Copy the formula downward as far as necessary and save WorkbookA. =VLOOKUP(A2,[WorkbookB.xls]OrderList!$A$2:$G$2739,7,FALSE) ----- Notes: 1. After saving the new column of formulas in WorkbookA, WorkbookB can be closed. Having it open just simplifies formula creation. 2. This formula will search through column A in WorkbookB until it finds an exact match and returns the value in column G. If there are duplicate values in column A of Workbook B, only the first match will yield a result. 3. The formula above will return a value of "#N/A" if a match is not found. The formula could be embellished as follows to return an empty cell or "NotFound" if desired: =IF(ISNA(VLOOKUP(A2,[WorkbookB.xls]OrderList!$A$2:$G$2739,7,FALSE)),"NotFound",VLOOKU P(A2,[WorkbookB.xls]OrderList!$A$2:$G$2739,7,FALSE)) (replace "NotFound" with "" for an empty cell) -- Jay "Les Stout" wrote: Hi all, i have struck a blank and must have this finished by morning !! need to go down column A of workbookA and look up the value of the cells in A in workbookB, if found place the order number from column G in the column AM of the workbookA. I have tried various options from this site but just cannot get it to work. Any help is gratefully accepted. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Perhaps this will help. Put this macro in WorkbookA. Change the worksheet
and workbook names as needed. Sub test() Dim rngCell As Range Dim rngFound As Range Dim rngA As Range Dim rngB As Range With ThisWorkbook.Sheets("Sheet1") '<<<CHANGE Set rngA = .Range("A1:A" & _ .Cells(.Rows.Count, 1).End(xlUp).Row) End With With Workbooks("WorkbookB.xls").Sheets("Sheet1") '<<CHANGE Set rngB = .Range("A1:A" & _ .Cells(.Rows.Count, 1).End(xlUp).Row) End With For Each rngCell In rngA If Len(rngCell.Value) 0 Then Set rngFound = rngB.Find( _ what:=rngCell.Value, _ after:=rngB.Range("A1"), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False, _ matchbyte:=False) If Not rngFound Is Nothing Then rngCell(1, 39).Value = rngFound(1, 7).Value Set rngFound = Nothing End If End If Next rngCell End Sub "Les Stout" wrote: Thanks JMB but i need to do this with code, automatically... Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Hope it helps. Watch for word wrap, this piece of code s/b on one line.
rngCell(1, 39).Value = rngFound(1, 7).Value "Les Stout" wrote: Thanks so much JMB... Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rather tired and need solution please 4 Vllookup :-D
Hi Les -
Sounds like you and JMB have this under control. For what it's worth, here's a version that's similar to JMB's. The only functional difference is that it opens WorkbookB.xls if it's not open. If you need this capability, run this procedure when WorkbookA is the active workbook. Otherwise, disregard this post and have a nice evening. Sub LesStout() Dim wbA As Workbook Dim wbB As Workbook Dim wsA As Worksheet Dim wsB As Worksheet Dim strt As Range Dim matching As Range ''''''''''''''''''''''''''''''''''''''''''''''' 'Modify these statements to suit wbBPath = "C:\Documents and Settings\Les\My Documents" '<==WorkbookB path wbBName = "WorkbookB.xls" '<==WorkbookB name wsBName = "Sheet1" '<==Name of sheet in WorkBookB Set strt = Range("A2") '<==Address (in WorkbookA) of first value to look for. ''''''''''''''''''''''''''''''''''''''''''''''' Application.ScreenUpdating = False Set wbA = ActiveWorkbook Set wsA = wbA.ActiveSheet On Error Resume Next Workbooks.Open (wbBPath & "\" & wbBName) On Error GoTo 0 Workbooks(wbBName).Activate Set wbB = ActiveWorkbook Set wsB = wbB.Worksheets(wsBName) wbA.Activate For Each itm In Range(strt, Cells(wsA.Rows.Count, 1).End(xlUp)).Cells On Error GoTo around Set matching = wsB.Columns(1).Find(itm, LookIn:=xlValues, lookat:=xlWhole) itm.Offset(0, 38) = matching.Offset(0, 6).Value around: Next 'itm wbB.Close savechanges:=False End Sub ------- Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I must be tired, because basic Math isn't working..... | Excel Programming | |||
I must be tired, because basic Math isn't working..... | Excel Worksheet Functions | |||
Urgent problem with Vllookup | Excel Discussion (Misc queries) | |||
VlLOOKUP function with MATCH | Excel Worksheet Functions | |||
Tired of Excel's Interface limitations...?? | Excel Programming |