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"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))"

 

[RoryA]

In that case I would suggest you use the code to add a new sheet with a table on it, then assign the simple lookup formula. Your code can read the list values from another master workbook (preferable) or you could use a literal array of values.

 

[gigabike]

OK I probably need more education on all this, because I don't understand what that means, sorry..

 

[RoryA]

A few questions:
1. Can you get your export altered to show what you want?
2. How and from where is this code run?
3. Do you have a central location that will be accessible to anyone using the output workbooks?

 

[gigabike]

Thanks for all your help. Well, I think I can't say where it's run from, given privacy stuff... and no the reporting system can't handle changing the export to do this because it's a nightmar-ish system that they have problems enough with. 3. If I'm understanding your question correctly - there is a shared drive that everyone has access to.

 

[RoryA]

All I meant is where is the code run from - eg an add-in - and is it always run by one user, or distributed to many? (it shouldn't really matter if you have a central location where you can store the master list workbook).

If you can put a workbook somewhere with a two column list of the codes you have and the results you want, you can then do something like this:

Sub LoadDataFromWbk()
' Sample demonstrating how to return a recordset from an xlsb workbook
    Dim cn                    As Object
    Dim strQuery              As String
    Dim strSheet              As String
    Dim rst                   As Object
    Dim strFileName           As String
    Dim wks                   As Excel.Worksheet

    Const adOpenForwardOnly   As Long = 0
    Const adLockPessimistic   As Long = 2
    Const adCmdText           As Long = 1

    Set cn = CreateObject("ADODB.Connection")

    ' change file path as required
    strFileName = "C:\Master Workbook.xlsx"
    ' change sheet name as required
    strSheet = "Sheet1"

    ' open connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 XML;HDR=No"";"
        .Open
    End With
    
    strQuery = "SELECT * FROM [" & strSheet & "$];"
    
    Set rst = CreateObject("ADODB.Recordset")
    
    ' get the data from the master sheet
    rst.Open strQuery, cn, adOpenForwardOnly, adLockPessimistic, adCmdText
    
    ' check records were returned
    If Not rst.EOF Then

        Set wks = ActiveWorkbook.Worksheets.Add
        wks.Name = "RefData"
        wks.Range("A1").CopyFromRecordset rst
        wks.Range("A1").CurrentRegion.Name = "Code_Table"

        ' to use the lookup table:
        Sheets(1).Range("B1").Formula = "=VLOOKUP(A1,Code_Table,2,false)"
    End If
    rst.Close
    cn.Close

End Sub

 

[gigabike]

OK I think I need to go to excel school, that's a foreign language to me. Thanks for trying with me though!