Excel VBA - concatenate string

2.1k views Asked by At

I am trying to learn VBA and had gotten some help on how to increment my row number on a workbook that I am referencing by using CStr. On the below I am attempting to concatenate a cell on the other workbook with string 'year'. If I take out the 'year' it works fine but with 'year' added it does not work. There must be something I am doing wrong but I cannot figure it out.

Dim year As String
year = "16"
ActiveCell.FormulaR1C1 = "='[Junk Work File.xlsx]Total'!R" & CStr(r) & "C1" & year
1

There are 1 answers

0
coder231 On

Try using '+' instead of '&'.

    ActiveCell.FormulaR1C1 = "='[Junk Work File.xlsx]Total'!R" & CStr(r) & "C1" + year

Or

You can use Concatenate function

    ActiveCell.FormulaR1C1 = Concatenate("='[Junk Work File.xlsx]Total'!R" & CStr(r) & "C1" , year)