Sleeper: Select Case Statement Problem

[tinamiller1]

I have 2000 rows and 52 columns in a spreadsheet. I have code that looks for certain values in column H and then populates based on a select case column I. The problem is, I only wnat certain values that I have designated to populate with values and anything that does not meet my select criteria to populate with just Null. I was think about running just the select alone and then doing a macro that says if column I is null then put in null but I am sure there has to be a way to not have to do 2 macros and handle this in the below code. Just not sure how.



Sub AutoPopulate()
Dim avsDsc As Variant
Dim asRes() As String
Dim i As Long
avsDsc = Range("H2:H2000").Value2
ReDim asRes(1 To UBound(avsDsc), 1 To 1)
For i = 1 To UBound(avsDsc)
Select Case avsDsc(i, 1)
Case "Anti-Depressant Medication Management"
asRes(i, 1) = "7, 23"
Case "Anti-Depressant Medication Management - Acute Phase"
asRes(i, 1) = "7"
Case "Anti-Depressant Medication Management - Continuation Phase"
asRes(i, 1) = "23"
Case "Asthma = % with Adherent Use of Asthma Control Medications"
asRes(i, 1) = "39"
Case "Asthma HEDIS Use of Appropriate Medications"
asRes(i, 1) = "39"
Case "Breast Cancer screening rate"
asRes(i, 1) = "43"
Case "CAD (LDL < 100)"
asRes(i, 1) = "63"
Case "CAD (LDL Testing)"
asRes(i, 1) = "65"
Case "CAD B-blockers"
asRes(i, 1) = "121"
Case "CAD Identified Participants"
asRes(i, 1) = "63, 65, 121"
Case "CAD LDL Control"
asRes(i, 1) = "63"
Case "CAD LDL Management"
asRes(i, 1) = "63"
Case "CAD LDL Testing"
asRes(i, 1) = "65"
Case "CAD, CHF, Diabetes Beta Blockers post-AMI"
asRes(i, 1) = "121"
Case "Cardiovascular Conditions"
asRes(i, 1) = "63, 65"
Case "Cervical Cancer Screening rate"
asRes(i, 1) = "45"
Case "Childhood Immunization"
asRes(i, 1) = "67, 68, 69, 70, 71, 72, 73, 74, 75, 82, 93, 94, 95, 101, 103, 117, 123, 128, 138"
Case "Childhood Immunization Rate"
asRes(i, 1) = "67, 68, 69, 70, 71, 72, 73, 74, 75, 82, 93, 94, 95, 101, 103, 117, 123, 128, 138"
Case "Colorectal Cancer Screening rate"
asRes(i, 1) = "66"
Case "Controlling High Blood Pressure Rate"
asRes(i, 1) = "44"
Case "COPD - Adherent use of Short Acting Broncho-dilator Medications"
asRes(i, 1) = "122"
Case "Cornoary Artery Disease (CAD) - Beta Blocker"
asRes(i, 1) = "121"
Case "Depression"
asRes(i, 1) = "7, 23"
Case "Depression Management"
asRes(i, 1) = "7, 23"
Case "Diabetes - Controlled LDL-C Level"
asRes(i, 1) = "55"
Case "Diabetes - Hemoglobin A1c Testing"
asRes(i, 1) = "54"
Case "Diabetes - LDL-C Screening"
asRes(i, 1) = "57"
Case "Diabetes - Medical Attention to Nephropathy"
asRes(i, 1) = "58"
Case "Diabetes - Nephropathy"
asRes(i, 1) = "58"
Case "Diabetes - Retinal Eye Exam"
asRes(i, 1) = "49"
Case "Diabetes A1C Control"
asRes(i, 1) = "52"
Case "Diabetes A1C Testing"
asRes(i, 1) = "54"
Case "Diabetes HEDIS Care measures"
asRes(i, 1) = "49, 52, 54, 55, 57"
Case "Diabetes Identified Participants"
asRes(i, 1) = "49, 52, 54, 55, 57, 58"
Case "Diabetes LDL Control"
asRes(i, 1) = "55"
Case "Diabetes LDL Testing"
asRes(i, 1) = "57"
Case "Diabetes LDL-Cholesterol"
asRes(i, 1) = "57"
Case "Diabetes Management"
asRes(i, 1) = "57"
Case "Diabetes Nephropathy"
asRes(i, 1) = "58"
Case "Diabetes Testing"
asRes(i, 1) = "49, 52, 54, 55, 57, 58"
Case "Persistent Asthma"
asRes(i, 1) = "39"
Case "Post Partum Care"
asRes(i, 1) = "125"
Case "Retinal Eye Exams"
asRes(i, 1) = "49"
Case "Std: CAD (LDL Testing)"
asRes(i, 1) = "65"
Case "Std: COPD (Appropriate Medication)"
asRes(i, 1) = "122, 123"
Case "Std: Diabetes A1C Testing"
asRes(i, 1) = "54"
Case "Std: Diabetes LDL-Cholesterol"
asRes(i, 1) = "57"
Case "Std: Persistent Asthma"
asRes(i, 1) = "39"
End Select
Next i
Range("I2").Resize(UBound(asRes)).Value2 = asRes
End Sub

