PDA

View Full Version : Solved: vlookup problem



wakdafak
01-16-2012, 12:03 AM
i have a query here

i have 2 workbooks:
workbook one: contain 1 sheet that have airwaybills number.
workbook two: contain 2 sheet that contain airwaybills number and shipper reff.

can sombody help me how can i vlookup the airwaybills and get the shipper reff and paste it into the new workbook.

:banghead:

thanks in advance

Bob Phillips
01-16-2012, 04:18 AM
Have you tried it? There is no problem with VLOOKUP across workbooks.

wakdafak
01-16-2012, 08:48 PM
it can be done only when i insert the vlookup manually

i need a code of vba to do it...

Bob Phillips
01-17-2012, 01:38 AM
So do it in Excel with the macro recorder on, there you have the code.

wakdafak
01-18-2012, 11:42 PM
done

thanks for giving me the idea :friends:

:thumb

wakdafak
01-19-2012, 12:00 AM
Sub vlookupservicearea()
Dim shtt As Worksheet
Dim wrkk As Workbook
lMaxSht = Sheets.Count
For shtno = 2 To lMaxSht
Set wrkk = ActiveWorkbook
Set shtt = wrkk.Worksheets(shtno)
If Worksheets(shtno).Range("D2").Value Like "9*" Then
For Each shtt In wrkk.Worksheets
If shtt.Name Like "HM*" Then
shtt.Select
Range("X2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-7],OPMS!RC[-21]:R[4261]C[58],27,FALSE)"
Selection.AutoFill Destination:=Range("X2:X2000"), Type:=xlFillDefault
Range("X1").Select
ActiveCell.FormulaR1C1 = "Country Code Area"
Columns("X:X").EntireColumn.AutoFit
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next
Else
For Each shtt In wrkk.Worksheets
If shtt.Name Like "HM*" Then
shtt.Select
Range("X2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-7],OPMS!RC[-21]:R[4261]C[58],32,FALSE)"
Selection.AutoFill Destination:=Range("X2:X2000"), Type:=xlFillDefault
Range("X1").Select
ActiveCell.FormulaR1C1 = "Services Area Code"
Columns("X:X").EntireColumn.AutoFit
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next
End If
Next
End Sub


working perfect... :clap: