Select Case to read values from a column

Question

I'm attempting to use a select case on a column of numbers.
e.g. (111, 56, 49, 92) if the value is equal to 111 then display "Test".

Sub Test()

Dim cd As Range
Dim wsSort As Worksheet

Set wsSort = Workbooks("Test.xlsm").Worksheets(2)
Set cd = wsSort.Columns("I")

Select Case cd
    Case Is = 111
        wsSort.Range("I10").Offset(0, -2) = "test"
End Select

MsgBox ("Done")

End Sub

This is for a single cell. So I had: Set cd = wsSort.Range("I10"), but now I need to replicate this for the entire column. Case Is = 111 I believe that I need to make the amends here but I get a type mismatch error on this line.

UPDATED CODE

Sub Test()

Dim cd As Range
Dim wsSort As Worksheet

Set wsSort = Workbooks("Learner data Elliot.xlsm").Worksheets(2)
Set cd = wsSort.Columns("I")

With wsSort
    Select Case LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
        Case Is = 111
            wsSort.Columns("I").Offset(0, -2) = "test"
    End Select
End With

MsgBox ("Done")

End Sub

You need a loop. Loop over the cells in the column, perhaps [finding the last used cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first. — BigBen, Nov 02 '21 at 16:19
You are using `Select Case` against an entire column, and I'm fairly certain that will never work. Also the variable name `wsSort` is misleading because it implies that this is sorted data or the result of a sort or something to do with a sort and it doesn't (at least not in the context of this code snippet). — FreeMan, Nov 02 '21 at 16:36
@BigBen I've looped through the cells now, however the code completes but the cell does not display "Test" so I'll need to play around with it a bit more. — SeanG1246, Nov 02 '21 at 16:37
@FreeMan Is there any alternative to using select case for an entire column? Apologies if the `wsSort` is misleading as I have used that in a separate macro that sorts data from one spreadsheet to another. Eventually this "Test" macro will be deleted and I'll move the code to the main macro. — SeanG1246, Nov 02 '21 at 16:39
Can you add your amended code to your question as it's not clear where "Test" is supposed to appear. — SJR, Nov 02 '21 at 16:42
@SJR Hopefully that makes a bit of sense, I've been playing with the loops position. I'm still quite new to this so sorry if it is all over the place. Basically I want "Test" to appear in any cell from column G (That's why I'm offsetting by 0, -2) when 111 is in column I. — SeanG1246, Nov 02 '21 at 16:51
That's ok. So you need a For-Next loop through each cell in the column. There are plenty of examples around. Or you could bypass a loop and use AutoFilter or Find, — SJR, Nov 02 '21 at 16:55
I think the confusion is that `Select Case` doesn't work against a range of cells all at once, selecting the ones that meet your criteria. As you've written it, I think that's what you're thinking. It works against a single variable `Select`ing the action to take in `Case` the value matches the given one. The official [MS docs](https://learn.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/select-case-statement) for your perusal. — FreeMan, Nov 02 '21 at 17:06
@SJR That's great! So I've almost got it working now with that. It's now displaying "Test" in column G, I just need to now adjust the output to only write "Test" in the Adjacent cell in column G and not every cell in the column. — SeanG1246, Nov 02 '21 at 17:07
So if you have a working loop and are using a range variable to represent the cell in each iteration, eg. `r` then just use `r.Offset(0, -2) = "test"`. — SJR, Nov 02 '21 at 17:12

score 1 · Accepted Answer · answered Nov 02 '21 at 17:12

1

You need a loop:

With ws
    Dim lastRow As Long
    lastRow = .Range("I" & .Rows.Count).End(xlUp).Row
  
    Dim rng As Range
    Set rng = .Range("I10:I" & lastRow)

    Dim cell As Range
    For Each cell in rng '<--- the loop
        Select Case cell.Value
            Case 111
                cell.Offset(,-2).Value = "Test"
        End Select
    Next
End With

answered Nov 02 '21 at 17:12

BigBen

46,229
7
24
40

This worked perfectly thank you!! – SeanG1246 Nov 02 '21 at 17:25
Hi Ben, I hope you're doing well. I've run into a slight problem however. I have finished my first switch statement and have moved onto the next. I have changed the the `lastrow` and `rng` range to AN and AN2:AN. my `Case` to look for is now PFR0012 etc. after running some tests it seems to be populating every row in column (AO, where I have offset it) except for the PFR0012. I believe that this is due to the PFR characters as when I remove it there is no issues. Do you know how to fix this issue? Sorry for all the hassle. – SeanG1246 Nov 03 '21 at 15:58
1

Can you perhaps ask a new question? It'll be easier and will keep this thread clean. – BigBen Nov 03 '21 at 16:00
Sure thing, I'll do that now! – SeanG1246 Nov 03 '21 at 16:33

VBasic2008 · Answer 2 · 2021-11-02T17:34:50.380

Looping Through the Cells of a Column Range

Play with the ideas between If and End If.
Choose the 'spot' (only one of three) where you will use the line Set dCell = sCell.EntireRow.Columns(dCol) which references the current destination cell.

Option Explicit

Sub Test()

    Const sCol As String = "I"
    Const dCol As String = "G"
    Const dString As String = "test"
    Const fRow As Long = 10
    
    Dim wb As Workbook: Set wb = Workbooks("Learner data Elliot.xlsm")
    ' Better use the worksheet name, because someone could move the tab.
    Dim ws As Worksheet: Set ws = wb.Worksheets(2) ' wb.Worksheets("Sheet2")
    
    Dim lRow As Long: lRow = ws.Range(sCol & ws.Rows.Count).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data in column
    Dim srg As Range: Set srg = ws.Range(sCol & fRow, sCol & lRow)
    
    Dim sCell As Range
    Dim sValue As Variant
    Dim dCell As Range
    
    For Each sCell In srg.Cells
        sValue = sCell.Value
        Set dCell = sCell.EntireRow.Columns(dCol)
        If IsNumeric(sValue) Then ' it's a number
            'Set dCell = sCell.EntireRow.Columns(dCol)
            Select Case sValue
            Case 111 ' could be more (separated by comma) e.g. 41, 111, 3
                'Set dCell = sCell.EntireRow.Columns(dCol)
                dCell.Value = dString
            ' add more cases if different action e.g.
            'Case 41
            '    dCell.Value = "TEST2"
            'Case Else
            '    dCell.Value = "Nope"
            End Select
        'Else ' it's not a number
        '    dCell.Value = ""
        End If
    Next sCell
    
    MsgBox "Done", vbInformation

End Sub

Thank you for taking the time to write all of this out. @BigBen answer has worked so far but I'll definitely come back to this and study it further to gain a better understanding of using If/End in the ways that you have. — SeanG1246, Nov 02 '21 at 17:27

Select Case to read values from a column

2 Answers2

Looping Through the Cells of a Column Range

Linked