vlook up macro entire column

[Tonyk1051]

Hi,

i recorded a vlook up for 3 different columns and applied it to new work book and it kind of works.. what part of the code do i need to change in order to make it to work for any amount of data? the original workbook had 800 lines the new work book has 2000 lines but the macro only applied it to 800, my guess is has something to do with the range. Also if the data is less than 800 it'll work but will just put N/A on blanks lines...

[code/]
Sub vlookup()

'

' vlookup Macro

'



'

Range("E2").Select

Application.CutCopyMode = False

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _

"=VLOOKUP(RC[-1],'Vendor Policies'!C[-4]:C[-1],4,FALSE)"

Selection.AutoFill Destination:=Range("E2:E831")

Range("E2:E831").Select

ActiveWindow.LargeScroll ToRight:=1

Range("R2").Select

Application.CutCopyMode = False

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _

"=VLOOKUP(RC[-14],'Vendor Policies'!C[-17]:C[-6],12,FALSE)"

Selection.AutoFill Destination:=Range("R2:R831")

Range("R2:R831").Select

Range("S2").Select

Application.CutCopyMode = False

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _

"=VLOOKUP(RC[-15],'Vendor Policies'!C[-18]:C[2],21,FALSE)"

Selection.AutoFill Destination:=Range("S2:S831")

Range("S2:S831").Select

Columns("R:S").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub
[/code]

 

[breynolds0431]

Hi. You can use a method to find the last row, such as in the below.

Dim ws As Worksheet: Set ws = ActiveSheet
Dim c As Range

With ws
    'adds formula to column E based on last cell in column D
    .Range("E2:E" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Vendor Policies'!C[-4]:C[-1],4,FALSE)"
  
    'converts formulas to values
    For Each c In .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
  
    'adds formula to column R based on last cell in column D
    .Range("R2:R" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-14],'Vendor Policies'!C[-17]:C[-6],12,FALSE)"

    'converts formulas to values
    For Each c In .Range("R2:R" & .Cells(.Rows.Count, "R").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c

    'adds formula to column S based on last cell in column D
    .Range("S2:S" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-15],'Vendor Policies'!C[-18]:C[2],21,FALSE)"

    'converts formulas to values
    For Each c In .Range("S2:S" & .Cells(.Rows.Count, "S").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
End With

 

[Tonyk1051]

Hi. You can use a method to find the last row, such as in the below.

Dim ws As Worksheet: Set ws = ActiveSheet
Dim c As Range

With ws
    'adds formula to column E based on last cell in column D
    .Range("E2:E" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Vendor Policies'!C[-4]:C[-1],4,FALSE)"
 
    'converts formulas to values
    For Each c In .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
 
    'adds formula to column R based on last cell in column D
    .Range("R2:R" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-14],'Vendor Policies'!C[-17]:C[-6],12,FALSE)"

    'converts formulas to values
    For Each c In .Range("R2:R" & .Cells(.Rows.Count, "R").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c

    'adds formula to column S based on last cell in column D
    .Range("S2:S" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-15],'Vendor Policies'!C[-18]:C[2],21,FALSE)"

    'converts formulas to values
    For Each c In .Range("S2:S" & .Cells(.Rows.Count, "S").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
End With

Hi. You can use a method to find the last row, such as in the below.

Dim ws As Worksheet: Set ws = ActiveSheet
Dim c As Range

With ws
    'adds formula to column E based on last cell in column D
    .Range("E2:E" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Vendor Policies'!C[-4]:C[-1],4,FALSE)"
 
    'converts formulas to values
    For Each c In .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
 
    'adds formula to column R based on last cell in column D
    .Range("R2:R" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-14],'Vendor Policies'!C[-17]:C[-6],12,FALSE)"

    'converts formulas to values
    For Each c In .Range("R2:R" & .Cells(.Rows.Count, "R").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c

    'adds formula to column S based on last cell in column D
    .Range("S2:S" & .Cells(.Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
        "=VLOOKUP(RC[-15],'Vendor Policies'!C[-18]:C[2],21,FALSE)"

    'converts formulas to values
    For Each c In .Range("S2:S" & .Cells(.Rows.Count, "S").End(xlUp).Row).Cells
        c.Formula = c.Value
    Next c
End With

once again, you save the day breynolds, cant thank you enough. i have few more issues but ill post a new thread new week about it. be safe