Select Case on Numerical Filtered Data

[reberryjr]

Is there some trick to using a Case Statement on a filtered range? The underlying code isn't throwing an error, it's just not updating any values to "A" as expected.

With mD.Range("L2:M" & mDLR)
    .Replace What:="Level ", Replacement:=""
    .NumberFormat = "0"
    .Value = .Value
End With

'Converts numerical values to alpha.
mD.UsedRange.Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes

With mD.Range("A1:Z" & mDLR)
    .AutoFilter Field:=13, Criteria1:=">1"
    .AutoFilter Field:=11, Criteria1:="LM"
End With

With mD.Range("L2:L" & mDLR).SpecialCells(xlCellTypeVisible)
    For i = 1 To mDLR
        Select Case .Cells(i, "L")
            Case Is < 250000
                .Cells(i, "L").Value = "A"
        End Select
    Next i
End With

 

[Micron]

I don't know the answer to your specific question because I'm a bit of an Excel vba noob, but quite understand Access vba and know about troubleshooting.
Try

   Case Is < 250000
       .Cells(i, "L").Value = "A"
   Case Else
       debug.print .Cells(i, "L")
End Select

or mouse over your variables/references as you step through the code. After it runs look at the immediate window for outputs.
EDIT - another example of mousing over to check values would be to ensure that mDLR is not zero. You can also type in the immediate window as
?mDLR and hit Enter. Its value should be printed below that line

 

[reberryjr]

I don't know the answer to your specific question because I'm a bit of an Excel vba noob, but quite understand Access vba and know about troubleshooting.
Try

   Case Is < 250000
       .Cells(i, "L").Value = "A"
   Case Else
       debug.print .Cells(i, "L")
End Select

or mouse over your variables/references as you step through the code. After it runs look at the immediate window for outputs.
EDIT - another example of mousing over to check values would be to ensure that mDLR is not zero. You can also type in the immediate window as
?mDLR and hit Enter. Its value should be printed below that line

mDLR isn't 0. I actually scrolled over some, and see the values being entered into column W instead of L. That has me at a total loss.

 

[Akuini]

You need to use For Each:

Dim c As Range
For Each c In mD.Range("L2:L" & mDLR).SpecialCells(xlCellTypeVisible)
    If c < 250000 Then c = "A"
Next

 

[reberryjr]

You need to use For Each:

Dim c As Range
For Each c In mD.Range("L2:L" & mDLR).SpecialCells(xlCellTypeVisible)
    If c < 250000 Then c = "A"
Next

That works, but any ideas on why column W would be updated instead of column L in the Select Case statement?

 

[Micron]

Can someone explain to a noob why, if mDLR was say, 20, that the cell reference method does not work:

With mD.Range("L2:L" & 20).SpecialCells(xlCellTypeVisible)
    For i = 1 To 20
        Select Case .Cells(20, "L")
            Case Is < 250000
                .Cells(20, "L").Value = "A"
        End Select
    Next i
End With

Is it because the default value of this reference: Select Case .Cells(20, "L") is not its value but is something else? If true, then would Select Case .Cells(20, "L").Value have worked?

 

[Akuini]

That works, but any ideas on why column W would be updated instead of column L in the Select Case statement?

See if this example helps:

Sub try()

Debug.Print Range("C1").Range("C1").Address 'returns $E$1
Debug.Print Range("L1").Range("L1").Address 'returns $W$1
Debug.Print Range("L1").Cells(1, "L").Address 'returns $W$1

With Range("L2:L20").SpecialCells(xlCellTypeVisible)
   Debug.Print Cells(1, "L").Address  'returns $L$1
   Debug.Print .Cells(1, "L").Address 'returns $W$2
End With
End Sub

 

[reberryjr]

See if this example helps:

Sub try()

Debug.Print Range("C1").Range("C1").Address 'returns $E$1
Debug.Print Range("L1").Range("L1").Address 'returns $W$1
Debug.Print Range("L1").Cells(1, "L").Address 'returns $W$1

With Range("L2:L20").SpecialCells(xlCellTypeVisible)
   Debug.Print Cells(1, "L").Address  'returns $L$1
   Debug.Print .Cells(1, "L").Address 'returns $W$2
End With
End Sub

I see what it's returning, but I'm not understanding why the different columns. W is the 23rd column. 23 isn't referenced in my Select Case snippet. W is an offset of 11 from L, but 11 isn't referenced in my Select Case snippet.

 

[Peter_SSs]

Cells(1,"C") is obviously cell C1 on the worksheet. the "A", "B", "C" columns are counted from the left of the worksheet.

However, Columns("H").Cells(1,"C") is actually worksheet cell J1 because now the columns "A", "B", "C" etc are referenced as starting from column H.
Try it with Columns("H").Cells(1,"C").Select

So in your snippet, you are starting at column L (col 12) and then going .Cells(i, "L") which means going to column 12, starting at column 12. That gets you to column W