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

 

[dustinbrearton via OfficeKB.com]

You might try the below. It is a bare bones code that will do what you are
looking for. I have told the code to start on row 2. If you want it to
start with row one you can edit that below in the Range statement. F2 =
Column F row 2.

Sub Sample()

Dim lngCount As Long


lngCount = ActiveSheet.UsedRange.Rows.Count

Range("F2:F" & lngCount).Formula = "=INDEX('C:\[FXAppl.xls]Sheet1'!$C$1:
$C$100,MATCH(LEFT(Sheet3!A2,4)&""*"",'C:\[FXAppl.xls] Sheet1'!$B$1:$B$100,0))
"
ActiveSheet.Paste



End Sub

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