[SamT]

At end of Select Statement:

Case Else
asRes(i, 1) = ""
End Select

[snb]

Your code can be condensed to:


Sub M_Populate()
sn = Range("H2:H2000")

For i = 1 To UBound(sn)
Select Case sn(i, 1)
Case "Anti-Depressant Medication Management - Acute Phase"
sn(i, 1) = "7"
Case "Anti-Depressant Medication Management", "Depression", "Depression Management"
sn(i, 1) = "7, 23"
Case "Anti-Depressant Medication Management - Continuation Phase"
sn(i, 1) = "23"
Case "Asthma = % with Adherent Use of Asthma Control Medications", "Asthma HEDIS Use of Appropriate Medications", "Persistent Asthma", "Std: Persistent Asthma"
sn(i, 1) = "39"
Case "Breast Cancer screening rate"
sn(i, 1) = "43"
Case "Controlling High Blood Pressure Rate"
sn(i, 1) = "44"
Case "Cervical Cancer Screening rate"
sn(i, 1) = "45"
Case "Diabetes - Retinal Eye Exam", "Retinal Eye Exams"
sn(i, 1) = "49"
Case "Diabetes HEDIS Care measures"
sn(i, 1) = "49, 52, 54, 55, 57"
Case "Diabetes Identified Participants", "Diabetes Testing"
sn(i, 1) = "49, 52, 54, 55, 57, 58"
Case "Diabetes A1C Control"
sn(i, 1) = "52"
Case "Diabetes - Hemoglobin A1c Testing", "Diabetes A1C Testing", "Std: Diabetes A1C Testing"
sn(i, 1) = "54"
Case "Diabetes - Controlled LDL-C Level", "Diabetes LDL Control"
sn(i, 1) = "55"
Case "Diabetes - LDL-C Screening", "Diabetes LDL Testing", "Diabetes LDL-Cholesterol", "Diabetes Management", "Std: Diabetes LDL-Cholesterol"
sn(i, 1) = "57"
Case "Diabetes - Medical Attention to Nephropathy", "Diabetes - Nephropathy", "Diabetes Nephropathy"
sn(i, 1) = "58"
Case "CAD Identified Participants"
sn(i, 1) = "63, 65, 121"
Case "Cardiovascular Conditions"
sn(i, 1) = "63, 65"
Case "CAD (LDL < 100)", "CAD LDL Control", "CAD LDL Management", "Std: CAD (LDL Testing)"
sn(i, 1) = "65"
Case "Childhood Immunization", "Childhood Immunization Rate"
sn(i, 1) = "67, 68, 69, 70, 71, 72, 73, 74, 75, 82, 93, 94, 95, 101, 103, 117, 123, 128, 138"
Case "Colorectal Cancer Screening rate"
sn(i, 1) = "66"
Case "CAD B-blockers", "CAD, CHF, Diabetes Beta Blockers post-AMI", "Cornoary Artery Disease (CAD) - Beta Blocker"
sn(i, 1) = "121"
Case "COPD - Adherent use of Short Acting Broncho-dilator Medications"
sn(i, 1) = "122"
Case "Std: COPD (Appropriate Medication)"
sn(i, 1) = "122, 123"
Case "Post Partum Care"
sn(i, 1) = "125"
case else
sn(i,1)=""
End Select
Next

Range("I2").Resize(UBound(sn)) = sn
End Sub