Home/ Questions/Q 9869
Next
Answered
computing
  • 4
Asked: 2021-12-03T09:05:07+00:00 In:

Solved VBA To Add VLOOKUP Formula To Last Row

  • 4

Hi,

I would like to add a formula for a VLOOKUP to a column to the last row. This is the actual formula:

=VLOOKUP(C4,'Open Job Query'!B:L,9,FALSE)

Tried adding to the below formula but failing miserably ;(

 

Sub CopyColumns()

'Clear range on Datasheet to Update Timberline sheet
    Sheets("Datasheet to Update Timberline").Range("A4:E5000").ClearContents

Dim lastrow As Long, erow As Long, i As Long

lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lastrow

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
Sheet2.Cells(i, 8).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 1).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 4).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 2).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 2).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 3).PasteSpecial Paste:=xlPasteValues

Sheet2.Cells(i, 3).Copy
    Worksheets("Datasheet to Update Timberline").Cells(erow, 4).PasteSpecial Paste:=xlPasteValues
    

<b>Sheets("Datasheet to Update Timberline").Range("E4" & erow + 1).Formula = "=VLOOKUP(C4:C" & erow & ","Sheets("Open Job Query").Range("B:L",9,FALSE)"</b>

Next i

Application.CutCopyMode = False
Sheet1.Columns.AutoFit
Range("A1").Select

End Sub

Any help is greatly appreciated!

Thank you,
Sandi

Share

    1 Answer

    1. Best Answer
      Just a comment about your VLOOKUP()

      You have it written so that it searches the ENTIRE column B
      which in Excel 2010 is 1,048,576

      That is a HUGE area to be searching and will take time.

      Do you really need to search the entire column?
      Wouldn’t it be more efficient to search just those rows that are necessary?

      MIKE

      http://www.skeptic.com/

      • 0