Excel VBA: Switch Case to read values from a column-CodePudding

I am currently attempting to use a switch case that will look into a column of numbers e.g (111, 56, 49, 92) and if the value is equal to 111 for example then I want the output to 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 the code I have written so far but this was used 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 am not 100% sure as when I run this I get a type mismatch error on this line.

Any help would be greatly appreciated.

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

CodePudding user response：

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

CodePudding user response：

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