Case Select or Nested IF VB Statement Help

K

KillerDragonKC

New Member
Joined
Sep 9, 2015
Messages
20
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
I am hoping I am on the right track with the following.

EDIT - Tried to implement this into my current sheet, and get a compile error. I am missing syntax to get this to populate in to the cells in column t of my sheet.

Code: 
Sub CurrentAdjType()
    If (H2 = 220) And (I2 = 23) And (L2 < 365) Then
       Value = "O2 CAP NON MCR"
    ElseIf (H2 = 220) And (I2 = 73) And (L2 < 365) Then
        Value = "O2 CAP MCR"
    ElseIf (H2 = 220) And (I2 <> 23 Or 73) And (L2 < 365) Then
        Value = "SALES ADJ CASH APP"
    ElseIf (H2 = 221) And (L2 < 90) Then
        Value = "SALES ACCOM MANUAL"
    ElseIf (H2 = 220) And (L2 >= 365) And (P2 <> "0") Then
        Value = "BD NET OTHER"
    ElseIf (H2 = 220) And (L2 >= 365) And (P2 = "0") Then
        Value = "PATIENT PAY BD NET"
    ElseIf (H2 = 242) And (P2 <> "0") Then
        Value = "BD NET OTHER"
    ElseIf (H2 = 221) And (L2 >= 90) Then
        Value = "PATIENT PAY BD NET"
    ElseIf (H2 = 245) Or (H2 = 246) Or (H2 = 247) Then
        Value = "XFER"
    ElseIf (H2 = 225) Then
        Value = "2 PCT SEQUESTRATION"
    ElseIf (H2 = 240) Then
        Value = "REFUND"
    Else
        Value = "UNKNOWN"
    End If
End Sub
 
Last edited:
Upvote 0
I have come to the conclusion that my though process may have been deluded in what I was needing. I have created the following in the interim, perhaps someone out there can help me fix the issue where it does not complete all the way down on some but it does on others. I have read somewhere that the SpecialCells reference can cause this issue on larger data sets, which mine is 17500 or so lines long, and others I have are longer than that.

Any guidance is appreciated;

Code: 
Sub CurrentAdjType()
Non_Cap_220_BD
Non_Cap_220_NON_BD
TRN_221_BD
TRN_225
TRN_240
TRN_242
TRN_245
TRN_246
TRN_247
Non_MCR_CAP_NON_BD
MCR_CAP_NON_BD
PP_BD_OTHER_FINAL_CHECK
    ActiveSheet.AutoFilterMode = False
Cells.Select
    Selection.EntireRow.Hidden = False
Columns("T:T").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Cells.Select
Range("A1").Select
End Sub


Sub Non_Cap_220_BD()
'
' Filter_220 Macro
' Filter 220 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="220"
        .AutoFilter Field:=9, Criteria1:="<>23", _
Operator:=xlOr, Criteria2:="<>73"
        .AutoFilter Field:=12, Criteria1:=">=365"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR BD 220'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = _
        "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub Non_Cap_220_NON_BD()
'
' Filter_220 Macro
' Filter 220 Trn Data
'

'
'AUTOFILTER DATA
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
        Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="220"
        .AutoFilter Field:=9, Criteria1:="<>23", _
Operator:=xlOr, Criteria2:="<>73"
        .AutoFilter Field:=12, Criteria1:="<365"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
