Case Select or Nested IF VB Statement Help

KillerDragonKC · Oct 19, 2015

I've read around and do not know if a nested IF statement Would be best, or a Case Select, or there may be something else I am unaware of that would perform better in my scenario.

The responses in column T should be one of the following(answers are in bold) and should be answered for all data down column T;

SALES ADJ CASH APP
IF (H2=220) AND (L2 < 365)

SALES ACCOM MANUAL
IF (H2=221) AND (L2 < 90)

BD NET OTHER
IF (H2=220) AND (L2>=365) AND (P2<>"0")
IF (H2=220) AND (I2 = 23 OR I2 = 73) AND (L2>=365)
IF (H2=242) AND (P2<>"0")

PATIENT PAY BD NET
IF (H2=220) AND (L2>365) AND (P2="0")
IF (H2=221) AND (L2>=90)
IF (H2=242) AND (P2="0")

XFER
IF (H2 = 245) OR (H2 = 246) OR (H2 = 247)

2 PCT SEQUESTRATION
IF (H2 = 225)

O2 CAP NON MCR
IF (H2 = 220) AND (I2 = 23) AND (L2<365)

O2 CAP MCR
IF (H2 = 220) AND (I2 = 73) AND (L2<365)

REFUND
IF (H2 = 240)

UNKNOWN
IF RESPONSE DOES NOT FOLLOW ONE OF THE PREVIOUSLY LISTED.

Below is a snippet of the data showing columns I have that I am tying to use VB to reply with an answer in T that corresponds to the data in the columns indicated.

Column H is labeled Trn
Column I is labeled Rs
Column L is labeled DSO
Column P is labeled P
Column T is labeled Current Adj Type

