adding text to cell with vba script

Derek at Potters Clay

I've done this before but I am completely blanking on what I am doing wrong with adding text or in this case a formula to a cell. The line I have and am getting an error on is

Workbooks("DSL Annual Rollup").Worksheets("DSL Annual Rollup").Cells(i, 3)="January!D"&i&"February!D"&i&"March!D"&i&"April!D"&i&"May!D"&i&"June!D"&i&"July!D"&i&"August!D"&i&"September!D"&i&"October!D"&i&"November!D"&i&"December!D"&i

magicalclick

Try this.

Cells(i, 3).FormulaR1C1 = "Hello World"

Cells(i, 3) returns a range obejct. And formula is part of the range object.

Derek at Potters Clay

but how do i put 2 items on there. I'm drawing from 12 different spreadsheets. so in this case it would kind of be like putting "hello" & "world" but i have the syntax wrong.

magicalclick

What I mean is, you need FormulaR1C1 to set the formula. It is ok you say

Range("A1").FormulaR1C1 = "Hello " + "World"

But not

Range("A1") = "Hello " + "World"

Range object contains more than just your data, it has color, format, size, and all sorts of things.

Andy Pope

Perhaps this is what you are after.

i = 1
With Workbooks("DSL Annual Rollup").Worksheets("DSL Annual Rollup")
.Cells(i, 3).Formula = "=January!D" & i & " & February!D" & i & " & March!D" & i & _
" & April!D" & i & " & May!D" & i & " & June!D" & i & " & July!D" & i & _
" & August!D" & i & " & September!D" & i & " & October!D" & i & _
" & November!D" & i & " & December!D" & i
End With

When concatentating use the & in preference to +
The following demonstrates the problem.

Dim vntNum1 As Variant
Dim vntNum2 As Variant

vntNum1 = 1
vntNum2 = 2
MsgBox vntNum1 + vntNum2 & vbLf & vntNum1 & vntNum2

vntNum1 = "1"
vntNum2 = "2"
MsgBox vntNum1 + vntNum2 & vbLf & vntNum1 & vntNum2

vntNum1 = CInt(1)
vntNum2 = CInt(2)
MsgBox vntNum1 + vntNum2 & vbLf & vntNum1 & vntNum2

magicalclick

Wow, no wonder everyone uses &. + adds the numbers.

Anyway, I don't use variant, so I have to cstr() all the time, otherwise a runtime error ocurres.

adding text to cell with vba script

Re: adding text to cell with vba script

Re: adding text to cell with vba script

Re: adding text to cell with vba script

Re: adding text to cell with vba script

Re: adding text to cell with vba script