' FORMULA FOR NON BD 220'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "SALES ADJ CASH APP"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_221_BD()
'
' Filter_221 Macro
' Filter 221 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="221"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR BD 221'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(RC[-8]>=90,""PATIENT PAY BD NET"",""SALES ACCOM MANUAL"")"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_225()
'
' Filter_225 Macro
' Filter 225 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="225"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR 225'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "2 PCT SEQUESTRATION"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_240()
'
' Filter_240 Macro
' Filter 240 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="240"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR 240'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "REFUND"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_242()
'
' Filter_242 Macro
' Filter 242 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="242"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR BD 242'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(((RC[-4]*1)<>0),""BD NET OTHER"",""PATIENT PAY BD NET"")"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_245()
'
' Filter_245 Macro
' Filter 245 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="245"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR 245'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=IF(RC[-12]=245,""XFER"","""")"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_246()
'
' Filter_246 Macro
' Filter 246 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="246"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR 246'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "XFER"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub TRN_247()
'
' Filter_247 Macro
' Filter 247 Trn Data
'

'
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="247"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
    ' FORMULA FOR 247'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "XFER"
    'ActiveCell.FormulaR1C1 = _
     '   "=IF(R[0]C[-4]<>""0"",""BD NET OTHER"",""PATIENT PAY BD NET"")"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub Non_MCR_CAP_NON_BD()
'
' Filter_CAPP Macro
' Filter CAPP Trn Data
'

'
'AUTOFILTER DATA
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
        Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="220"
        .AutoFilter Field:=9, Criteria1:="23"
        .AutoFilter Field:=20, Criteria1:="SALES ADJ CASH APP"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
' FORMULA FOR NON BD 220'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "O2 CAP NON MCR"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub MCR_CAP_NON_BD()
'
' Filter_MCR_CAPP Macro
' Filter MCR CAPP Trn Data
'

'
'AUTOFILTER DATA
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
        Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="220"
        .AutoFilter Field:=9, Criteria1:="73"
        .AutoFilter Field:=20, Criteria1:="SALES ADJ CASH APP"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
' FORMULA FOR NON BD 220'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "O2 CAP MCR"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub

Sub PP_BD_OTHER_FINAL_CHECK()
'
' Filter_MCR_CAPP Macro
' Filter MCR CAPP Trn Data
'

'
'AUTOFILTER DATA
 Dim Rng As Range
    Dim Rw As Long
    Dim LR As Long
    Dim LastRow As Long
    Rw = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.AutoFilterMode = False
        Set Rng = Range("A1:U" & Rw)
    With Rng
        .AutoFilter
        .AutoFilter Field:=16, Criteria1:="0"
        .AutoFilter Field:=20, Criteria1:="BD NET OTHER"
    On Error Resume Next
    End With
    'SELECT FIRST VISABLE CELL IN COLUMN AFTER AUTOFILTER
    Range("T2:T" & Rows.Count).SpecialCells(xlVisible)(1).Select
' FORMULA FOR NON BD 220'S
    Range("T2:T" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "PATIENT PAY BD NET"
'Fill formula to bottom of column
        'ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "A").End(xlUp).Row, "T"))
End Sub
 
Upvote 0
Bump...

Can the code I have entered be converted to a Case Select or Nested IF string to eliminate the autofiltering method I have currently begun to explore. The autofilter and special cell combonation tend to fail more as the reports get larger.
 
Upvote 0
The Select Case should work better.
I would roll each condition into a Boolean variable that would then be evaluated and give your response.
A UDF may work better than a VBA code to loop through as well.
 
Upvote 0
I will be completely honest here, you just spoke a lot of language that I would probably need to discover some Rosetta Stone to interpret.

I had figured that a Select Case would probably be a better solution than the one I am currently trying to employ. The problem is I have failed at getting the syntax to work as a select case.

I am not sure what the UDF you speak of is, but I will research to learn more.

I am limited to what I can get to through my companies network.

Thank you,
 
Upvote 0
UDF = User Defined Function. That way you can have your own formula to yield the desired results. If the WorkBook is shared then the UDF needs to be part of it rather than in your Personal.XLSB file (personal macros on your machine.)
Using a UDF values are updated when the worksheet calculates. If you use a macro, the values only update when the macro is run. The macro can be invoked by a change event.

Boolean = TRUE or FALSE. That's your basic evaluation. You then evaluate that to your Cases for the Select part.

MSDN (Microsoft Developer's Network) https://msdn.microsoft.com/en-us/library/cy37t14y.aspx
Watch out for Select Case examples in other languages like C# which will not help this situation.

see this thread, look at post#9 from Rick Rothstein http://www.mrexcel.com/forum/excel-questions/896136-if-function.html

In MrExcel.com | "VBA and Macros Excel 2013" Information Page there's a decent explanation of Select Case statements. Walkenbach goes a little bit farther in building a statement in Wiley: Excel 2013 Power Programming with VBA - John Walkenbach
Both are good and I use both because, while there is plenty of overlap, there are some differences in their approaches and situations addressed.
(Considering I began with Using Visual Basic 6 | Que these other titles are a much more direct help to VBA in Excel!)
 
Upvote 0
SpillerBD thank you for taking the time to assist me with this. Looks like I have some reading to do. I'll post what I come up with after I read up on those suggestions.

Thank you very much. :)
 
Upvote 0
Untill I can get my hands on the reference material that was recommended. How would I get the following code to properly reply in the cells of column t?

It processes the first cell, incorrectly, as the first row has 220 in h2, and 62 in I2, and 1372 in L2, and 6 in P2, so it shoul have returned with the value BD NET OTHER


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)
If Range("H2") = 220 And (Range("I2") <> 23 Or Range("I2") <> 73) And Range("L2") < 365 And Range("P2") = "" Then
    ActiveCell.Value = "SALES ADJ CASH APP"
ElseIf Range("H2") = 220 And Range("I2") = 23 And Range("L2") < 365 Then
    ActiveCell.Value = "O2 CAP NON MCR"
ElseIf Range("H2") = 220 And Range("I2") = 73 And Range("L2") < 365 Then
    ActiveCell.Value = "O2 CAP MCR"
ElseIf Range("H2") = 221 And Range("I2") = "" And Range("L2") < 90 Then
    ActiveCell.Value = "SALES ACCOM MANUAL"
ElseIf Range("H2") = 225 And Range("I2") = "" And Range("L2") = "" Then
    ActiveCell.Value = "2 PCT SEQUESTRATION"
ElseIf Range("H2") = 240 And Range("I2") = "" And Range("L2") = "" And Range("P2") = "" Then
    ActiveCell.Value = "REFUND"
ElseIf (Range("H2") = "220") And (Range("I2") = "") And (Range("L2") >= 365) And (Range("P2") <> "0") Then
    ActiveCell.Value = "BD NET OTHER"
ElseIf Range("H2") = 242 And Range("I2") = "" And Range("L2") = "" And Range("P2") <> "0" Then
    ActiveCell.Value = "BD NET OTHER"
ElseIf Range("H2") = 220 And Range("I2") = "" And Range("L2") >= 365 And Range("P2") = "0" Then
    ActiveCell.Value = "PATIENT PAY BD NET"
ElseIf Range("H2") = 242 And Range("I2") = "" And Range("L2") = "" And Range("P2") = "0" Then
    ActiveCell.Value = "PATIENT PAY BD NET"
ElseIf Range("H2") = 245 Then
    ActiveCell.Value = "XFER"
ElseIf Range("H2") = 246 Then
    ActiveCell.Value = "XFER"
ElseIf Range("H2") = 247 Then
    ActiveCell.Value = "XFER"
Else: ActiveCell.Value = "UNKNOWN"
                      
End If
        
Next c
End Sub
 
Upvote 0
Im trying to get the If then Else formula to evaluate the cells on the same row , the cells it is evaluating are H, I, L and P.
Thus far I can only get the macro to complete a single line, in this case T2, but it evaluates it as an Unknown value.

I know I must be missing something, probably simple, I just can not see it.

The goal is to include this into an existing macro, and have this portion of coding evaluate the cells and respond with the answer in Column T, all the way down to whenever the data ends.

I appreciate all of the advice given thus far and any to come in the future.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
171
kerm007
K
F
  • Solved
Vba loop sequence
Replies
4
Views
213
Frankroger
F
ExcelNewbie2020
  • Solved
textjoin high and low of a sequence
Replies
8
Views
175
Peter_SSs
Peter_SSs
S
  • Solved
How to Rotate Rows to Columns with Large Data Set
Replies
8
Views
240
kevin9999
K
H
  • Solved
Increasing & Decreasing order of a range of integers
2
Replies
11
Views
388
Fluff
Fluff

Forum statistics

Threads
1,215,172
Messages
6,123,443
Members
449,100
Latest member
sktz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top