Riaaz66
07-08-2010, 02:40 AM
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: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: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_Translate!$ 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
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: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: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_Translate!$ 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