Hello,
I have various named ranges in my excel sheet and i recorded a macro using the named ranges in various formulas. I ran the macro and nothing is happening. Can someone let me know what I need to do to make the named ranges I created outside of the VBA to work within the VBA formula? All of my named ranges start with "MS_ID_Name_xxxxx".
Thank you,
I have various named ranges in my excel sheet and i recorded a macro using the named ranges in various formulas. I ran the macro and nothing is happening. Can someone let me know what I need to do to make the named ranges I created outside of the VBA to work within the VBA formula? All of my named ranges start with "MS_ID_Name_xxxxx".
VBA Code:
Sub MSFormulas()
'
' MSFormulas Macro
'
'.
'Define Variables
Dim lRow As Long
lRow = Range("GT" & Rows.Count).End(xlUp).Row
'Disbale Excel properties while macrro runs
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'Formula to Flag Managed Segment Hierarchy (Home)
Range("HN3:HN" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_CTI&""*"")),""X"","""")"
Range("HO3:HO" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_CAO&""*"")),""X"","""")"
Range("HP3:HP" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_CSS&""*"")),""X"","""")"
Range("HQ3:HQ" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_CISO&""*"")),""X"","""")"
Range("HR3:HR" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_COO&""*"")),""X"","""")"
Range("HS3:HS" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_Chng_Mngmnt&""*"")),""X"","""")"
Range("HT3:HT" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_Other&""*"")),""X"","""")"
Range("HU3:HU" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_GFT&""*"")),""X"","""")"
Range("HV3:HV" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_OpExcellence&""*"")),""X"","""")"
Range("HW3:HW" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_Business_Simplification&""*"")),""X"","""")"
Range("HX3:HX" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_PBWM_Ops&""*"")),""X"","""")"
Range("HY3:HY" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_PBWM_Tech&""*"")),""X"","""")"
Range("HZ3:HZ" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_NAme_ICG_Ops&""*"")),""X"","""")"
Range("IA3:IA" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_ICG_Tech&""*"")),""X"","""")"
Range("IB3:IB" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_Business&""*"")),""X"","""")"
Range("IC3:IC" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_LF_PBWM_Ops&""*"")),""X"","""")"
Range("ID3:ID" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC239,""*""&MS_ID_Name_LF_PBWM_Tech&""*"")),""X"","""")"
'Formula to Flag Managed Segment Hierarchy (Impacted)
Range("JB3:JB" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_CTI&""*"")),""X"","""")"
Range("JC3:JC" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_CAO&""*"")),""X"","""")"
Range("JD3:JD" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_CSS&""*"")),""X"","""")"
Range("JE3:JE" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_CISO&""*"")),""X"","""")"
Range("JF3:JF" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_COO&""*"")),""X"","""")"
Range("JG3:JG" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_Chng_Mngmnt&""*"")),""X"","""")"
Range("JH3:JH" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_Other&""*"")),""X"","""")"
Range("JI3:JI" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_GFT&""*"")),""X"","""")"
Range("JJ3:JJ" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_OpExcellence&""*"")),""X"","""")"
Range("JK3:JK" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_Business_Simplification&""*"")),""X"","""")"
Range("JL3:JL" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_PBWM_Ops&""*"")),""X"","""")"
Range("JM3:JM" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_PBWM_Tech&""*"")),""X"","""")"
Range("JN3:JN" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_NAme_ICG_Ops&""*"")),""X"","""")"
Range("JO3:JO" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_ICG_Tech&""*"")),""X"","""")"
Range("JP3:JP" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_Business&""*"")),""X"","""")"
Range("JQ3:JQ" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_LF_PBWM_Ops&""*"")),""X"","""")"
Range("JR3:JR" & lRow).FormulaR1C1 = "=IF(OR(COUNTIF(RC279,""*""&MS_ID_Name_LF_PBWM_Tech&""*"")),""X"","""")"
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End sub
Thank you,