Trn Rs DSO P Current Adj Type
220 30 138 2
220 30 138 2
220 30 138 2
220 50 138 1
220 50 126 1
220 50 97 1
220 48 117 1
220 48 27 1
220 48 27 6
220 48 87 1
220 48 87 6
220 30 39 2
220 66 80 1
220 30 89 2
220 30 89 2
242 26 962 1
242 26 962 6
220 50 61 0
221 53 241 0
221 53 88 0
220 30 142 1
220 30 142 1
220 30 142 1
220 48 113 1
220 48 113 5
220 48 205 1
220 48 174 1
220 48 189 1
220 48 160 1
220 48 85 6
220 30 53 1
220 48 95 1
220 48 95 5
220 48 95 1
220 48 95 5
220 63 458 0
220 63 427 0
220 63 273 0
220 63 273 0
220 63 263 0
220 63 264 0
220 63 243 0
220 63 243 0
220 63 243 0
220 63 243 0
220 63 215 0
220 63 28 0
220 63 28 0
220 63 28 0
220 63 103 0
220 63 103 0
220 63 103 0
221 53 157 0
221 53 65 0
220 49 27 1
220 62 509 1
220 62 509 6
220 62 509 1
220 62 509 6
221 53 287 0
221 53 256 0
220 66 117 0
220 66 117 6
220 63 56 0
220 63 79 6
220 62 541 0
220 29 21 1
220 29 22 6
220 29 20 1
220 29 20 6
220 29 23 1
220 29 23 6
220 30 448 0
240 54 294 0
240 54 264 0
240 54 233 0
240 54 324 0
240 54 70 0
220 48 115 0
220 48 115 1
220 48 142 6
220 48 142 1
220 48 53 1
220 48 53 5
220 48 52 1
220 48 52 5
220 48 35 1
220 48 35 5
221 53 200 0
220 66 95 0
220 50 63 0
221 53 228 0
221 53 228 0
221 53 228 0
220 48 305 1
220 48 305 6
220 62 305 1
220 62 305 6
220 62 91 2
220 62 91 2
220 48 89 0
220 48 89 0
220 48 89 0
220 48 130 1
220 48 130 0
220 56 87 8
220 56 87 8
220 34 125 1
220 34 125 6
220 34 117 1
220 34 117 6
220 30 105 1
220 30 106 6
220 49 87 1
220 49 87 6
220 49 23 1
220 49 23 6
220 48 161 6
240 48 161 6
220 48 130 6
240 48 130 6
220 48 161 1
220 48 130 1
220 48 124 1
220 48 124 6
220 48 124 1
220 48 124 6
220 48 124 1
220 48 124 6
220 48 124 1
220 48 124 6
220 48 119 1
220 48 119 1
242 19 261 0
242 17 261 0
242 17 233 0
242 17 204 0
242 17 173 0
242 17 143 0
242 26 207 0
242 26 207 1
220 63 93 5
220 63 93 5
240 63 93 5
240 63 93 5
220 48 104 1
221 53 165 0
221 53 133 0
221 53 133 0
220 63 131 1
220 63 131 6
220 63 162 1
220 63 162 6
220 63 131 1
220 63 131 6
220 63 98 5
220 63 98 6
220 63 68 5
220 63 68 6
242 17 214 0
220 30 68 2
220 30 63 2
220 30 63 2
220 30 96 2
220 30 96 2
220 30 96 2
220 30 95 2
220 30 95 2
220 70 213 1
220 73 213 2
220 49 201 1
220 49 201 6
220 48 95 1
220 48 95 6
220 48 95 1
220 48 95 6
220 48 95 1
220 48 95 6
240 54 23 0
240 54 23 0
220 48 106 6
240 48 106 6
220 48 105 1
220 48 100 1
220 48 100 6
220 48 126 1
220 48 126 6
220 48 23 1
220 48 23 6
220 62 82 2
220 62 82 2
240 54 52 0
240 54 21 0
220 45 52 0
220 45 21 0
220 45 82 6
220 48 95 6
220 48 95 6
220 48 95 1
220 48 95 1
220 62 39 2
220 48 121 1
220 48 121 6
220 48 121 1
220 48 121 6
220 30 127 1
220 30 127 6
220 30 128 1
220 30 128 6
220 30 27 1
221 53 60 0
221 53 58 0
221 53 58 0
221 53 55 0
221 53 28 0
220 30 54 1
220 30 86 0
220 30 54 1
220 63 86 0
220 63 86 6
220 63 86 0
220 63 86 6
220 49 86 1
220 49 86 6
220 48 100 0
220 48 82 1
220 49 63 0
220 49 69 1
220 49 83 0
220 30 40 2
220 30 40 2
220 49 52 1
220 48 79 5
240 48 79 5
220 63 51 1
220 63 51 6
220 63 51 1
220 63 51 6
220 30 26 1
220 30 26 6
220 30 72 2
220 30 69 5
220 30 69 5
220 30 62 1
220 30 62 1
220 30 27 1
220 30 27 6
220 62 53 2
220 30 27 1
220 30 27 6
220 62 51 2
220 63 23 1
220 49 61 6
220 66 946 6
240 66 946 6
220 66 762 6
240 66 762 6
220 66 582 6
240 66 582 6
220 48 95 1
242 26 692 1
242 26 663 1
242 26 632 1
220 48 124 1
220 48 124 1
220 48 108 6
220 48 108 6
240 48 108 6
240 48 108 6

Thank you ahead of time for any and all assistance offered by the viewers and helpers of this phenomenal site.

SpillerBD · Oct 29, 2015

I didn't have a suitable sample data sheet to test this on, but see how this works

Code:

Sub Current_ADJ_Type1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Data").Range("T2:T" & lr)

