Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with vlookup
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:P" & 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 |
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 |