Unable to record

[Riaaz66]

Hi,

I am preparing/writing vba code, based on both recorded actions and manual adjustments to use for a file on daily base for about 3 to 5 thousand rows. The macro has to put 14 different formulas in each column and copy it doen to the last active cell.

How do I do this (I know this is very low level, but it works)
In a sheet I copied all the needed formulas (from a test sheet), with a " ' "-sign to change it as text (just for my overview).
Now I want to add these formulas manually in the pre-recorded macro.
Due to the fact that these are pretty long formulas and that I am anonoyed-for-ever by the R1C1 style reference notation, I simply copy the formula without the " ' "-sign, push the "record macro"-button and paste the formula in just another cell. Then I open the recorded macro and use the formula in R1C1 notation and copy that to the real macro.

Problem:
When I do the steps as mentioned above and do that for a pretty long formula, I get the message "Unable to record".
Does VBA Excel 2003 has it's limitation? or is it a configuration issue? Or is it just simply not possible in VBA?

Examples of the last 3 largest formula are showed below, just to get an idea whether these are really long formulas in VBA or not. The first one started with the pop-up message "Unable to record":
1)
=IF(E5=" PCC component";AU5&MID(AV5;4;11);IF(AND((E5=" SWAP component");(LEN(MID(AV5;1;17))=LEN("TRS Divs on Reset")));AU5&MID(AV5;FIND(K5;AV5);99);IF(AND((E5=" SWAP component");(LEN(MID(AV5;1;20))=LEN("TRS Divs on Pay date")));AU5&MID(AV5;FIND(K5;AV5);99);AV5)))

2)
=IF(ISNA(IF(AW5="Cash";"No SMS Account";INDEX('SMS Data'!D;MATCH('Daily Position'!AW:AW;'SMS Data'!L:L;0))));IF(AND((MID(AW5;1;8)="TRS Divs");AD5="Buy");"34022017"&K5;IF(AND((MID(AW5;1;8)="TRS Divs");AD5="Sell");"14022017"&K5;"Not in today's SMS Report"));IF(AW5="Cash";"No SMS Account";INDEX('SMS Data'!D;MATCH('Daily Position'!AW:AW;'SMS Data'!L:L;0))))

3)
=IF(ISNA(INDEX(S_SMS_M_Translate!$A$9:$A$362;MATCH(MID(AX5;1;8);S_SMS_M_Tra nslate!$C$9:$C$362;0)));"No Translation Possible";INDEX(S_SMS_M_Translate!$A$9:$A$362;MATCH(MID(AX5;1;8);S_SMS_M_Tr anslate!$C$9:$C$362;0)))



I hope one of you VBA Veterans can help me out.

P.S. Is it possible in VBA editor to work with the A1 style reference notation in stead of the R1C! notation?


Regards,

Riaaz66

[GTO]

Greetings Riaaz,

I do not believe its anything to do with the formula's length. In copying your first formula, I had to change the semi-colons to commas for the seperators (US version). I imagine you use semi-colons for seperators?

Anyways, I think the problem may be a simple one. See where I doubled all the quote marks in the formula? Try that and see if its fixed.

Sub exa()
    Range("A1").Formula = "=IF(E5="" PCC component"",AU5&MID(AV5,4,11)," & _
                          "IF(AND((E5="" SWAP component"")," & _
                          "(LEN(MID(AV5,1,17))=LEN(""TRS Divs on Reset"")))," & _
                          "AU5&MID(AV5,FIND(K5,AV5),99),IF(AND((E5="" SWAP component"")," & _
                          "(LEN(MID(AV5,1,20))=LEN(""TRS Divs on Pay date"")))," & _
                          "AU5&MID(AV5,FIND(K5,AV5),99),AV5)))"
End Sub

Hope that helps,

Mark

[Riaaz66]

Hi GTO,

Thanx for your respond.
I will try you solution soon, but before that I want to know how you can switch in the VBA editor from the R1C1-reference style notation to the A1-notation. Can you or somebody help me with that?

Thanks in advance,

Riaaz66