Macro Help - Move/Delete based on cell criteria

cmschmitz24

cmschmitz24

Board Regular
Joined
Jan 27, 2017
Messages
150
Hello, I have quite a big ask for help on a macro I'm building (or wanting to build).
I have attached the current spreadsheet after my current macro has been ran, named UW_COBRA_QUERY_example. PS I have removed some sensitive data and replaced it with "DATA".
UW_COBRA_QUERY_example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDS
1Ticket #Completed Date
2COB.EMPLIDCOB_EMPL_RCDCOB.PLAN_TYPECOB.EVENT_DTCOB.DEPENDENT_IDCOB.RELATIONSHIPFirst NameMiddleLastCOB.EMPL_NAMECOB.APPLICANT_NAMECOB.ADDRESS1COB.ADDRESS2COB.CITYCOB.STATECOB.POSTALCountryDescrCOB.PHONECOB.BIRTHDATECOB.SEXCOB.MAR_STATUSCOB.NATIONAL_IDCOB.MEMBER_IDCOB.UW_MEMBER_IDCOB.BENEFIT_PLANCOB.ENROLL_CDCOB.UW_BN_ENROLL_RSNCombined ReasonReasonCOB.COVRG_CDCOB.EFFDTCOB.COVERAGE_LVLCOB.COVERAGE_ELECT_DTCOB.COVERAGE_END_DTCOB.TERMINATION_DTCOB.TERMINATION_REASONCOB.NOTIFIED_DTCOB.NOTICEDATECOB.BUSINESS_UNITCOB.INSTITUTION_NAMECOB.ANNUAL_PLEDGECOB.FSA_BALANCECOB.RATECOB.PROCESSEDPLAN.XLATPLAN.DESCRPLAN.GROUPCOVRG.DESCRPROMPT_EMPLID1 DEP.DEPENDENT_BENEF1 DEP.NAME1 DEP.BIRTHDATE1 DEP.SEX1 DEP.RELATIONSHIP1 DEP.DISABLED1 DEP.UW_TAX_DEPENDENT1 DEP.MAR_STATUS2 DEP.DEPENDENT_BENEF2 DEP.NAME2 DEP.BIRTHDATE2 DEP.SEX2 DEP.RELATIONSHIP2 DEP.DISABLED2 DEP.UW_TAX_DEPENDENT2 DEP.MAR_STATUS3 DEP.DEPENDENT_BENEF3 DEP.NAME3 DEP.BIRTHDATE3 DEP.SEX3 DEP.RELATIONSHIP3 DEP.DISABLED3 DEP.UW_TAX_DEPENDENT3 DEP.MAR_STATUS4 DEP.DEPENDENT_BENEF4 DEP.NAME4 DEP.BIRTHDATE4 DEP.SEX4 DEP.RELATIONSHIP4 DEP.DISABLED4 DEP.UW_TAX_DEPENDENT4 DEP.MAR_STATUS5 DEP.DEPENDENT_BENEF5 DEP.NAME5 DEP.BIRTHDATE5 DEP.SEX5 DEP.RELATIONSHIP5 DEP.DISABLED5 DEP.UW_TAX_DEPENDENT5 DEP.MAR_STATUS6 DEP.DEPENDENT_BENEF6 DEP.NAME6 DEP.BIRTHDATE6 DEP.SEX6 DEP.RELATIONSHIP6 DEP.DISABLED6 DEP.UW_TAX_DEPENDENT6 DEP.MAR_STATUS7 DEP.DEPENDENT_BENEF7 DEP.NAME7 DEP.BIRTHDATE7 DEP.SEX7 DEP.RELATIONSHIP7 DEP.DISABLED7 DEP.UW_TAX_DEPENDENT7 DEP.MAR_STATUS8 DEP.DEPENDENT_BENEF8 DEP.NAME8 DEP.BIRTHDATE8 DEP.SEX8 DEP.RELATIONSHIP8 DEP.DISABLED8 DEP.UW_TAX_DEPENDENT8 DEP.MAR_STATUSADD FieldsADD Number MonthsADD PremiumADD Other12/31/2020Plan Coverage BeginDltDntTERDelDntRETDltDntEND
3008260120106/1/201800EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATADEAN14G14GTerm11/7/20177/1/20186/30/20186/30/20180066/16/20187/1/2018DATADATA0.000.00453.680000YState Group HealthDean w/Dental1Single00826012DEA30#N/A#N/A8/29/20187/1/20186/30/2018 12/1/2019
4008260120105/1/202100EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20213/17/20215/31/20216/30/20200066/12/20205/1/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601201DATA7/22/1977MSpouseNYMDEA#NUM!#N/A#N/A7/30/20216/1/20216/30/2020 11/1/2022
5008260120105/1/202100EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20213/17/20215/31/20216/30/20200066/12/20205/1/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601202DATA5/18/2019MChildNNSDEA#NUM!#N/A#N/A7/30/20216/1/20216/30/2020 11/1/2022
6008260120105/1/202100EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20213/17/20215/31/20216/30/20200066/12/20205/1/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601203DATA3/23/2021MChildNNSDEA#NUM!#N/A#N/A7/30/20216/1/20216/30/2020 11/1/2022
7008260120106/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20216/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601201DATA7/22/1977MSpouseNYMDEA#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
8008260120106/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20216/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601202DATA5/18/2019MChildNNSDEA#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
9008260120106/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20216/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601203DATA3/23/2021MChildNNSDEA#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
10008260120146/1/201800EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATAVSP14G14GTerm11/7/20177/1/20186/30/20186/30/20180066/16/20187/1/2018DATADATA0.000.006.540000YVision InsuranceVSP Vision Insurance1Single00826012VSP30#N/A#N/A8/29/20187/1/20186/30/2018 12/1/2019
11008260120145/1/202100EDATADATADATADATADATADATADATADATADATADATA8/4/1982FMDATADATADATADLTVSN14G14GTerm1612/1/20203/17/20215/31/20216/30/20200066/12/20205/1/2021DATADATA0.000.0011.420000YVision InsuranceDeltaVision1Employee & Spouse0082601201DATA7/22/1977MSpouseNYMDLT#NUM!#N/A#N/A7/30/20216/1/20216/30/2020 11/1/2022
12008260120146/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADLTVSN14G14GTerm1612/1/20206/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.0011.420000YVision InsuranceDeltaVision1Employee & Spouse0082601201DATA7/22/1977MSpouseNYMDLT#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
COBRA
Cell Formulas
RangeFormula
AC3:AC12AC3=CONCATENATE(AA3,AB3)
AD3:AD12AD3=VLOOKUP(AC3,'Reason Translator'!A:B,2,FALSE)
DK3:DK12DK3=LEFT(Z3,3)
DL3:DL12DL3=DATEDIF(AI3,$DO$2,"M")
DM3:DM12DM3=VLOOKUP(Z3,'Reason Translator'!F:G,2,FALSE)*DL3
DN3:DN12DN3=VLOOKUP(Z3,'Reason Translator'!F:G,2,FALSE)
DO3:DO12DO3=AI3+60
DP3:DP12DP3=AI3+1
DQ3:DQ12DQ3=IF(AD3="Term",AJ3,"")
DR3:DR12DR3=IF(AD3="Retirement",AJ3,"")
DS3:DS12DS3=IF(AD3="Divorce",EDATE(D3,36),EDATE(D3,18))


