macros vba need to apply formula to column till it

Question

I need to apply "IF "formula in whole C column till has last value in the sheet using VBA .

but i am getting error 438 if i use the following code . Plz help me

Sub test11()

With Sheets("Sheet")
        .Range("c1:c" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=IF(B1="",TRIM(A1),TRIM(B1))"

    End With



End Sub

Answer 1

So your sheet name is Sheet or Sheet1? And OP mentioned Sheet name is Sheet2. That removes one error. Secondly, you need to set D column as .Cells(.Rows.Count,"D").End(xlUp).Row) instead of A column.

Here is a very ugly code to try out: It takes last used row in count into the Long variable. Then set the range accordingly for setting up the formula using AutoFill.

Sub test11()
Dim l As Long    

l = Sheets(1).Range("d1:d" & Sheets(1).Cells(Sheets(1).Rows.Count, "D").End(xlUp).Row).Count
    With Sheets("Sheet1")
        .Range("d1").Formula = "=IF(IsNull(B1),TRIM(A1),TRIM(B1))"
        .Range("d1").AutoFill Destination:=Range("d1:d" & l), Type:=xlFillDefault
    End With
End Sub

Answer 2

Your logic seems a bit strange, but this works:

Sub test11()
    With Sheets("Sheet1")
        .Range(.Range("c1"), .Range("C" & .Rows.Count).End(xlUp)) = "=IF(B1="""",TRIM(A1),TRIM(B1))"
    End With
End Sub

You need to double the quotes within quotes in VBA.
Another variant:

Sub test12()
    With Sheets("Sheet1")
        Intersect(.Range("c1").CurrentRegion, .Range("C:C")).Formula = "=IF(B1="""",TRIM(A1),TRIM(B1))"
    End With
End Sub