Manual Record Excel Macro error - insert formula into a cell

0
votes

I was trying to insert a formula directly into a cell and have it reference the cells around it based on where it is placed (R1C1). I turned on the record macro feature in excel and edited a cell with the desired formula and hit enter. Excel provided me with the following function. The odd part about this, is this is the exact formula excel gave me, and when I try to run the function, it errors out with a "run time error '1004': Application-defined or object defined error. Background on the use: The function itself is just for a budget I am creating and it uses a cell two columns over to decide whether to continue numbering or to create a subset of the number above (TR is notation for total request, so anything that is not a TR, is a subset of the total request, and will be labeled the last known number & 'A', 'B', etc)

ActiveCell.FormulaR1C1 = _
        "=IF(RC[2]=""TR"",IF(R[-1]C[2]<>""TR"",IF(R[-2]C[2]<>""TR"",IF(R[-3]C[2]<>""TR"",IF(R[-4]C[2]<>""TR"",IF(R[-5]C[2]<>""TR"",IF(R[-6]C[2]<>""TR"",IF(R[-7]C[2]<>""TR"",""add more"",R[-7]C+1),R[-6]C+1),R[-5]C+1),R[-4]C+1),R[-3]C+1),R[-2]C+1),R[-1]C+1), IF(R[-1]C[2]=""TR"",IF(RC[2]<>""TR"",R[-1]C&""A"",R[-1]C+1),IF(R[-2]C[2]=""TR"",R[-2]C&""B"",IF(R[-3]C[2]=""TR"",R[-3]C&" & "(R[-4]C[2]=""TR"",R[-4]C&""D"",IF(R[-5]C[2]=""TR"",R[-5]C&""E"",IF(R[-6]C[2]=""TR"",R[-6]C&""F"",IF(R[-7]C[2]=""TR"",R[-7]C&""G"",""""))))))))"

Any help on this will be appreciated, thanks

Edit: I'm at work, and I cant respond in line, so I decided to try here: Below is the recorded macro as is.

Sub Macro7()
'
' Macro7 Macro
'

'
ActiveCell.FormulaR1C1 = _
    "=IF(RC[2]="""","""",IF(RC[2]=""TR"",IF(R[-1]C[2]<>""TR"",IF(R[-2]C[2]<>""TR"",IF(R[-3]C[2]<>""TR"",IF(R[-4]C[2]<>""TR"",IF(R[-5]C[2]<>""TR"",IF(R[-6]C[2]<>""TR"",IF(R[-7]C[2]<>""TR"",""add more"",R[-7]C+1),R[-6]C+1),R[-5]C+1),R[-4]C+1),R[-3]C+1),R[-2]C+1),R[-1]C+1), IF(R[-1]C[2]=""TR"",IF(RC[2]<>""TR"",R[-1]C&""A"",R[-1]C+1),IF(R[-2]C[2]=""TR"",R[-2]C&""B"",IF(R[-3]" & _
    "R"",R[-3]C&""C"",IF(R[-4]C[2]=""TR"",R[-4]C&""D"",IF(R[-5]C[2]=""TR"",R[-5]C&""E"",IF(R[-6]C[2]=""TR"",R[-6]C&""F"",IF(R[-7]C[2]=""TR"",R[-7]C&""G"","""")))))))))"


Range("C121").Select


End Sub

This is the recorded macro from the record macro feature.

The formula was designed in excel, and i'm attempting to reuse it inside a macro. the reason I didn't try a select case or anything like that is because this formula was designed to dynamically change based the cells around it. So if i add a line above it and insert a new budget line (this is for a budget as referenced above), the lines below it will change accordingly.

edit:ex of what it looks like in excel:

enter image description here

2
excelvba
Can you just describe what you want to do? Your monstrous series of nested IFs suggests that a VBA Select Case or loop would be a better solution. But I can't tell what the intended result is... – Joshua Honig
I'd like to see a snapshot of your recorded formula as - is it giving a valid answer? – brettdj
I've given a snapshot of the recorded macro above: As an example of what it does: If you have a budget line, it will take the number above it, and add 1, if you have an ammendment to a budget item, you can preserve the same number, (to allow items to be grouped together) and just add a letter, a, b, c, etc. As reference above, the reason I wanted to use a formula is this budget will be updated frequently, and the formula is used to insure that the numbered items adjust automatically. Though I'm open to suggestions. – JWysocki
Just to clarify, the comments above ask to see the resulting formula. What should the formula in the cell look like after the code runs? – Doug Glancy
sorry, here it is (would be in cell c128), also posted a picture link above of the output =IF(E128="","",IF(E128="TR",IF(E127<>"TR",IF(E126<>"TR",IF(E125<>"TR",IF(E124<>"TR",IF(E123<>"TR",IF(E122<>"TR",IF(E121<>"TR","add more",C121+1),C122+1),C123+1),C124+1),C125+1),C126+1),C127+1), IF(E127="TR",IF(E128<>"TR",C127&"A",C127+1),IF(E126="TR",C126&"B",IF(E125="TR",C125&"C",IF(E124="TR",C124&"D",IF(E123="TR",C123&"E",IF(E122="TR",C122&"F",IF(E121="TR",C121&"G",""))))))))) – JWysocki

2 Answers

1
votes

You have an error in the formula, that's why VBA isn't adding the formula.

I've pulled out the formula and tried to put it in manually and excel throws a message saying there is an error.

0
votes

With all the to and fro on possible errors in the formula, it's clearly too unwieldy to debug easily, so here's an alternative:

=COUNTIF(E$16:E128,"TR")&IF(E128="TR","",IF(E127="TR","A",CHAR(CODE(MID(C127,2,1))+1)))

or in R1C1 format

=COUNTIF(R16C[2]:RC[2],"TR")&IF(RC[2]="TR","",IF(R[-1]C[2]="TR","A",CHAR(CODE(MID(R[-1]C,2,1))+1)))

And VBA code to add it to the Active Cell

ActiveCell.FormulaR1C1 = _
    "=COUNTIF(R2C[2]:RC[2],""TR"")& _
    IF(RC[2]=""TR"","""",IF(R[-1]C[2]=""TR"",""A"",CHAR(CODE(MID(R[-1]C,2,1))+1)))"

It has the added benifit of not being limited to 7 levels (well, in practice limited to 26 levels, after which it will append some odd characters)

Note: the first cell reference R2C[2] may need to be adjusted to match the starting row of your data, eg if the first rank is in row 128 change it to R128C[2]

How it works:

  • Count the number of TR's in the table down to this row.
  • If the Type column is TR, return this count
  • Otherwise, if the Type in the row above is TR, append A
  • Otherwise, get the character off the end of the Rank in the row above, increment it to the next character and append it
  • Note: If the first Type is not TR you will get unexpected results down to the first TR