opazzo
Board Regular
- Joined
- Dec 21, 2005
- Messages
- 69
- Office Version
- 365
- Platform
- Windows
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
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