INsert Formula via Macro

[franciz]

Hi all

How do I insert a formula in a column via macro, eg I want to insert

=INDEX('C:\[FXAppl.xls]Sheet1'!$C$1:$C$100,MATCH(LEFT(Sheet3!A2,4)&"*",'C:\[FXAppl.xls]Sheet1'!$B$1:$B$100,0)) into column F till the last row with
data.

Thanks

regards, xlsops

 

[Dave Peterson]

I used column A to determine the last row that should be used. And I placed the
formula in F2:F(lastrow). Your formula looks like you wanted to start in row 2.

Option Explicit
Sub testme()
Dim LastRow As Long
Dim Wks As Worksheet

Set Wks = Worksheets("sheet1")

With Wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("F2:F" & LastRow).Formula _
= "=INDEX('C:\[FXAppl.xls]Sheet1'!$C$1:$C$100," _
& "MATCH(LEFT(Sheet3!A2,4)&""*""," _
& "'C:\[FXAppl.xls]Sheet1'!$B$1:$B$100,0))"
End With
End Sub

Notice that the embedded double quotes got doubled ("*" changed to ""*"").

Hi all

How do I insert a formula in a column via macro, eg I want to insert

=INDEX('C:\[FXAppl.xls]Sheet1'!$C$1:$C$100,MATCH(LEFT(Sheet3!A2,4)&"*",'C:\[FXAppl.xls]Sheet1'!$B$1:$B$100,0)) into column F till the last row with
data.

Thanks

regards, xlsops