Can't find what's wrong with this VBA code - Runtime Error 1004

[opazzo]

Hi there,

I keep getting a Application defined or Object defined error on this piece of code, but can't see what's wrong. Have tried a few things but I feel i need a set a fresh eyes to look at it.

Here is the code :

Dim LastRow As Long
Dim WB As Workbook
Dim SPFWB As String
Dim SPFWS As String
Dim FirstBlank As String

SPFWB = ActiveWorkbook.Name
SPFWS = ActiveSheet.Name


LastRow = Workbooks(SPFWB).Worksheets(SPFWS).Cells(Rows.Count, 4).End(xlUp).Row
Set WB = Workbooks.Open(XXXX)

WB.Sheets("Register").Range("O:" & FirstBlank).FormulaR1C1 = _
"=INDEX( '[" & SPFWB & "]" & SPFWS & "'!R2C15:R" & LastRow & "C15,MATCH(RC[18], '[" & SPFWB & "]" & SPFWS & "'!R2C39:R" & LastRow & "C39,0))"

As you can see I'm basically trying to create an index/match formula with another workbook/worksheet. I believe the error in not in the formula itself but in somewhere in the syntax.
My guess is that it is the root cause is in the reference to the workbook/worksheet SPFWB / SPFWS .

Appreciate any help.

Thanks

 

[dmt32]

You don’t say where the error is occurring but just an observation, where have you initialised “FirstBlank” variable?

WB.Sheets("Register").Range("O:" & FirstBlank).FormulaR1C1

Dave

 

[opazzo]

You don’t say where the error is occurring but just an observation, where have you initialised “FirstBlank” variable?

WB.Sheets("Register").Range("O:" & FirstBlank).FormulaR1C1

Dave

I've just copied some extracts of the code. Firstblank is basically the same as Lastrow.

The Vba error is on the formula.

 

[opazzo]

This line specifically :

WB.Sheets("Register").Range("O:" & FirstBlank).FormulaR1C1 = _
"=INDEX( '[" & SPFWB & "]" & SPFWS & "'!R2C15:R" & LastRow & "C15,MATCH(RC[18], '[" & SPFWB & "]" & SPFWS & "'!R2C39:R" & LastRow & "C39,0))"

 

[shg]

I'd suggest you assign the formula to a string variable and print that to the Immediate window. If it's correct, you should be able to copy and paste it into the cell. If not, correct the code until it is.

 

[opazzo]

Thanks guy

Found my issue as indicated in bold :

WB.Sheets("Register").Range("O:" & FirstBlank).FormulaR1C1 = _
"=INDEX( '[" & SPFWB & "]" & SPFWS & "'!R2C15:R" & LastRow & "C15,MATCH(RC[18], '[" & SPFWB & "]" & SPFWS & "'!R2C39:R" & LastRow & "C39,0))"

Since I have a single cell in range, the column is an error.

Hope this can help others.