IF formula in macro not working

[gigabike]

I have a very long IF formula that works fine itself, but when I record a macro with the formula it does not work. Here is how the formula part of the macro looks. What needs to be fixed? Thanks much

"=IF(RC[-1]=""Additional Info Received"",""Additional Info Received"",IF(RC[-1]=""Aetna Coding Error"",""Aetna Coding Error"",IF(RC[-1]=""Assignment"",""Assignment"",IF(RC[-1]=""Cap Deduct"",""Cap Deduct"",IF(RC[-1]=""Cap Deduct - Dedelegated"",""Cap Deduct - Dedelegated"",IF(RC[-1]=""Check Issued In Error"",""Check Issued In Error"",IF(RC[-1]=""Claims XTEN/Unbundling"",""Claims XTEN/Unbundling"",IF(RC[-1]=""COB"",""COB"",IF(RC[-1]=""COB Non-Health Insurance"",""COB Non-Health Insurance"",IF(RC[-1]=""COB-Commercial Not Known"",""COB-Commercial Not Known"",IF(RC[-1]=""COB-Ident Commercial Cov"",""COB-Ident Commercial Cov"",IF(RC[-1]=""COB-Ident MDCR Coverage"",""COB-Ident MDCR Coverage"",IF(RC[-1]=""COB-Medicare Not Known"",""COB-Medicare Not Known"",IF(RC[-1]=""Coinsurance Rate Error"",""Mbr OOP(copay/ded/coins)"",IF(RC[-1]=""Contract - Administration"",""Contract - Administration"",IF(RC[-1]=""Contract - Capitation"",""Contract - Capitation"",IF(RC[-1]=""Contract - Carve-Outs"",""Contract - Carve-Outs"",IF(RC[-1]=""Contract - Comments"",""Contract - Comments"",IF(RC[-1]=""Contract - DRG Facility"",""Contract - DRG Facility"",IF(RC[-1]=""Contract - Set-Up"",""Contract - Set-Up"",IF(RC[-1]=""Contract - Stop Loss"",""Contract - Stop Loss"",IF(RC[-1]=""Contracts & Rates"",""Contracts & Rates"",IF(RC[-1]=""Dental"",""Dental"",IF(RC[-1]=""Duplicates"",""Duplicates"",IF(RC[-1]=""Eligibility"",""Eligibility"",IF(RC[-1]=""External Vdr -Retro Term"",""External Vdr -Retro Term"",IF(RC[-1]=""External Vdr-Hi Cost RX"",""External Vdr-Hi Cost RX"",IF(RC[-1]=""External Vendor - COB"",""External Vendor - COB"",IF(RC[-1]=""External Vendor - DRG"",""External Vendor - DRG"",IF(RC[-1]=""External Vendor - HBA"",""External Vendor - HBA"",IF(RC[-1]=""External Vendor - Other"",""External Vendor - Other"",IF(RC[-1]=""External Vendor - Subro"",""External Vendor - Subro"",IF(RC[-1]=""External Vendor-Contract"",""External Vendor-Contract"",IF(RC[-1]=""External Vendor-Dupe"",""External Vendor-Dupe"",IF(RC[-1]=""External Vendor-Work Comp"",""External Vendor-Work Comp"",IF(RC[-1]=""Legislation"",""Legislation"",IF(RC[-1]=""Mbr OOP(copay/ded/coins)"",""Mbr OOP(copay/ded/coins)"",IF(RC[-1]=""Medical POB - Set-Up"",""Medical POB - Set-Up"",IF(RC[-1]=""Medical POB - Admin"",""Medical POB - Admin"",IF(RC[-1]=""Medicare"",""Medicare"",IF(RC[-1]=""Member copay/Ded/Coins"",""Mbr OOP(copay/ded/coins)"",IF(RC[-1]=""Other"",""Other"",IF(RC[-1]=""Other Dental"",""Other Dental"",IF(RC[-1]=""Plan of Benefits"",""Plan of Benefits"",IF(RC[-1]=""Plan Sponsor Specific"",""Plan Sponsor Specific"",IF(RC[-1]=""Policy - Auth/CXT"",""Policy - Auth/CXT"",IF(RC[-1]=""Policy - Claim Payment"",""Policy - Claim Payment"",IF(RC[-1]=""Provider Billing Error"",""Provider Billing Error"",IF(RC[-1]=""Provider Identifier"",""Provider Identifier"",IF(RC[-1]=""Provider Selection"",""Provider Selection"",IF(RC[-1]=""Provider Submission Error"",""Provider Submission Error"",IF(RC[-1]=""Replacement Check"",""Replacement Check"",IF(RC[-1]=""SIU"",""SIU"",IF(RC[-1]=""Subrogation"",""Subrogation"",IF(RC[-1]=""System"",""System"",IF(RC[-1]=""Termination"",""Termination"",IF(RC[-1]=""Workers Compensation"",""Workers Compensation"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))"

 

[Blade Hunter]

Why have you hard coded equal values? Why not just do this?

=IF(RC[-1]=""Coinsurance Rate Error"",""Mbr OOP(copay/ded/coins)"",IF(RC[-1]=""Member copay/Ded/Coins"",""Mbr OOP(copay/ded/coins)"",RC[-1]))

 

[gigabike]

Hi, I'm really new to macros and the "language", I'm not sure what you mean? When I record my macro, it works on my sheet as I intend. But then when I save and run it on the same sheet, it doesn't work...... It's something with the formula being too long or something...

 

[Blade Hunter]

OK To put it into formula terms, why do you have such a large formula in the sheet?

This achieves a similar thing:

=IF(OR(A1=""Coinsurance Rate Error"",A1=""Member copay/Ded/Coins""),""Mbr OOP(copay/ded/coins)"",A1)

