Vlookup VBA Excel-CodePudding

Range("B3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'02.09'!C[-1]:C,2,0)"
    Range("B3").Select
    Selection.FillDown

First: I'm using the above code to to a Vlookup. If I run this Macro there is no error, but also the column which i did the vlookup has no values.

Second: In this code im referencing sheet "02.09". But i would like to reference always the second sheet. I haven't found a solution yet.

Thank you all!

CodePudding user response：

VLookup R1C1 Formula in VBA

A Quick Fix

Sub VLookupR1C1()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(2)
    Dim dws As Worksheet: Set dws = wb.Worksheets(1) ' adjust!
    
    With dws.Range("B3") ' reference the first cell
        Dim lRow As Long ' the last row in column 'A' (offset '-1')
        lRow = .Offset(dws.Rows.Count - .Row, -1).End(xlUp).Row
        With .Resize(lRow - .Row   1) ' reference the (one-column) range
            .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'" & sws.Name _
                & "'!C[-1]:C,2,0),"""")" ' write the formula to the range
        End With
    End With

End Sub

CodePudding user response：

The reason that there is no data in your column is that Fill down requires a range; it fills a range with the first cell in the range. The following code will fill down to row 5, you will need to define the last row that you want the formula copying to.

Range("B3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'02.09'!C[-1]:C,2,0)"
Range("B3:B5").FillDown