locked
adding text to cell with vba script RRS feed

  • Question

  • 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

     

    Thursday, January 18, 2007 6:05 PM

Answers

  • 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.

    Friday, January 19, 2007 10:05 PM

All replies

  • Try this.

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

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

    Thursday, January 18, 2007 11:51 PM
  • 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.
    Friday, January 19, 2007 9:36 PM
  • 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.

    Friday, January 19, 2007 10:05 PM
  • 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

    Saturday, January 20, 2007 11:07 AM
  • 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.

    Monday, January 22, 2007 7:39 PM