0

I recently started a new job and was asked to make make updates to a macro that a previous employee had written. The macro is about 250 lines of code but the update is only relevant to a small piece of it.

The relevant part of the macro inputs formulas into a table. These formulas are defined in the code and are inputted as strings (this seems odd to me, but I have very limited vba experience. Can't you define actual formula for a cell?).

I was asked to add 12 new columns of formulas which I did by defining 12 more strings and changing the range of acceptable n values to the new number of columns. However, I am now getting Run-time error 1004

Note: I have replaced the actual formulas with just a number list ("=FormulaX") to make it easier to look through my code and because it doesn't seem relevant to the question since they are just strings. Some of the strings are longer than 255 characters but they were longer before I did anything to it and it worked then.

Error: I marked where the error occurs with stars. n is equal to 23 at the time of the error

Dim FormulaArr(4 To 34) As String

    FormulaArr(4) = "=Formula1"
    FormulaArr(5) = "=Formula2"
    FormulaArr(6) = "=Formula3"
    FormulaArr(7) = "=Formula4"
    FormulaArr(8) = "=Formula5"
    FormulaArr(9) = "=Formaul6"
    FormulaArr(10) = "=Formula7"
    FormulaArr(11) = "=Formula8"
    FormulaArr(12) = "=Formula9"
    FormulaArr(13) = "=Formula10"
    FormulaArr(14) = "=Formula11"
    FormulaArr(15) = "=Formula12"
    FormulaArr(16) = "=Formula13"
    FormulaArr(17) = "=Formaula14"
    FormulaArr(18) = "=Formula15"
    FormulaArr(19) = "=Formula16"
    FormulaArr(20) = "=Formula17"
    FormulaArr(21) = "=Formula18"
    FormulaArr(22) = "=Formula19"
    FormulaArr(24) = "=Formula20"
    FormulaArr(25) = "=Formula21"
    FormulaArr(26) = "=Formaula22"
    FormulaArr(27) = "=Formula23"
    FormulaArr(28) = "=Formula24"
    FormulaArr(29) = "=Formula25"
    FormulaArr(30) = "=Formula26"
    FormulaArr(31) = "=Formula27"
    FormulaArr(32) = "=Formula28"
    FormulaArr(33) = "=Formula29"
    FormulaArr(34) = "=Formula30"

Dim n As Integer
For n = 4 To 34
    ****wbin.Sheets("Metrics").Cells(2, n).Value = FormulaArr(n)****
Next n

Set keys = wbin.Sheets("Metrics").ListObjects("MetricsTable").DataBodyRange
For Each rng In keys.Cells
    If rng.Column() <> 1 And rng.Column() <> 2 And rng.Column() <> 3 Then
        rng.Value = FormulaArr(rng.Column)
    End If
Next rng

Edit: I have done some research on the error, 1, 2, but these errors seem related to the .Formula property which I don't use.

Community
  • 1
  • 1
Imalostmerchant
  • 3
  • 2

1 Answers1

0

I'm able to reproduce the error IF I attempt to write in a formula which does not exist or is otherwise unavailable.

For example:

'test to see if I can write to the sheet
Sheet1.Cells("A1").Value = "a"

'write a formula which is valid:
Sheet1.Cells("A1").Value = "=Sum(1,2,3)"

'write a formula which will not work:
Sheet1.Cells("A1").Value = "=Sum(1,2,3"

The likely cause of your error is a typo in some of the newly-constructed formula strings.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • That worked! David, I'm honestly in awe that you were able to find the error in my code when I didn't even include the error in the post. Thank you very much. Thank you. Thank you. I incorrectly assumed that since I was just inputting a string, that the error couldn't be a bad formula. I thought it was effectively just dumping text into cells, but I guess excel still realizes it is a formula and doesn't allow a bad formula. – Imalostmerchant Jul 21 '14 at 18:02
  • It recognizes a formula if it begins with `=`. To input raw string, you have to precede that with `'` (single-quote). I just tried to do something like `=Formula(1)` and then I got the same error, so I figured most likely a bad formula construction would be the error. Cheers. – David Zemens Jul 21 '14 at 18:10