Macro to Insert Formula's in C13 to C17 on sheet

[howard]

I have this Code to Insert Formula's in C13 to C17, but get an application defined error

  ws.Cells(i, 3).Formula = "='[" & filePath & fileName & "]" & sheetName & "'!D" & formulaRow

[/code] My Formula in C13 is ='C:\My Documents\[Sales Template Mark.xlsm]Sheet1'!D5

C14 and C15 increments by 1 eg C15 is ='C:\My Documents\[Sales Template Mark.xlsm]Sheet1'!D7, C 16 is 'C:\My Documents\[Sales Template Mark.xlsm]Sheet1'!D10 and C17 is 'C:\My Documents\[Sales Template Mark.xlsm]Sheet1'!D11 [/code]

The Source File is in C:\My Documents

 Sub InsertFormulas()

Dim ws As Worksheet

Dim lastRow As Long

Dim i As Long

Dim formulaRow As Long

Dim filePath As String

Dim fileName As String

Dim sheetName As String



' Set active worksheet

Set ws = activeSheet



' Correctly format file path and file name

filePath = "C:\My Documents\" ' Folder path without file name (ensure it ends with \)

fileName = "Sales Template Mark.xlsm"

sheetName = "Sheet1"



' Start with D5 in the external workbook

formulaRow = 5



' Manually adjust the pattern to match your requirement for row numbers in D (5, 6, 7, 10, 11, ...)

' Loop from C13 to C17 (adjust range as needed)

For i = 13 To 17

' Construct the formula based on the row number pattern

If formulaRow = 8 Then

formulaRow = 10 ' Skip row 8 and move to row 10

End If

ws.Cells(i, 3).Formula = "='[" & filePath & fileName & "]" & sheetName & "'!D" & formulaRow



' Increment formulaRow for the next row

formulaRow = formulaRow + 1

Next i



MsgBox "Formulas inserted successfully!", vbInformation

End Sub

It would be appreciated if somenoe could amend my code

 

[Fuji]

Should be

ws.Cells(i, 3).Formula = "='" & filePath & "[" & fileName & "]" & sheetName & "'!D" & formulaRow

 

[howard]

Many Thanks Fuji

Code works 100% now

 

[Fuji]

You are welcome and thanks for the feedback.