VBA to enter formula using variables: Error 438
I'm need to input a fairly complex formula into a range of cells. I am using a range variable to input the correct range reference and I am getting a 438 error "Object doesn't support this property or method"
The error occurs in the With block when the code reaches the formula. Any help to figure out why this is not working is appreciated.
Code:
Dim lastrowOld as range
Dim ws as worksheet
Dim wbPFEP as workbook
Dim ws2PFEPUpdate as worksheet
Set wbPFEP = activeworkbook
Set ws = wbPFEP.Sheets("REPO-MOS-Tracker")
Set ws2PFEPUpdate = wbPFEP.Sheets("PFEP Update")
lastrowOld = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set r1 = ws.Range(ws.Cells(4, 5), ws.Cells(lastrowOld, 5))
Set r2 = ws.Range(ws.Cells(4, 54), ws.Cells(lastrowOld, 54))
Set r3 = ws.Range(ws.Cells(4, 1), ws.Cells(lastrowOld, 1))
'=TEXTJOIN(" | ",TRUE,IF($A5 = 'REPO-MOS-Tracker'!$E$4:$E$16483,IF('REPO-MOS-Tracker'!$BB$4:$BB$16483="Census Out",TRIM('REPO-MOS-Tracker'!$A$4:$A$16483),""),"")) <-----This is what the formula should look like when entered in each cell in the range.
With ws2PFEPUpdate.Range("C5:C" & lastrow)
.Formula2xlR1C1 = _
"=TEXTJOIN("" | "",TRUE,IF(RC1 =" & "'" & ws.Name & "'!" & r1.Address(ReferenceStyle:=xlR1C1) & ",IF(" & "'REPO-MOS-Tracker'!" & r2.Address(ReferenceStyle:=xlR1C1) & "=""Census Out"",TRIM(" & "'REPO-MOS-Tracker'!" & r3.Address(ReferenceStyle:=xlR1C1) & "),""""),""""))" <------Error happens here
.Value = .Value
End With
That was it! Thank you so much. The dangers of Find/Replace.
Quote:
Originally Posted by
p45cal
has an xl crept into:
.Formula2xlR1C1
maybe it should be:
.Formula2R1C1
?
