PDA

View Full Version : Range.Formula for A1-ref.style notation goes wrong



Riaaz66
07-13-2010, 02:12 AM
Hi,

I've a macro where in a part I set formulas to certain cells. Because I wanted to get rid of the R1C1 notation, I found post in the forum where you can put your formulas in the A1 notation by just using Range.Formula in stead of Range.FormulaR1C1. When I run the run the macro, the first 3 formulas are set and at the 4th it stops for debugging. What am I doing wrong?

Here is a part of the code:

Range("AN5").Formula = "=E5&I5"
Range("AO5").Formula = "=+X5"
Range("AP5").Formula = "=""Stock""&I5"
' goes wrong here - it turns yellow
Range("AQ5").Formula = "=IF(ISNA(VLOOKUP(AP5;AN:AO;2;0));""No MarketPrice"";(VLOOKUP(AP5;AN:AO;2;0)))"
Range("AR6").Formula = "=IF(AQ5=""No MarketPrice"";""No MarketValue"";-AC5*AQ5)"


Kind regards,

Riaaz66

p45cal
07-13-2010, 02:27 AM
try replacing semicolons with commas:
Range("AQ5").Formula = "=IF(ISNA(VLOOKUP(AP5,AN:AO,2,0)),""No MarketPrice"",(VLOOKUP(AP5,AN:AO,2,0)))"
Range("AR6").Formula = "=IF(AQ5=""No MarketPrice"",""No MarketValue"",-AC5*AQ5)"

Riaaz66
07-14-2010, 06:44 AM
Hey,

Your solution worked, but only in the beginning.
Every time when I run this peace of code (see below) it sometimes stops with an error message. Everytime it yellow marks another rule.

This part of the code sets formulas in certain cells:

' Commas replaced with Semicolons
Range("AN5").Formula = "=E5&I5"
Range("AO5").Formula = "=+X5"
Range("AP5").Formula = "=""Stock""&I5"
Range("AQ5").Formula = "=IF(ISNA(VLOOKUP(AP5;AN:AO;2;0));""No MarketPrice"";(VLOOKUP(AP5;AN:AO;2;0)))"
Range("AR5").Formula = "=IF(AQ5=""No MarketPrice"";""No MarketValue"";-AC5*AQ5)"
Range("AS5").Formula = "=E5&K5&AD5&AG5"
Range("AT5").Formula = "=K5&AD5&AG5"
Range("AU5").Formula = "=E5&AD5&AG5"
Range("AV5").Formula = "=IF(AND(E5=""Cash"";B5="""");""Cash"";IF(B5="""";AV4;AS5))"
Range("AW5").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)))"
Range("AX5").Formula = "=IF(ISNA(IF(AW5=""Cash"";""No SMS Account"";INDEX('SMS Data'!D:D;MATCH('Sophis 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('Sophis Daily Position'!AW:AW;'SMS Data'!L:L;0))))"
Range("AY5").Formula = "=IF(ISNA(INDEX(Sophis_SMS_Magnitude_Translate!$A$9:$A$362;MATCH(MID(AX5;1;8 );Sophis_SMS_Magnitude_Translate!$C$9:$C$362;0)));""No Translation Possible"";INDEX(Sophis_SMS_Magnitude_Translate!$A$9:$A$362;MATCH(MID(AX5;1;8);Sophis _SMS_Magnitude_Translate!$C$9:$C$362;0)))"

Can you or somebody check for me what I am doing wrong.

Thanks in advance,

Riaaz66

p45cal
07-14-2010, 06:58 AM
This, where I've replaced all semicolons with commas, works just fine here. It is helpful if the sheets "Sophis Daily Position", "SMS Data", "Sophis_SMS_Magnitude_Translate" exist:
Range("AN5").Formula = "=E5&I5"
Range("AO5").Formula = "=+X5"
Range("AP5").Formula = "=""Stock""&I5"
Range("AQ5").Formula = "=IF(ISNA(VLOOKUP(AP5,AN:AO,2,0)),""No MarketPrice"",(VLOOKUP(AP5,AN:AO,2,0)))"
Range("AR5").Formula = "=IF(AQ5=""No MarketPrice"",""No MarketValue"",-AC5*AQ5)"
Range("AS5").Formula = "=E5&K5&AD5&AG5"
Range("AT5").Formula = "=K5&AD5&AG5"
Range("AU5").Formula = "=E5&AD5&AG5"
Range("AV5").Formula = "=IF(AND(E5=""Cash"",B5=""""),""Cash"",IF(B5="""",AV4,AS5))"
Range("AW5").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)))"
Range("AX5").Formula = "=IF(ISNA(IF(AW5=""Cash"",""No SMS Account"",INDEX('SMS Data'!D:D,MATCH('Sophis 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('Sophis Daily Position'!AW:AW,'SMS Data'!L:L,0))))"
Range("AY5").Formula = "=IF(ISNA(INDEX(Sophis_SMS_Magnitude_Translate!$A$9:$A$362,MATCH(MID(AX5,1,8 ),Sophis_SMS_Magnitude_Translate!$C$9:$C$362,0))),""No Translation Possible"",INDEX(Sophis_SMS_Magnitude_Translate!$A$9:$A$362,MATCH(MID(AX5,1,8),Sophis _SMS_Magnitude_Translate!$C$9:$C$362,0)))"

Riaaz66
07-14-2010, 07:14 AM
Hey P45cal,

Thanks for your reply.
According to my settings, I have to work with semicolons instead of commas. When the macro runs, formulas in cell AN5 to AQ5 are going well and then it just stops on AR5. If the semicolons would be the problem, then I think it would stop entering the formula in cell AQ5.

Main question here is that why does the macro stops suddenly. One time it stops on cell AR5 and the other time on AW5 and sometimes it don't.

I hope you can provide me a solution.

Thanks in advance,

Riaaz66

p45cal
07-14-2010, 07:28 AM
What is the error message?

Riaaz66
07-14-2010, 08:21 AM
Hey P45cal,

Sorry it took so long to respond, I had a meeting.
The error message I get is:

Run-time error '1004':
Application-defined or Object-defined error

When I click on the DEBUG button, the row where it says: Range("AQ5").Formula etc... is marked yellow.
A few hours ago, the formula on cell AR5 was marked yellow.

May be you can solve this mystery?

Thanks in advance and kind regards,

Riaaz66

p45cal
07-14-2010, 08:33 AM
What kind of code module is te code in?
Sheet, Workbook, Standard code module?