Rather tired and need solution please 4 Vllookup :-D

[Les Stout]

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 ***

 

[JMB]

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]

Thanks JMB but i need to do this with code, automatically...

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jay]

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",VLOOKUP(A2,[WorkbookB.xls]OrderList!$A$2:$G$2739,7,FALSE))

(replace "NotFound" with "" for an empty cell)

 

[JMB]

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]

Thanks so much JMB...

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[JMB]

Hope it helps. Watch for word wrap, this piece of code s/b on one line.

rngCell(1, 39).Value = rngFound(1, 7).Value

 

[Jay]

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