Sleeper: Select Case Statement Problem

**tinamiller1** · 12-08-2014, 12:57 PM

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** · 12-08-2014, 02:03 PM

At end of Select Statement:

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

**snb** · 12-08-2014, 04:13 PM

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

Thread: Sleeper: Select Case Statement Problem

Thread Tools

Display

Sleeper: Select Case Statement Problem

Posting Permissions