Help with vlookup

[Sinner]

Hello,

How do I modify G2 in .Formula = "=vlookup(G2," & VLookUpAddr & ",
4,false)" the number so that it can loop through cell by

cell receipt numbers in columnG of sheet1 and yield result like G3,
G4, G5.... etc.

It is obvious form the code that I have two sheets.
I want to add sheet3 and get values from that for values that are in
columnD of sheet1.


Like:
sheet1 columnG with sheet2 table(C:F) and result in columnP of sheet1
sheet1 columnD with sheet3 table(A:B) and result in columnQ of sheet1


Should be able to do with same macro.
Thx.
---------------------------------------------------------------------------­---------
Option Explicit
Sub Testme()


Dim Wk As Worksheet
Dim Wk2 As Worksheet
Dim FormRng As Range
Dim VLookUpAddr As String
Dim LastRow As Long

Set Wk = Worksheets("sheet1")
Set Wk2 = Worksheets("sheet2")

With Wk
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
Set FormRng = .Range("P2" & LastRow)
End With


VLookUpAddr = Wk2.Range("C:F").Address(external:=True)


With FormRng
'turn calculation to manual before plopping in the formulas
Application.Calculation = xlManual
.Formula = "=vlookup(G2," & VLookUpAddr & ",4,false)"
'back to automatic
Application.Calculation = xlAutomatic


'convert to values
.Copy
.PasteSpecial Paste:=xlPasteValues


'remove those marching ants/marquee
Application.CutCopyMode = False


'get rid of no match and empty cells that came back as 0's
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
.Replace what:="0", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
End With

End Sub

 

[Per Jessen]

Hi Sinner

Try this.

Option Explicit
Sub Testme()


Dim Wk As Worksheet
Dim Wk2 As Worksheet
Dim FormRng As Range
Dim VLookUpAddr As String
Dim LastRow As Long
Dim c As Variant

Set Wk = Worksheets("sheet1")
Set Wk2 = Worksheets("sheet2")

With Wk
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
Set FormRng = .Range("P2" & LastRow)
End With


VLookUpAddr = Wk2.Range("C:F").Address(external:=True)

'turn calculation to manual before plopping in the formulas
Application.Calculation = xlManual

For Each c In FormRng
c.Formula = "=vlookup(G" & c.Row & "," & VLookUpAddr &
",4,false)"
Next

'back to automatic
Application.Calculation = xlAutomatic

With FormRng
'convert to values
.Copy
.PasteSpecial Paste:=xlPasteValues


'remove those marching ants/marquee
Application.CutCopyMode = False


'get rid of no match and empty cells that came back as 0's
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
.Replace what:="0", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
End With

End Sub

Best regards,
Per

"Sinner" <[email protected]> skrev i meddelelsen
Hello,

How do I modify G2 in .Formula = "=vlookup(G2," & VLookUpAddr & ",
4,false)" the number so that it can loop through cell by

cell receipt numbers in columnG of sheet1 and yield result like G3,
G4, G5.... etc.

It is obvious form the code that I have two sheets.
I want to add sheet3 and get values from that for values that are in
columnD of sheet1.


Like:
sheet1 columnG with sheet2 table(C:F) and result in columnP of sheet1
sheet1 columnD with sheet3 table(A:B) and result in columnQ of sheet1


Should be able to do with same macro.
Thx.
---------------------------------------------------------------------------­---------
Option Explicit
Sub Testme()


Dim Wk As Worksheet
Dim Wk2 As Worksheet
Dim FormRng As Range
Dim VLookUpAddr As String
Dim LastRow As Long

Set Wk = Worksheets("sheet1")
Set Wk2 = Worksheets("sheet2")

With Wk
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
Set FormRng = .Range("P2" & LastRow)
End With


VLookUpAddr = Wk2.Range("C:F").Address(external:=True)


With FormRng
'turn calculation to manual before plopping in the formulas
Application.Calculation = xlManual
.Formula = "=vlookup(G2," & VLookUpAddr & ",4,false)"
'back to automatic
Application.Calculation = xlAutomatic


'convert to values
.Copy
.PasteSpecial Paste:=xlPasteValues


'remove those marching ants/marquee
Application.CutCopyMode = False


'get rid of no match and empty cells that came back as 0's
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
.Replace what:="0", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
End With

End Sub