vlookup macros with dynamic number of rows

[chrishrsa]

Hi,

I have a macro which i need to run which involves running a vlookup on a sheet with a different number of rows each time. The columns (fields) will stay the same however the number of rows will change from day to day.

Is there a way in which I can automate this vlookup?

Thanks

Chris

 

[unnilennium78]

post ur code please.... for some expert will definitely help u modify it.....

 

[Andrew Poulsom]

Couldn't you just use entire columns in your VLookup?

 

[chrishrsa]

Here is my code

PLease can you review my code:

Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("K2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R2C1:R4004C2,2,FALSE)"
Selection.AutoFill Destination:=Range("K2:K218")
Range("K2:K218").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K1").Select
ActiveCell.FormulaR1C1 = "Parcel No."
Range("L1").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Range("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet2!R2C1:R4004C3,3,FALSE)"
Selection.AutoFill Destination:=Range("L2:L218")
Range("L2:L218").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("L1").Select
ActiveCell.FormulaR1C1 = "TO Number"
Range("L2").Select
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R2C3:R4004C6,4,FALSE)"
Selection.AutoFill Destination:=Range("M2:M218")
Range("M2:M218").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("M1").Select
ActiveCell.FormulaR1C1 = "Source Bin"
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Range("N1").Select
ActiveCell.FormulaR1C1 = "Route"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet2!R2C3:R4004C7,5,FALSE)"
Selection.AutoFill Destination:=Range("N2:N218")
Range("N2:N218").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("N3").Select
Columns("N:N").EntireColumn.AutoFit
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Columns("O:O").Select
Selection.delete Shift:=xlToLeft
Range("O7").Select
ActiveWindow.SmallScroll ToRight:=5
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Sheet2!R5C5:R4003C8,4,FALSE)"
Selection.AutoFill Destination:=Range("R2:R218")
Range("R2:R218").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "d-mmm-yy"
Columns("R:R").EntireColumn.AutoFit
Range("R1").Select
ActiveCell.FormulaR1C1 = "Inv Date"
Range("R2").Select
End Sub