Writing VB for Excel

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Andreak14
    New Member
    • Aug 2008
    • 1
    #1

    Writing VB for Excel

    Hi,
    I have an excel spreadsheet that populates columns based on a user's input. I was previously handling this with If statements in excel but have hit the nesting limitation so I wrote a Case statement but it only seems to work on the first line which I know has to do with my range but every change I make to the range screws up the spreadsheet.
    Any help would be appreciated. I am attaching the code below.
    Thanks,
    A

    Function TheSelectCase()

    Select Case ("I3")
    Case "OPEN"
    Range("L3").Val ue = 1
    Case "OPEN/CATH"
    Range("L3").Val ue = 1
    Case "OPEN/MRI"
    Range("L3").Val ue = 1
    Case "OPEN/OTHER"
    Range("L3").Val ue = 1
    Case "OPEN/TTE"
    Range("L3").Val ue = 1
    Case "OPEN/CT ANGIO"
    Range("L3").Val ue = 1
    Case "CATH/OPEN"
    Range("L3").Val ue = 1
    Case "MRI/OPEN"
    Range("L3").Val ue = 1
    Case "CT ANGIO/OPEN"
    Range("L3").Val ue = 1
    Case "TTE/OPEN"
    Range("L3").Val ue = 1
    Case Else
    Range("L3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "Closed"
    Range("M3").Val ue = 1
    Case "Closed/Cath"
    Range("M3").Val ue = 1
    Case "Closed/MRI"
    Range("M3").Val ue = 1
    Case "Closed/Other"
    Range("M3").Val ue = 1
    Case "Closed/TTE"
    Range("M3").Val ue = 1
    Case "Closed/CTAngio"
    Range("M3").Val ue = 1
    Case "Cath/Closed"
    Range("M3").Val ue = 1
    Case "MRI/Closed"
    Range("M3").Val ue = 1
    Case "CT Angio/Closed"
    Range("M3").Val ue = 1
    Case "TTE/Closed"
    Range("M3").Val ue = 1
    Case Else
    Range("M3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "Cath"
    Range("N3").Val ue = 1
    Case "Cath/Open"
    Range("N3").Val ue = 1
    Case "Cath/Closed"
    Range("N3").Val ue = 1
    Case "Cath/MRI"
    Range("N3").Val ue = 1
    Case "Cath/Other"
    Range("N3").Val ue = 1
    Case "Cath/TTE"
    Range("N3").Val ue = 1
    Case "Cath/CTAngio"
    Range("N3").Val ue = 1
    Case "Cath/Non-cardiac"
    Range("N3").Val ue = 1
    Case "Open/Cath"
    Range("N3").Val ue = 1
    Case "Closed/Cath"
    Range("N3").Val ue = 1
    Case "MRI/Cath"
    Range("N3").Val ue = 1
    Case "CT Angio/Cath"
    Range("N3").Val ue = 1
    Case "TTE/Cath"
    Range("N3").Val ue = 1
    Case "Non-cardiac/Cath"
    Range("N3").Val ue = 1
    Case Else
    Range("N3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "MRI"
    Range("O3").Val ue = 1
    Case "MRI/Open"
    Range("O3").Val ue = 1
    Case "MRI/Closed"
    Range("O3").Val ue = 1
    Case "MRI/Other"
    Range("O3").Val ue = 1
    Case "MRI/TTE"
    Range("O3").Val ue = 1
    Case "MRI/CTAngio"
    Range("O3").Val ue = 1
    Case "MRI/Non-cardiac"
    Range("O3").Val ue = 1
    Case "Open/MRI"
    Range("O3").Val ue = 1
    Case "Closed/MRI"
    Range("O3").Val ue = 1
    Case "MRI/Cath"
    Range("O3").Val ue = 1
    Case "Cath/MRI"
    Range("O3").Val ue = 1
    Case "TTE/MRI"
    Range("O3").Val ue = 1
    Case "Non-cardiac/MRI"
    Range("O3").Val ue = 1
    Case Else
    Range("O3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "TTE"
    Range("P3").Val ue = 1
    Case "TTE/Open"
    Range("P3").Val ue = 1
    Case "TTE/Closed"
    Range("P3").Val ue = 1
    Case "TTE/MRI"
    Range("P3").Val ue = 1
    Case "TTE/Other"
    Range("P3").Val ue = 1
    Case "TTE/Non-cardiac"
    Range("P3").Val ue = 1
    Case "TTE/CTAngio"
    Range("P3").Val ue = 1
    Case "MRI/TTE"
    Range("P3").Val ue = 1
    Case "Open/TTE"
    Range("P3").Val ue = 1
    Case "Closed/TTE"
    Range("P3").Val ue = 1
    Case "Cath/TTE"
    Range("P3").Val ue = 1
    Case "MRI/TTE"
    Range("P3").Val ue = 1
    Case "CT Angio/TTE"
    Range("P3").Val ue = 1
    Case "Non-cardiac/TTE"
    Range("P3").Val ue = 1
    Case Else
    Range("P3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "CT Angio"
    Range("Q3").Val ue = 1
    Case "CT Angio/Open"
    Range("Q3").Val ue = 1
    Case "CT Angio/Closed"
    Range("Q3").Val ue = 1
    Case "CT Angio/MRI"
    Range("Q3").Val ue = 1
    Case "CT Angio/Other"
    Range("Q3").Val ue = 1
    Case "CT Angio/Non-cardiac"
    Range("Q3").Val ue = 1
    Case "MRI/CT Angio"
    Range("Q3").Val ue = 1
    Case "Open/CT Angio"
    Range("Q3").Val ue = 1
    Case "Closed/CT Angio"
    Range("Q3").Val ue = 1
    Case "Cath/CT Angio"
    Range("Q3").Val ue = 1
    Case "MRI/CT Angio"
    Range("Q3").Val ue = 1
    Case "Non-cardiac/CT Angio"
    Range("Q3").Val ue = 1
    Case Else
    Range("Q3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "Non-cardiac"
    Range("S3").Val ue = 1
    Case "Non-cardiac/Open"
    Range("S3").Val ue = 1
    Case "Non-cardiac/Closed"
    Range("S3").Val ue = 1
    Case "Non-cardiac/MRI"
    Range("S3").Val ue = 1
    Case "Non-cardiac/Other"
    Range("S3").Val ue = 1
    Case "Non-cardiac/TTE"
    Range("S3").Val ue = 1
    Case "Non-cardiac/Cath"
    Range("S3").Val ue = 1
    Case "Non-cardiac/CT Angio"
    Range("S3").Val ue = 1
    Case "MRI/Non-cardiac"
    Range("S3").Val ue = 1
    Case "Open/Non-cardiac"
    Range("S3").Val ue = 1
    Case "Closed/Non-cardiac"
    Range("S3").Val ue = 1
    Case "Cath/Non-cardiac"
    Range("S3").Val ue = 1
    Case "TTE/Non-cardiac"
    Range("S3").Val ue = 1
    Case "CT Angio/Non-cardiac"
    Range("S3").Val ue = 1
    Case Else
    Range("S3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "Other"
    Range("T3").Val ue = 1
    Case "Other/Open"
    Range("T3").Val ue = 1
    Case "Other/Closed"
    Range("T3").Val ue = 1
    Case "Other/MRI"
    Range("T3").Val ue = 1
    Case "Other/TTE"
    Range("T3").Val ue = 1
    Case "Other/CT Angio"
    Range("T3").Val ue = 1
    Case "Other/Non-cardiac"
    Range("T3").Val ue = 1
    Case "MRI/Other"
    Range("T3").Val ue = 1
    Case "Open/Other"
    Range("T3").Val ue = 1
    Case "Closed/Other"
    Range("T3").Val ue = 1
    Case "Cath/Other"
    Range("T3").Val ue = 1
    Case "TTE/Other"
    Range("T3").Val ue = 1
    Case "CT Angio/Other"
    Range("T3").Val ue = 1
    Case "Non-cardiac/Other"
    Range("T3").Val ue = 1
    Case Else
    Range("T3").Val ue = 0
    End Select

    Select Case Range("I3").Tex t
    Case "Standby"
    Range("R3").Val ue = 1
    Case Else
    Range("R3").Val ue = 0
    End Select
    End Function
    Tags: None
    • leoce7
      New Member
      • Aug 2008
      • 8
      #2
      Hey!

      Ok so at a quick glance ....

      Your initial case, "Select Case ("I3")" ... I'm not sure it is recognizing this because it is not told that it is a cell/single range. This statement assumes an arbitrary string "I3" rather than (what I assume you intend) the cell I3. Also, if you are having trouble changing the "value" of range I would suggest using the following instead: Cells(Row_Index , Column_Index).v alue = "blah"

        Comment

        • QVeen72
          Recognized Expert Top Contributor
          • Oct 2006
          • 1445
          #3
          Hi,

          What Leoce said, is correct, you need not refer a Range for single cell...
          just use "Cells"

          Also Your Select statement can be simplified:

          [code=vb]
          Select Case Range("I3").Tex t
          Case "Other", "Other/Open", "Other/Closed","Other/MRI","Other/CT Angio"
          Range("T3").Val ue = 1
          Case Else
          Range("T3").Val ue = 0
          End Select
          [/code]

          If same thing you are doing, then a Comma Seprated Case will work

          Regards
          Veena

            Comment

            Previous template Next
            Working...