Copying formula to the last active row

[NoxGalleon106]

Hi,

I have a formula in B2 that i want to drag up to the last row that has a value in column C.

Formula is:
= if(D2=“BOAUSD”, “NY to advise”, IF (D2= “TDTOR”, “Toronto to advise”, “London to advise”))

When I drag to the next row, the cell should change to D4,D5 etc.

 

[mole999]

it does for me, do you have filters in place?

 

[NoxGalleon106]

it does for me, do you have filters in place?

Hi, there are no filters.can you help me with the correct VBA code to execute the scenario I have described? The formula is in B2 and should be drag up to the last row where there is value in column C.

 

[mole999]

when you said drag I assumed you would do it manually

 Sub Macro1()
    Dim LastRow As Long
    LastRow = ActiveSheet.Range("C100000").End(xlUp).Row
    Range("B2").FormulaR1C1 = _
        "=IF(RC[2]=""BOAUSD"",""NY to advise"",IF(RC[2]=""TDTOR"",""Toronto to advise"",""London to advise""))"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
    Range("B2:B" & LastRow).Select
End Sub

 

[MARK858]

@NoxGalleon106, you apply the formula directly to the range without filling/copying (Excel is intelligent enough to increment the rows).

Sub FillFormula()
Range("B2:B" & Range("C" & Rows.Count).End(xlUp).Row).Formula = "=IF(D2=""BOAUSD2"", ""NY to advise"", IF(D2= ""TDTOR"", ""Toronto to advise"", ""London to advise""))"
End Sub