Select Case True
    Case (Range("H2") = 220 And (Range("I2") <> 23 Or Range("I2") <> 73) And Range("L2") < 365 And Range("P2") = "")
        ActiveCell.Value = "SALES ADJ CASH APP"
    Case (Range("H2") = 220 And Range("I2") = 23 And Range("L2") < 365)
        ActiveCell.Value = "O2 CAP NON MCR"
    Case Range("H2") = 220 And Range("I2") = 73 And Range("L2") < 365
        ActiveCell.Value = "O2 CAP MCR"
    Case Range("H2") = 221 And Range("I2") = "" And Range("L2") < 90
        ActiveCell.Value = "SALES ACCOM MANUAL"
    Case Range("H2") = 225 And Range("I2") = "" And Range("L2") = ""
        ActiveCell.Value = "2 PCT SEQUESTRATION"
    Case Range("H2") = 240 And Range("I2") = "" And Range("L2") = "" And Range("P2") = ""
        ActiveCell.Value = "REFUND"
    Case (Range("H2") = "220") And (Range("I2") = "") And (Range("L2") >= 365) And (Range("P2") <> "0")
        ActiveCell.Value = "BD NET OTHER"
    Case Range("H2") = 242 And Range("I2") = "" And Range("L2") = "" And Range("P2") <> "0"
        ActiveCell.Value = "BD NET OTHER"
    Case Range("H2") = 220 And Range("I2") = "" And Range("L2") >= 365 And Range("P2") = "0"
        ActiveCell.Value = "PATIENT PAY BD NET"
    Case Range("H2") = 242 And Range("I2") = "" And Range("L2") = "" And Range("P2") = "0"
        ActiveCell.Value = "PATIENT PAY BD NET"
    Case Range("H2") = 245
        ActiveCell.Value = "XFER"
    Case Range("H2") = 246
        ActiveCell.Value = "XFER"
    Case Range("H2") = 247
        ActiveCell.Value = "XFER"
    Case Else: ActiveCell.Value = "UNKNOWN"
                      
End Select
        
Next c
End Sub

KillerDragonKC · Oct 30, 2015

SpillerBD,

Thank you for helping me understand the syntax involved with select case. I modified it to allow me to step through the macro and see what the system is evaluating.

The only issue I have is it does not go all the way down. Is there something I'm missing here.

When I start the macro on a different cell it is still evaluating based on the H2, I2, L2 etc, not the data relevant to the row it is on.

Here is the code as I have modified it to check the expressions as it is stepped through;

Code:

Sub Current_ADJ_Type1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Data").Range("T2:T" & lr)

Select Case True
    Case (Range("H2") = 220) And (Range("I2") <> 23 Or Range("I2") <> 73) And (Range("L2") < 365)
        ActiveCell.Value = "SALES ADJ CASH APP"
    Case (Range("H2") = 220) And (Range("I2") = 23) And (Range("L2") < 365)
        ActiveCell.Value = "O2 CAP NON MCR"
    Case (Range("H2") = 220) And (Range("I2") = 73) And (Range("L2") < 365)
        ActiveCell.Value = "O2 CAP MCR"
    Case (Range("H2") = 221) And (Range("L2") < 90)
        ActiveCell.Value = "SALES ACCOM MANUAL"
    Case (Range("H2") = 225)
        ActiveCell.Value = "2 PCT SEQUESTRATION"
    Case (Range("H2") = 240)
        ActiveCell.Value = "REFUND"
    Case (Range("H2") = 220) And (Range("L2") >= 365) And (Range("P2") <> "0")
        ActiveCell.Value = "BD NET OTHER"
    Case (Range("H2") = 242) And (Range("P2") <> "0")
        ActiveCell.Value = "BD NET OTHER"
    Case (Range("H2") = 220) And (Range("L2") >= 365) And (Range("P2") = "0")
        ActiveCell.Value = "PATIENT PAY BD NET"
    Case (Range("H2") = 242) And (Range("P2") = "0")
        ActiveCell.Value = "PATIENT PAY BD NET"
    Case (Range("H2") = 245)
        ActiveCell.Value = "XFER"
    Case (Range("H2") = 246)
        ActiveCell.Value = "XFER"
    Case (Range("H2") = 247)
        ActiveCell.Value = "XFER"
    Case Else: ActiveCell.Value = "UNKNOWN"
                      