1. I need to have any rows deleted if they are outside the appropriate date frame, listed in column AI. Can you help with a code that will delete rows if the date in this column is 90 days outside of today's date? Both 90 days prior to today's date and 90 days after today's date.

2. After the rows have been deleted based on date frame, I will need to have any dependents moved to their respective dependent rows/columns. Dependent data is located in columns AY-BF to start. The data will need to be moved based on column C. Therefore, if column C has a "10" only move dependents that are listed with that number in column C. Each dependent has it's own number listed in column AY. That number should match up with the headers for each dependent column. So, dependent 01 would go to columns AY-BF, dependent 02 would go to columns BG-BN, and so on through dependent 08. I assume this would be an if then statement formula with moving cells appropriately. After the dependent information has been moved, that row can be deleted. Not all will have dependents listed to be moved.
If there is a duplicate (same number in column C and same dependent number in column AY), like the one in the example for "14" in column C, the most recent date in column AI should stay.

I have attached a final version of what the spreadsheet should look like after the above, named UW_COBRA_QUERY_example_finished.
UW_COBRA_QUERY_example_finished.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDS
1Ticket #Completed Date
2COB.EMPLIDCOB_EMPL_RCDCOB.PLAN_TYPECOB.EVENT_DTCOB.DEPENDENT_IDCOB.RELATIONSHIPFirst NameMiddleLastCOB.EMPL_NAMECOB.APPLICANT_NAMECOB.ADDRESS1COB.ADDRESS2COB.CITYCOB.STATECOB.POSTALCountryDescrCOB.PHONECOB.BIRTHDATECOB.SEXCOB.MAR_STATUSCOB.NATIONAL_IDCOB.MEMBER_IDCOB.UW_MEMBER_IDCOB.BENEFIT_PLANCOB.ENROLL_CDCOB.UW_BN_ENROLL_RSNCombined ReasonReasonCOB.COVRG_CDCOB.EFFDTCOB.COVERAGE_LVLCOB.COVERAGE_ELECT_DTCOB.COVERAGE_END_DTCOB.TERMINATION_DTCOB.TERMINATION_REASONCOB.NOTIFIED_DTCOB.NOTICEDATECOB.BUSINESS_UNITCOB.INSTITUTION_NAMECOB.ANNUAL_PLEDGECOB.FSA_BALANCECOB.RATECOB.PROCESSEDPLAN.XLATPLAN.DESCRPLAN.GROUPCOVRG.DESCRPROMPT_EMPLID1 DEP.DEPENDENT_BENEF1 DEP.NAME1 DEP.BIRTHDATE1 DEP.SEX1 DEP.RELATIONSHIP1 DEP.DISABLED1 DEP.UW_TAX_DEPENDENT1 DEP.MAR_STATUS2 DEP.DEPENDENT_BENEF2 DEP.NAME2 DEP.BIRTHDATE2 DEP.SEX2 DEP.RELATIONSHIP2 DEP.DISABLED2 DEP.UW_TAX_DEPENDENT2 DEP.MAR_STATUS3 DEP.DEPENDENT_BENEF3 DEP.NAME3 DEP.BIRTHDATE3 DEP.SEX3 DEP.RELATIONSHIP3 DEP.DISABLED3 DEP.UW_TAX_DEPENDENT3 DEP.MAR_STATUS4 DEP.DEPENDENT_BENEF4 DEP.NAME4 DEP.BIRTHDATE4 DEP.SEX4 DEP.RELATIONSHIP4 DEP.DISABLED4 DEP.UW_TAX_DEPENDENT4 DEP.MAR_STATUS5 DEP.DEPENDENT_BENEF5 DEP.NAME5 DEP.BIRTHDATE5 DEP.SEX5 DEP.RELATIONSHIP5 DEP.DISABLED5 DEP.UW_TAX_DEPENDENT5 DEP.MAR_STATUS6 DEP.DEPENDENT_BENEF6 DEP.NAME6 DEP.BIRTHDATE6 DEP.SEX6 DEP.RELATIONSHIP6 DEP.DISABLED6 DEP.UW_TAX_DEPENDENT6 DEP.MAR_STATUS7 DEP.DEPENDENT_BENEF7 DEP.NAME7 DEP.BIRTHDATE7 DEP.SEX7 DEP.RELATIONSHIP7 DEP.DISABLED7 DEP.UW_TAX_DEPENDENT7 DEP.MAR_STATUS8 DEP.DEPENDENT_BENEF8 DEP.NAME8 DEP.BIRTHDATE8 DEP.SEX8 DEP.RELATIONSHIP8 DEP.DISABLED8 DEP.UW_TAX_DEPENDENT8 DEP.MAR_STATUSADD FieldsADD Number MonthsADD PremiumADD Other12/31/2020Plan Coverage BeginDltDntTERDelDntRETDltDntEND
3008260120106/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADEAN14G14GTerm153/1/20216/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.001248.660000YState Group HealthDean & Dental1Family0082601201DATA7/22/1977MSpouseNYM02DATA5/18/2019MChildNNS03DATA3/23/2021MChildNNSDEA#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
4008260120146/1/202100EDATADATADATADATADATADATADATADATADATAUSAUnited StatesDATA8/4/1982FMDATADATADATADLTVSN14G14GTerm1612/1/20206/23/20216/30/20216/22/20210016/1/20216/23/2021DATADATA0.000.0011.420000YVision InsuranceDeltaVision1Employee & Spouse0082601201DATA7/22/1977MSpouseNYMDLT#NUM!#N/A#N/A8/29/20217/1/20216/22/2021 12/1/2022
COBRA
Cell Formulas
RangeFormula
AC3:AC4AC3=CONCATENATE(AA3,AB3)
AD3:AD4AD3=VLOOKUP(AC3,'Reason Translator'!A:B,2,FALSE)
DK3:DK4DK3=LEFT(Z3,3)
DL3:DL4DL3=DATEDIF(AI3,$DO$2,"M")
DM3:DM4DM3=VLOOKUP(Z3,'Reason Translator'!F:G,2,FALSE)*DL3
DN3:DN4DN3=VLOOKUP(Z3,'Reason Translator'!F:G,2,FALSE)
DO3:DO4DO3=AI3+60
DP3:DP4DP3=AI3+1
DQ3:DQ4DQ3=IF(AD3="Term",AJ3,"")
DR3:DR4DR3=IF(AD3="Retirement",AJ3,"")
DS3:DS4DS3=IF(AD3="Divorce",EDATE(D3,36),EDATE(D3,18))


