VBA Excel function isn't filling down completely? - Stack Overflow

admin管理员组

文章数量:1122832

I have a VBA project that usually works well, until I received a large file with several more rows. I'm doing a Vlookup against column A, and it must fill down all rows in columns B, C, and D. For some reason, it's stopping at row 1893. Here is my code so far:

'Enter Vlookups to get Invoice, InternalID, Status 
Dim LastRow As Long
     LastRow = Range("A2").End(xlDown).Row
     Range("B2:B" & LastRow).Formula = "=Vlookup(A2,NS!A:N,2,0)"
     Range("C2:C" & LastRow).Formula = "=Vlookup(A2,NS!A:N,3,0)"
     Range("D2:D" & LastRow).Formula = "=Vlookup(A2,NS!A:N,4,0)"

I also have the following block:

'Ensure Formula copies down
Range("B2:B" & LastRow).FillDown
Range("C2:C" & LastRow).FillDown
Range("D2:D" & LastRow).FillDown

Neither of these is working. Any ideas? Edit A1894 DOES include Data, excellent question!

I have a VBA project that usually works well, until I received a large file with several more rows. I'm doing a Vlookup against column A, and it must fill down all rows in columns B, C, and D. For some reason, it's stopping at row 1893. Here is my code so far:

'Enter Vlookups to get Invoice, InternalID, Status 
Dim LastRow As Long
     LastRow = Range("A2").End(xlDown).Row
     Range("B2:B" & LastRow).Formula = "=Vlookup(A2,NS!A:N,2,0)"
     Range("C2:C" & LastRow).Formula = "=Vlookup(A2,NS!A:N,3,0)"
     Range("D2:D" & LastRow).Formula = "=Vlookup(A2,NS!A:N,4,0)"

I also have the following block:

'Ensure Formula copies down
Range("B2:B" & LastRow).FillDown
Range("C2:C" & LastRow).FillDown
Range("D2:D" & LastRow).FillDown

Neither of these is working. Any ideas? Edit A1894 DOES include Data, excellent question!

• excel
• vba

Share Improve this question Follow edited Nov 22, 2024 at 21:02 Ingrid Potter asked Nov 22, 2024 at 19:00 Ingrid PotterIngrid Potter 1133 bronze badges 5

• 3 And does A1894 contain data? Maybe use with Thisworkbook.Worksheet("Sheetname") .cells(.rows.count,1).end(xlup).row End with – P.b Commented Nov 22, 2024 at 19:03
• 4 LastRow = Range(Rows.Count, "A").End(xlUp).Row is typically a more reliable way to find the last row in a column. It's not sensitive to gaps in your data. – Tim Williams Commented Nov 22, 2024 at 19:05
• Set up your data as a structured Table and then you can avoid having to do figure out your last row. With a structured table you can use Column headings to refer to the entire column. – Frank Ball Commented Nov 22, 2024 at 19:51
• A1894 does indeed contain data, will edit the main question to include this bit. – Ingrid Potter Commented Nov 22, 2024 at 21:02
• @TimWilliams, Thank you, will try that! – Ingrid Potter Commented Nov 22, 2024 at 21:05

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

if you are still using the "A" column, you can try changing to max among the used columns:

lastrow = WorksheetFunction.Max( _
        Cells(Rows.Count, "A").End(xlUp).Row, _
        Cells(Rows.Count, "B").End(xlUp).Row, _
        Cells(Rows.Count, "C").End(xlUp).Row, _
        Cells(Rows.Count, "D").End(xlUp).Row)

A VBA Lookup: Lookup Using Formulas

• VLOOKUP isn't quite flexible i.e. it cannot be copied from one column to another because of the hard-coded col_num_index parameter.

• A better choice is the INDEX/MATCH combo e.g. you could use the following formula...

  =INDEX(NS!B$2:B$11,MATCH($A2,NS!$A$2:$A$11,0))
  

  in Sheet2!B2 and copy across as required.

Main

Sub WriteFormula()

    ' Define constants.
    Const SRC_SHEET_NAME As String = "NS"
    Const SRC_TOP_LEFT_CELL_ADDRESS As String = "A2"
    Const SRC_LOOKUP_COLUMN As String = "A"
    Const SRC_FIRST_RETURN_COLUMN As String = "B"
    Const DST_SHEET_NAME As String = "Sheet2" ' ???
    Const DST_TOP_LEFT_CELL_ADDRESS As String = "A2"
    Const DST_LOOKUP_COLUMN As String = "A"
    Const DST_RETURN_COLUMNS As String = "B:D"
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If it isn't, reference it by its name or use 'ActiveWorkbook' instead.
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_SHEET_NAME)
    Dim srg As Range:
    Set srg = RefCurrentRegion(sws.Range(SRC_TOP_LEFT_CELL_ADDRESS))
    ' Lock rows and columns.
    Dim SourceLookup As String: SourceLookup = "'" & sws.Name & "'!" _
        & srg.EntireRow.Columns(SRC_LOOKUP_COLUMN).Address
    ' Lock only rows.
    Dim SourceReturn As String: SourceReturn = "'" & sws.Name & "'!" _
        & srg.EntireRow.Columns(SRC_FIRST_RETURN_COLUMN).Columns(1).Address(, 0)
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Sheets(DST_SHEET_NAME)
    Dim drg As Range:
    Set drg = RefCurrentRegion(dws.Range(DST_TOP_LEFT_CELL_ADDRESS))
    ' Lock only columns.
    Dim DestinationLookup As String: DestinationLookup = _
        drg.Cells(1).EntireRow.Columns(DST_LOOKUP_COLUMN).Address(0)
    ' Resulting range.
    Dim drrg As Range: Set drrg = drg.EntireRow.Columns(DST_RETURN_COLUMNS)
    
    ' Formula
    Dim FormulaString As String: FormulaString = "" _
        & "=INDEX(" & SourceReturn _
        & ",MATCH(" & DestinationLookup _
        & "," & SourceLookup _
        & ",0))"
            
    ' Write formula.
    drrg.Formula = FormulaString
        
End Sub

Help

Function RefCurrentRegion(topLeftCell As Range) As Range
    
    If topLeftCell Is Nothing Then Exit Function
    
    With topLeftCell.Cells(1).CurrentRegion
        Set RefCurrentRegion = topLeftCell.Resize(.Row + .Rows.Count _
            - topLeftCell.Row, .Column + .Columns.Count - topLeftCell.Column)
    End With

End Function

本文标签： VBA Excel function isn39t filling down completelyStack Overflow