Filldown a VLookup formula

[redhill]

Sub testfill()


Selection.Offset(0, -3).Select
    Selection.End(xlDown).Select
        Selection.End(xlToRight).Select
            Selection.Offset(, 1).Select
                Selection.End(xlUp).Offset(1).Select

Dim myrange As Range
 Set myrange = Range("New!A:C")
 
Dim LastRow As Integer
LastRow = WorksheetFunction.CountA(Range("A:A"))


 activecell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE)=0,""-"",VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE))"
        

End Sub

Hello. The above code works as required. What I cannot work out is how to reference the range I require and Filldown the formula.

The lastrow dimension finds the last row with data. The range I want to fill down will be from the active cell down to the row specified by lastrow, in a single column that the active cell is in.

The active cell will be different each time the code runs and the last row will vary each time as well.

How to I write the Filldown code to reference the range I want?

Many thanks

[redhill]

Sub testfill()

Dim first As Range
Set first = activecell

Selection.Offset(0, -3).Select
    Selection.End(xlDown).Select
        Selection.End(xlToRight).Select
            Selection.Offset(, 1).Select
            
Dim last As Range
        Set last = activecell

Selection.End(xlUp).Offset(1).Select

Dim myrange As Range
        Set myrange = Range("New!A:C")


 activecell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE)=0,""-"",VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE))"
        
With activecell
.AutoFill Destination:=Range(first, last)
End With



End Sub

I got it to work about 1 minute after I posted.

[huuthang_bd]

I got it to work about 1 minute after I posted.

It looks like the code will be very simple if you attach a sample workbook and explain what do you want.

[huuthang_bd]

Try this

Sub testfill()
Dim Rng As Range, LastRow As Integer
LastRow = WorksheetFunction.CountA(Range("A:A"))
Set Rng = Selection.Offset(0, -3).End(xlDown).End(xlToRight).Offset(, 1).End(xlUp).Offset(1)
Set Rng = Rng.Resize(LastRow - Rng.Row + 1)
Rng.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE)=0,""-"",VLOOKUP(RC[-3],New!C[-3]:C[-1],3,FALSE))"
'Rng.Select 'If you want to select the range that have been set formula
End Sub

[redhill]

Now it works I get an N/A error where there is no value for the VLookup formula, the formula isn't replacing N/A with "-". I tried an IFERROR and VLOOKUP nested but it comes back with a Type mismatch error. Any suggestions?

[redhill]

Thank you huuthang_bd that is much tidier than my code. Still getting the N/A error though.

[redhill]

Got rid of the N/A error. I didn't have the IFERROR formula correct. Thanks