Here's my current macro.
VBA Code: 
Sub COBRAMacro()
'
' COBRAMacro Macro
'

'
    'names first tab
    Sheets("sheet1").Select
    Sheets("sheet1").Name = "COBRA"
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Reason Translator"
    
    Sheets("COBRA").Select
    
    Range("A1").Value = "Ticket #"
    Range("A1").Copy
    Range("C1").PasteSpecial
    Range("C1").Value = "Completed Date"
    Range("B1").ClearContents
    
    Columns("AZ:BD").Select
    Selection.Cut
    Columns("AR:AR").Select
    Selection.Insert Shift:=xlToRight
    Range("AW2:BD2").Select
    Selection.Copy
    Range("BE2").Select
    ActiveSheet.Paste
    Range("BM2").Select
    ActiveSheet.Paste
    Range("BU2").Select
    ActiveSheet.Paste
    Range("CC2").Select
    ActiveSheet.Paste
    Range("CK2").Select
    ActiveSheet.Paste
    Range("CS2").Select
    ActiveSheet.Paste
    Range("DA2").Select
    ActiveSheet.Paste
    
    'adds dependent headers
    Range("AW2").Value = "1 DEP.DEPENDENT_BENEF"
    Range("AX2").Value = "1 DEP.NAME"
    Range("AY2").Value = "1 DEP.BIRTHDATE"
    Range("AZ2").Value = "1 DEP.SEX"
    Range("BA2").Value = "1 DEP.RELATIONSHIP"
    Range("BB2").Value = "1 DEP.DISABLED"
    Range("BC2").Value = "1 DEP.UW_TAX_DEPENDENT"
    Range("BD2").Value = "1 DEP.MAR_STATUS"
    Range("BE2").Value = "2 DEP.DEPENDENT_BENEF"
    Range("BF2").Value = "2 DEP.NAME"
    Range("BG2").Value = "2 DEP.BIRTHDATE"
    Range("BH2").Value = "2 DEP.SEX"
    Range("BI2").Value = "2 DEP.RELATIONSHIP"
    Range("BJ2").Value = "2 DEP.DISABLED"
    Range("BK2").Value = "2 DEP.UW_TAX_DEPENDENT"
    Range("BL2").Value = "2 DEP.MAR_STATUS"
    Range("BM2").Value = "3 DEP.DEPENDENT_BENEF"
    Range("BN2").Value = "3 DEP.NAME"
    Range("BO2").Value = "3 DEP.BIRTHDATE"
    Range("BP2").Value = "3 DEP.SEX"
    Range("BQ2").Value = "3 DEP.RELATIONSHIP"
    Range("BR2").Value = "3 DEP.DISABLED"
    Range("BS2").Value = "3 DEP.UW_TAX_DEPENDENT"
    Range("BT2").Value = "3 DEP.MAR_STATUS"
    Range("BU2").Value = "4 DEP.DEPENDENT_BENEF"
    Range("BV2").Value = "4 DEP.NAME"
    Range("BW2").Value = "4 DEP.BIRTHDATE"
    Range("BX2").Value = "4 DEP.SEX"
    Range("BY2").Value = "4 DEP.RELATIONSHIP"
    Range("BZ2").Value = "4 DEP.DISABLED"
    Range("CA2").Value = "4 DEP.UW_TAX_DEPENDENT"
    Range("CB2").Value = "4 DEP.MAR_STATUS"
    Range("CC2").Value = "5 DEP.DEPENDENT_BENEF"
    Range("CD2").Value = "5 DEP.NAME"
    Range("CE2").Value = "5 DEP.BIRTHDATE"
    Range("CF2").Value = "5 DEP.SEX"
    Range("CG2").Value = "5 DEP.RELATIONSHIP"
    Range("CH2").Value = "5 DEP.DISABLED"
    Range("CI2").Value = "5 DEP.UW_TAX_DEPENDENT"
    Range("CJ2").Value = "5 DEP.MAR_STATUS"
    Range("CK2").Value = "6 DEP.DEPENDENT_BENEF"
    Range("CL2").Value = "6 DEP.NAME"
    Range("CM2").Value = "6 DEP.BIRTHDATE"
    Range("CN2").Value = "6 DEP.SEX"
    Range("CO2").Value = "6 DEP.RELATIONSHIP"
    Range("CP2").Value = "6 DEP.DISABLED"
    Range("CQ2").Value = "6 DEP.UW_TAX_DEPENDENT"
    Range("CR2").Value = "6 DEP.MAR_STATUS"
    Range("CS2").Value = "7 DEP.DEPENDENT_BENEF"
    Range("CT2").Value = "7 DEP.NAME"
    Range("CU2").Value = "7 DEP.BIRTHDATE"
    Range("CV2").Value = "7 DEP.SEX"
    Range("CW2").Value = "7 DEP.RELATIONSHIP"
    Range("CX2").Value = "7 DEP.DISABLED"
    Range("CY2").Value = "7 DEP.UW_TAX_DEPENDENT"
    Range("CZ2").Value = "7 DEP.MAR_STATUS"
    Range("DA2").Value = "8 DEP.DEPENDENT_BENEF"
    Range("DB2").Value = "8 DEP.NAME"
    Range("DC2").Value = "8 DEP.BIRTHDATE"
    Range("DD2").Value = "8 DEP.SEX"
    Range("DE2").Value = "8 DEP.RELATIONSHIP"
    Range("DF2").Value = "8 DEP.DISABLED"
    Range("DG2").Value = "8 DEP.UW_TAX_DEPENDENT"
    Range("DH2").Value = "8 DEP.MAR_STATUS"
    
    'highlights dependent headers
    Range("AW2:BD2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BE2:BL2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BM2:BT2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BU2:CB2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("CC2:CJ2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("CK2:CR2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("CS2:CZ2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("DA2:DH2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    
    
       
    'adds termination combined reason and Reason Translator tab
    Sheets("Reason Translator").Select
    
    Range("A1").Value = "14G"
    Range("A2").Value = "14G"
    Range("A3").Value = "14H"
    Range("A4").Value = "14I"
    Range("A5").Value = "07A"
    Range("A6").Value = "07B"
    Range("A7").Value = "07E"
    Range("A8").Value = "07F"
    Range("A9").Value = "07G"
    Range("A10").Value = "07I"
    Range("A11").Value = "14G020"
    Range("A12").Value = "14G021"
    Range("A13").Value = "14G022"
    Range("A14").Value = "14G023"
    Range("B1").Value = "Term"
    Range("B2").Value = "Layoff"
    Range("B3").Value = "Retirement"
    Range("B4").Value = "Death"
    Range("B5").Value = "Divorce"
    Range("B6").Value = "Divorce"
    Range("B7").Value = "Not Eligible"
    Range("B8").Value = "Not Eligible"
    Range("B9").Value = "Not Eligible"
    Range("B10").Value = "Not Eligible"
    Range("B11").Value = "Layoff"
    Range("B12").Value = "Layoff"
    Range("B13").Value = "Layoff"
    Range("B14").Value = "Layoff"

    
    Range("E1").Value = "'020"
    Range("E2").Value = "'021"
    Range("E3").Value = "'022"
    Range("E4").Value = "'023"
    
    Range("F1").Value = "ADD"
    Range("F2").Value = "ADE25"
    Range("F3").Value = "ADE50"
    Range("F4").Value = "ADE100"
    Range("F5").Value = "ADE150"
    Range("F6").Value = "ADE200"
    Range("F7").Value = "ADE250"
    Range("F8").Value = "ADE300"
    Range("F9").Value = "ADE350"
    Range("F10").Value = "ADE400"
    Range("F11").Value = "ADE450"
    Range("F12").Value = "ADE500"
    Range("F13").Value = "ADF25"
    Range("F14").Value = "ADF50"
    Range("F15").Value = "ADF100"
    Range("F16").Value = "ADF150"
    Range("F17").Value = "ADF200"
    Range("F18").Value = "ADF250"
    Range("F19").Value = "ADF300"
    Range("F20").Value = "ADF350"
    Range("F21").Value = "ADF400"
    Range("F22").Value = "ADF450"
    Range("F23").Value = "ADF500"
    
    
    Range("G2").Value = "0.73"
    Range("G3").Value = "1.45"
    Range("G4").Value = "2.9"
    Range("G5").Value = "4.35"
    Range("G6").Value = "5.8"
    Range("G7").Value = "7.25"
    Range("G8").Value = "8.7"
    Range("G9").Value = "10.15"
    Range("G10").Value = "11.6"
    Range("G11").Value = "13.05"
    Range("G12").Value = "14.5"
    Range("G13").Value = "1.1"
    Range("G14").Value = "2.2"
    Range("G15").Value = "4.4"
    Range("G16").Value = "6.6"
    Range("G17").Value = "8.8"
    Range("G18").Value = "11"
    Range("G19").Value = "13.2"
    Range("G20").Value = "15.4"
    Range("G21").Value = "17.6"
    Range("G22").Value = "19.8"
    Range("G23").Value = "22"
    Columns("G:G").NumberFormat = "0.00"
    
    Sheets("COBRA").Select
    
    Columns("AC:AC").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AC2").Value = "Combined Reason"
    Range("AC3").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
    Range("AC3").AutoFill Destination:=Range("AC3:AC" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("AD:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AD2").Value = "Reason"
    Range("AD3").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Reason Translator'!C[-29]:C[-28],2,FALSE)"
    Range("AD3").AutoFill Destination:=Range("AD3:AD" & Cells(Rows.Count, "A").End(xlUp).Row)
    
     
    'adds and formats ADD and DltDnt
    Range("DJ2").Select
    Selection.Copy
    Range("DK2").Select
    ActiveSheet.Paste
    Range("DL2").Select
    ActiveSheet.Paste
    Range("DM2").Select
    ActiveSheet.Paste
    Range("DN2").Select
    ActiveSheet.Paste
    Range("DO2").Select
    ActiveSheet.Paste
    Range("DP2").Select
    ActiveSheet.Paste
    Range("DQ2").Select
    ActiveSheet.Paste
    Range("DR2").Select
    ActiveSheet.Paste
    Range("DS2").Select
    ActiveSheet.Paste
        
    Range("DK2").Value = "ADD Fields"
    Range("DL2").Value = "ADD Number Months"
    Range("DM2").Value = "ADD Premium"
    Range("DN2").Value = "ADD Other"
    Range("DO2").Value = "12/31/2020"
    Range("DP2").Value = "Plan Coverage Begin"
    Range("DQ2").Value = "DltDntTER"
    Range("DR2").Value = "DelDntRET"
    Range("DS2").Value = "DltDntEND"
    
    Range("DK2:DP2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("DQ2:DS2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 48
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    
    'adds ADD formulas
    Range("DK3").FormulaR1C1 = "=LEFT(RC[-89],3)"
    Range("DK3").AutoFill Destination:=Range("DK3:DK" & Cells(Rows.Count, "A").End(xlUp).Row)
          
    Range("DL3").FormulaR1C1 = "=DATEDIF(RC[-81],R2C119,""M"")"
    Range("DL4").FormulaR1C1 = "=DATEDIF(RC[-81],R2C119,""M"")"
    Range("DL3:DL4").AutoFill Destination:=Range("DL3:DL" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DM3").FormulaR1C1 = _
        "=VLOOKUP(RC[-91],'Reason Translator'!C[-111]:C[-110],2,FALSE)*RC[-1]"
    Range("DM3").AutoFill Destination:=Range("DM3:DM" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DN3").FormulaR1C1 = _
        "=VLOOKUP(RC[-92],'Reason Translator'!C[-112]:C[-111],2,FALSE)"
    Range("DN3").AutoFill Destination:=Range("DN3:DN" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DO3").FormulaR1C1 = "=RC[-84]+60"
    Range("DO3").AutoFill Destination:=Range("DO3:DO" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DP3").FormulaR1C1 = "=RC[-85]+1"
    Range("DP3").AutoFill Destination:=Range("DP3:DP" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    'adds DltDnt formulas
    Range("DQ3").FormulaR1C1 = "=IF(RC[-91]=""Term"",RC[-85],"""")"
    Range("DQ3").AutoFill Destination:=Range("DQ3:DQ" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DR3").FormulaR1C1 = "=IF(RC[-92]=""Retirement"",RC[-86],"""")"
    Range("DR3").AutoFill Destination:=Range("DR3:DR" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DS3").FormulaR1C1 = _
        "=IF(RC[-93]=""Divorce"",EDATE(RC[-119],36),EDATE(RC[-119],18))"
    Range("DS3").AutoFill Destination:=Range("DS3:DS" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("DQ:DS").NumberFormat = "m/d/yyyy"
    
    
    
    Cells.EntireColumn.AutoFit
    Range("D1").Select
    ActiveWindow.FreezePanes = True
    Range("B1").Select
       
End Sub
 
Sort by date Sort by votes

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
So far this macro has been working 90%. When I have sheets that only have 1 row, my formulas bug out. Can someone help update the formulas so it doesn't bug only 1 line?

VBA Code: 
'adds ADD formulas
    Range("DK3").FormulaR1C1 = "=LEFT(RC[-89],3)"
    Range("DK3").AutoFill Destination:=Range("DK3:DK" & Cells(Rows.Count, "A").End(xlUp).Row)
          
    Range("DL3").FormulaR1C1 = "=DATEDIF(RC[-81],R2C119,""M"")"
    Range("DL3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("DL3:DL" & Cells(Rows.Count, "A").End(xlUp).Row)
        
    Range("DM3").FormulaR1C1 = _
        "=VLOOKUP(RC[-91],'Reason Translator'!C[-111]:C[-110],2,FALSE)*RC[-1]"
    Range("DM3").AutoFill Destination:=Range("DM3:DM" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DN3").FormulaR1C1 = _
        "=VLOOKUP(RC[-92],'Reason Translator'!C[-112]:C[-111],2,FALSE)"
    Range("DN3").AutoFill Destination:=Range("DN3:DN" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DO3").FormulaR1C1 = "=RC[-84]+60"
    Range("DO3").AutoFill Destination:=Range("DO3:DO" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DP3").FormulaR1C1 = "=RC[-85]+1"
    Range("DP3").AutoFill Destination:=Range("DP3:DP" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    'adds DltDnt formulas
    Range("DQ3").FormulaR1C1 = "=IF(RC[-91]=""Term"",RC[-85],"""")"
    Range("DQ3").AutoFill Destination:=Range("DQ3:DQ" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DR3").FormulaR1C1 = "=IF(RC[-92]=""Retirement"",RC[-86],"""")"
    Range("DR3").AutoFill Destination:=Range("DR3:DR" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DS3").FormulaR1C1 = _
        "=IF(RC[-93]=""Divorce"",EDATE(RC[-119],36),EDATE(RC[-119],18))"
    Range("DS3").AutoFill Destination:=Range("DS3:DS" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("DQ:DS").NumberFormat = "m/d/yyyy"
 
Upvote 0
Hi cmschmitz24,

This is a different macro than the original requirement, I suggest that you create a new thread for each new requirement. That way you will have more opportunities for other experts to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Protecting all cells in a sheet based on .TintAndShade
Replies
4
Views
91
Peter_SSs
Peter_SSs
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
262
rlv01
R
J
  • Question Question
Macro to affect cell background color
Replies
3
Views
403
mumps
mumps
E
  • Solved
How to Loop through code
Replies
3
Views
294
dreid1011
dreid1011
T
  • Question Question
Filling alternate cells with two colours?
Replies
3
Views
480
Joe4
Joe4

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top