These two seem to be the only ones you are changing so basically it is saying:

IF A1 = Coinsurance Rate Error OR Member copay/Ded/Coins THEN the result is Mbr OOP(copay/ded/coins) OTHERWISE the result is A1

You "should" be able to replace your WHOLE code posted above with just this:

"=IF(OR(RC[-1]=""Coinsurance Rate Error"",RC[-1]=""Member copay/Ded/Coins""),""Mbr OOP(copay/ded/coins)"",RC[-1]))"

 

[ukmikeb]

Hi, I'm really new to macros and the "language", I'm not sure what you mean? When I record my macro, it works on my sheet as I intend. But then when I save and run it on the same sheet, it doesn't work...... It's something with the formula being too long or something...

Maybe this :-
http://www.mrexcel.com/forum/excel-...array-formulas-visual-basic-applications.html

will help

 

[gigabike]

I'm sorry, that didn't come through correctly, that's not the correct formula. I am changing most of them. Below is what the actual formula looks like in excel: =IF(A1="Additional Info Received","Additional Info Received",IF(A1="Aetna Coding Error","Aetna Coding Error",IF(A1="Assignment","<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Assignment",IF(A1="Cap Deduct","Contract and Rates",IF(A1="Cap Deduct - Dedelegated","Contract and Rates",IF(A1="Check Issued in Error","Check Issued in Error",IF(A1="Claims XTEN/Unbundling","Claims XTEN/Unbundling",IF(A1="COB","<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">COB/Medicare",IF(A1="COB Non-Health Insurance","COB/Medicare",IF(<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">A1="COB-Commercial Not Known","COB/Medicare",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">COB-Ident Commercial Cov","COB/Medicare",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">COB-Ident MDCR Coverage","COB/Medicare",IF(<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">A1="COB-Medicare Not Known","COB/Medicare",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Coinsurance Rate Error","Mbr OOP(copay/ded/coins)",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Contract - Administration","Contracts and Rates",IF(A1="Contract - Capitation","Contract and Rates",IF(A1="Contract - Carve-Outs","Contract and Rates",IF(A1="Contract - Comments","Contract and Rates",IF(A1="Contract - DRG Facility","Contract and Rates",IF(A1="Contract - Set-Up","Contract and Rates",IF(A1="Contract - Stop Loss","Contract and Rates",IF(A1="Contracts & Rates","Contract and Rates",IF(A1="Dental","Dental"<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">,IF(A1="Duplicates","<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Duplicates",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Eligibility","Retro Term/Eligibility",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">External Vdr -Retro Term","Retro Term/Eligibility",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">External Vdr-Hi Cost RX","Contract and Rates",IF(A1="External Vendor - COB","COB/Medicare",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">External Vendor - DRG","Contract and Rates",IF(A1="External Vendor - HBA","Provider Submission Error",IF(A1="External Vendor - Other","External Vendor - Other",IF(A1="External Vendor - Subro","Subro/Workers Comp",IF(A1="External Vendor-Contract","Contract and Rates",IF(A1="External Vendor-Dupe","Duplicates",IF(<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">A1="External Vendor-Work Comp","Subro/Workers Comp",IF(A1="Legislation","<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Legislation",IF(A1="Mbr OOP(copay/ded/coins)","Mbr OOP(copay/ded/coins)",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Medical POB - Set-Up","Plan of benefits",IF(A1="Medical POB - Admin","Plan of benefits",IF(A1="Medicare","<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">COB/Medicare",IF(A1="Member copay/Ded/Coins","Mbr OOP(copay/ded/coins)",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Other","Other",IF(A1="Other Dental","Dental",IF(A1="Plan of Benefits","Plan of benefits",IF(A1="Plan Sponsor Specific","Plan Sponsor Specific",IF(A1="Policy - Auth/CXT","Policy",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Policy - Claim Payment","Policy",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Provider Billing Error","Provider Submission Error",IF(A1="Provider Identifier","Provider Selection",IF(A1="Provider Selection","Provider Selection",IF(A1="Provider Submission Error","Provider Submission Error",IF(A1="Replacement Check","Replacement Check",IF(A1="SIU","SIU",IF(<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">A1="Subrogation","Subro/<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Workers Comp",IF(A1="System","System",<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">IF(A1="Termination","Retro Term/Eligibility",IF(A1="<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">Workers Compensation","Subro/Workers Comp",IF(A1="Contract Interp/Calc Err","Contract and Rates"))))))))))))))))))))))))<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">))))))))))))))))))))))))))))))<wbr style="font-family: Calibri, sans-serif; font-size: 15px;">))))

 

[RoryA]

It would be much better to have a lookup table of the codes that are in A1 and the values you want to display. Then your formula would just be:
=VLOOKUP(A1,your_table,2,false)

 

[gigabike]

Sorry, I'm really new to this coding/etc - where would this table be housed? Is it something that would always be there, no matter what spreadsheet I want to use this macro on? This formula is part of a much larger macro to be used on many different sheets, and by different users

 

[RoryA]

You'd need to add the table to each workbook that uses this formula. It's regarded as bad practice to hardcode lists like that into a formula or code, not least because it makes it harder to maintain your code/formulas. If you use a table, you can simply update the table without needing to change anything else.

To address your question directly, yes there can be issues with formula length. Also, that formula wouldn't work in versions of Excel pre-2007.

 

[gigabike]

The problem is, the workbooks are exported reports from a reporting system, so it's not the same workbooks over and over, these are new workbooks so if I'm understanding what you're stating correctly, we can't hardcode a table into each workbook because they are always new workbooks coming from a reporting system..