VBA Select Case

[Warragul]

The select case construct is moving to the Case Else line every time the programme loops, even though there are "Longvue" & "Roseleigh" entries in the worksheet.

Option Explicit
Option Compare Text

Sub Complete()

  Dim HOComplete As Range
  Dim Client As String
  Dim JobNumber As Range
  Dim workrange1 As Range
  Dim workrange2 As Range
  Dim workrange3 As Range
  Dim Count As Integer
  Dim n As Integer
  
  
  
  Set workrange1 = Range("HOComplete")
  Set workrange3 = Range("JobNumber")
  Count = Application.WorksheetFunction.Subtotal(103, workrange3)
    For n = 1 To Count
        Worksheets("Current Projects").Activate
        Range("HOComplete").Cells(n).Select
            If ActiveCell.Value = "c" Then
                ActiveCell.Offset(0, -10).Select
                Selection.Resize(1, 22).Select
                    With Selection
                        
                            Select Case Client
                                Case "Longvue"
                                    Worksheets("Longvue").Activate
                                Case "Roseleigh"
                                    Worksheets("Roseleigh").Activate
                                Case Else
                                    Worksheets("AllOther").Activate
                            End Select
                    End With
            End If
    Next n
  

 End Sub

[Stevoni]

It looks to me that you're not populating Client, thus the application is running properly since "Longvue" and "Roseleigh" aren't in the string Client.

Set the client value to something and you should be good to go.

[Warragul]

Hi Stevoni. Thanks for that. I have tried setting client = "Woftam" (another string in the worksheet) but get the same result. What is the correct way to set the client value?

[Stevoni]

If you set the value using the following then you're setting the value correctly.

set client = "Woftam"

It's better to use the following rather than creating the variable and it may eliminate your problem. Replace your select case statement with the following.

Select case trim(sheet.cell(row,column).value)

If those fail, and you can, you can attach the xls file and I'll take a look at it.

[Warragul]

Hi Stevoni.Worked it out. Thanks.

[Stevoni]

Glad I could help, don't forget to mark the thread as solved.