I am trying to create a referenced formula in VBA with combined strings, cell, and sheet references.
I am trying to create a code in VBA that will add the following formula to a cell
= ” Sample: ” & ‘Test 1 – 520 psf’!B5
I am using the following code
Dim Consol_Sheet As Worksheet
Set Consol_Sheet = Sheets(“Test 1 – 520 psf”)
ActiveSheet.Range(“B3:J3”).FormulaR1C1 = “= “”Sample: ‘”” & Consol_Sheet.Name & “”‘!”” & R[2]C”
but it does not work. I get the error #name in the cell, and the formula shows this
= “Sample: ‘” &@ Consol_Sheet.Name & “‘!” & B5
New contributor
Bernardo Castellanos is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.