0

this is a follow up to my post at: Excel VBA: Switch Case to read values from a column

I currently have one working switch statement that looks for a case e.g (493) from Column I, and if it finds 493 it returns "Robotics" in column G.

Now I need to create a second switch statement that looks for a case e.g (EPA0012) from column AN, and if it finds EPA0012 it will display "Accounting" in column AO. The issue I am encountering is that when I run the same code that I used for the first switch statement, instead of it populating the adjacent column with "Accounting", it instead populates every single cell in the column except for the column adjacent to EPA0012 (where it should be populating).

The issue is because of "EPA" as when I removed this and just use 0012 it worked perfectly so I am assuming I need to adjust a variable or something.

The code is as follows:

Public Sub Switch_Statement_EPA()

Dim wsSort As Worksheet
Set wsSort = Workbooks("Test.xlsm").Worksheets(2)
With wsSort
    Dim lastRow As Long
    lastRow = .Range("AN" & .Rows.Count).End(xlUp).Row
  
    Dim rng As Range
    Set rng = .Range("AN2:AN" & lastRow)
    Dim cell As Range
    
    For Each cell In rng '<--- the loop
        Select Case cell.Value
            Case EPA0012
                cell.Offset(, 1).Value = "Accounting"
        End Select
    Next
End With
MsgBox ("Done")
End Sub
SeanG1246
  • 33
  • 7
  • 4
    `Case "EPA0012"`? – Scott Craner Nov 03 '21 at 16:54
  • `EPA0012` is a value in my column. So the column contains a list of different values e.g (EPA0012, EPA0032, EPA0025) and these all correlate to different meanings. So column AN contains all of these EPA codes and I want to use a switch case to check for these EPA's in column AN and if one matches then it will display the corresponding text in column AO. – SeanG1246 Nov 03 '21 at 22:49
  • 2
    If it is a string it needs to be in quotes. Only vba variables do not need the quotes. – Scott Craner Nov 03 '21 at 23:41
  • @ScottCraner Thank you for that, I realised yesterday that I was missing the quotes and was like wow it just completely blew over my head that I needed to add that. – SeanG1246 Nov 05 '21 at 12:22

0 Answers0