adding text to cell with vba script

adding text to cell with vba script

• FH WANG

  Try this.

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

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

  
  

• Beat

  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.

  
  

• Benin

  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.

  
  

• MA2005

  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.
  
  

• Dean Johnson - MSFT

  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
  
  
  
  
• adding text to cell with vba script