End Select
        
Next c
End Sub

SpillerBD · Oct 30, 2015

You should only have 1 active cell. Is the Active Cell in the SELECT CASE supposed to correspond to all the C in Sheets("Data") ?
So to link the corresponding Value and Evaluation you appear to be looping through

Code:

Sub Current_ADJ_Type1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Data").Range("T2:T" & lr)

Select Case True
    Case (Range("H" & Cref.Row) = 220) And (Range("I" & c.Row) <> 23 Or Range("I" & c.Row) <> 73) And (Range("L" & c.Row) < 365)
        c.Value = "SALES ADJ CASH APP"
    Case (Range("H" & c.Row) = 220) And (Range("I" & c.Row) = 23) And (Range("L" & c.Row) < 365)
        c.Value = "O2 CAP NON MCR"
    Case (Range("H" & c.Row) = 220) And (Range("I" & c.Row) = 73) And (Range("L" & c.Row) < 365)
        c.Value = "O2 CAP MCR"
    Case (Range("H" & c.Row) = 221) And (Range("L" & c.Row) < 90)
        c.Value = "SALES ACCOM MANUAL"
    Case (Range("H" & c.Row) = 225)
        c.Value = "2 PCT SEQUESTRATION"
    Case (Range("H" & c.Row) = 240)
        c.Value = "REFUND"
    Case (Range("H" & c.Row) = 220) And (Range("L" & c.Row) >= 365) And (Range("P" & c.Row) <> "0")
        c.Value = "BD NET OTHER"
    Case (Range("H" & c.Row) = 242) And (Range("P" & c.Row) <> "0")
        c.Value = "BD NET OTHER"
    Case (Range("H" & c.Row) = 220) And (Range("L" & c.Row) >= 365) And (Range("P" & c.Row) = "0")
        c.Value = "PATIENT PAY BD NET"
    Case (Range("H" & c.Row) = 242) And (Range("P" & c.Row) = "0")
        c.Value = "PATIENT PAY BD NET"
    Case (Range("H" & c.Row) = 245)
        c.Value = "XFER"
    Case (Range("H" & c.Row) = 246)
        c.Value = "XFER"
    Case (Range("H" & c.Row) = 247)
        c.Value = "XFER"
    Case Else: c.Value = "UNKNOWN"
End Select
       
Next c
End Sub

KillerDragonKC · Oct 31, 2015

SpillerBD you are a God send.

Thank you soooo much for your knowledge.

Correct me if I am incorrect with any of the following.

The select case that this is looking for, is for all variables in each case to result to be True, which is why it is listed as Select Case True, correct.

SpillerBD · Oct 31, 2015

Yes. It s an alternative method of using the Select Case Construct, even though when its boiled down; when the "Select Case Value or Variable equals the first instance of "Case Value or Variable then the statements under that case are executed and then escapes to the End Select line.

KillerDragonKC · Nov 2, 2015

Again thank you so much. I have been working on Automating this report so I could focus on other projects, instead of something so repetitive. With your assistance I was able to get my full script to work flawlessly.

More than that I am armed with a bit more knowledge in Excel.

Thanks.

Case Select or Nested IF VB Statement Help

KillerDragonKC

New Member

SpillerBD

Well-known Member

Excel Facts

KillerDragonKC

New Member

SpillerBD

Well-known Member

KillerDragonKC

New Member

SpillerBD

Well-known Member

KillerDragonKC

New Member

Similar threads

Forum statistics

Share this page

Case Select or Nested IF VB Statement Help

KillerDragonKC

New Member

SpillerBD

Well-known Member

Excel Facts

KillerDragonKC

New Member

SpillerBD

Well-known Member

KillerDragonKC

New Member

SpillerBD

Well-known Member

KillerDragonKC

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock