So I need to go to each line, evaluate the string against a list of possible payers, then return the formula for that particular payer. But I keep getting errors, could someone please help me figure out what is wrong with my code? I'm new to progamming and cannot figure out what the issue is.
Sub CkNoConcatenate()
'
' CkNoConcatenate Macro
'
'
Dim x As Range, y As Range, c As String
rnumbers = Rows(ActiveCell.Range("A2").End(xlDown))
Set x = ActiveSheet.Range("AL2:AL3000")
Set y = ActiveSheet.Range("AS2:AS3000")
For Each cell In x
If c Is "AETNA A04" Or "AETNA AS01" Or "AETNA H09" Or "CIGNA" Or "HUMANA AHP" Or "HUMANA INS CO" Or "HWHO" Or "HHP" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],15))"
ElseIf c = "AARP SUPPLEMENTA" Or "ANTHEM BLUE IN5C" Or "ANTHEM BLUE IN5F" Or "ANTHEM BLUE O5C" Or "HUMANA GOVT BUSI" Or "MARKETPLACE" Or "MCRIN CLAIMS" Or "OPTUM VA CCN REG" Or "WISCONSIN PHYSIC" Or "UNITEDHEALTHCARE" Or "MANAGED HEALTH" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],10))"
ElseIf c = "CIGNA EDGE TRANS" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],12))"
ElseIf c = "GERBER LIFE INS" Or "GPM HEALTH INS" Or "MUTUAL OF OMAHA" Or "OMAHA INS CO" Or "UNITED OF OMAHA" Or "UNITED WORLD" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],28))"
ElseIf c = "GOLDEN RULE INSU" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],13))"
ElseIf c = "WPS" Or "HNB - ECHO" Or "UHC GOVERNMENT E" Or "UMR" Or "UMR CITY OF HEND" Or "UMR COMPASS ROSE" Or "UMR FREEDOM MORT" Or "UMR NRECA" Or "UMR PEDCOR MANAG" Or "UMR SMC CORPORAT" Or "UNITED BEHAVIORA" Or "UMR THE CHRISTIA" Or "UMR WABASH NATIO" Then
y = FormulaR1C1 = "=CONCATENATE(RC[-7],"" - "",LEFT(RC[4],9))"
Else: y = x
End If
Next cell
End Sub
Aucun commentaire:
Enregistrer un commentaire