CODEWITHSUNDEEP
excelvbastring
Antonio Inserni
Antonio Inserni

Reputation: 19

VBA put literal value of string into an excel cell

Excel 2010 Is there a way to put the literal contents of a string variable into an excel cell?

I can achieve what I want to do with this hard code, but this code is deep inside nested subroutines. I want to soft code this, so that the contents of Formula_wanted is inserted in the cell. Here's the code that puts the value of the formula in the cell. 

   If modifier1 = "ILMROS " Then
          Range("M4").Select
          ActiveCell.FormulaR1C1 = "Sales Total"
          Range("M5").Select
          ActiveCell.FormulaR1C1 = "=[@701]+[@707]+[@708]+[@709]+[@712]"

The problem with this is that the categories change all the time. They are read from 1 table on the input sheet. If I could make it so that only this input table needed to be altered when categories change, no one would have to dig through the code to find the resulting problem.

I can get VBA to create the string that I want inside a variable, but, trying everything below, I canNOT get it to put the string from the variable into cell "M5." Per the rules of OS, I've included everything I've tried with the error received. THANK YOU for any help you can give.

   Dim Formula_wanted as string (also tried Dim as Variant, same results)

    Formula_wanted = "=[@"
              For x = 1 To UBound(ILMROS) - 1
                  Formula_wanted = Formula_wanted & ILMROS(x) & "]+[@"
              Next x
      Formula_wanted = Formula_wanted & ILMROS(UBound(ILMROS)) & "]"

  MsgBox "my formula is:  " & Formula_wanted

   'shows this in msgbox: my formula is: = "=[@701]+[@707]+[@708]+[@709]+[@712]"

   '@@@@@@@@@@@@@@@@@@@@@@

   These all failed:
  Range("Y5").Text = Formula_wanted
     ' error 424
  Range("Y5").Value = Formula_wanted
       error 1004
  Range("y5").Formula = Formula_wanted
      'error 1004
  Range("Y5").FormulaR1C1 = Formula_wanted
      error 1004
  Cells(4, 24).Select
     error 424
  ActiveCell.Text = Formula_wanted
    'error 424

  Set Ob = Range("Y5") ' Ob dim as "object"
    Ob.Value = Formula_wanted
      '1004
 Ob.Text = Formula_wanted
      '1004
  Cells(4, 24).Text = Formula_wanted
      '1004

  ActiveCell.Text = Formula_wanted
         '1004    
  Range("Y5").FormulaLocal = Formula_wanted
      'error 1004

  End Sub

Upvotes: 1

Views: 945

Answers (1)

ACCtionMan
ACCtionMan

Reputation: 511

It's not clear what your formula is trying to do, but I don't have enough reputation to comment. By setting a formula to "=[@701]+[@707]+[@708]+[@709]+[@712]" it looks like you are trying to add values from table columns named 701, 707 etc.

If this is what you are doing then either:

a) you referencing a column that doesn't exist in the table; or b) "Y5" is not in the table.

If it's a), then correct the column names. If it's b) then change your formula to

"=Table1[@701]+Table1[@707]+Table1[@708]+Table1[@709]+Table1[@712]"

If you're trying to do something else such as reference a specific row in the table, then you may need to completely rethink and look at using ListObject. I've found this link very helpful in the past.

List Object helpful reference

To clarify further, the formula value in your string variable appears to be invalid.

Example with String variable that works

Dim validFormulaString As String
validFormulaString = "=1+2"
Range("A1").Formula = validFormulaString

Example with String variable that does not work as the resulting formula is not valid

Dim invalidFormulaString As String
invalidFormulaString = "=1$2"
Range("A1").Formula = invalidFormulaString

Upvotes: 0

Related Questions