vba R1C1 Formula Error.......

[Mallesh23]

Hi Team,


Please assist to make R1C1 formula dynamic. Thanks
Below are my code my attempted Code.getting error at formula.




Sub R1C1_Dynamic()


Dim wbk As Workbook
Dim wsh As Worksheet
Dim mwbk As Workbook
Dim msh As Worksheet


Set mwbk = ThisWorkbook
Set msh = mwbk.Worksheets("Sheet1")


Set wbk = Workbooks.Open(msh.Range("c6").Value)
Set wsh = wbk.Worksheets("Sheet1")




Dim lr As Long
lr = wsh.Range("a1").CurrentRegion.Rows.Count


wsh.Activate
wsh.Range("d2:d"& lr).FormulaR1C1 = "=CONCATENATE((RC[-3]&" "&RC[-2]))"


End Sub

Thanks
Mallesh

 

[Joe4]

The issue is the double-quotes from the space in your formula is being interpretted as text qualifiers in the building of the formula.
To get literal double-quotes in the formula, you need to double them up, like this:

wsh.Range("d2:d" & lr).FormulaR1C1 = "=CONCATENATE((RC[-3]& "" ""&RC[-2]))"

Hint: The easy way to figure this out is to turn on your Macro Recorder, record yourself entering the formula on the worksheet, stopping the Macro Recorder, and viewing the code you just recorded to create that formula.

 

[Mallesh23]

Thanks Joe for quick solution.

 

[Joe4]

You are welcome.