Select sheet using a variable

Question

I am trying to select a specific sheet based on a value. I have the clients list setup with the sheet name for that client next to it. Finding the name of the sheet from the list is working, but when I try to set the worksheet variable, it errors out. Here is the code I am using:

Public Sub SeparateClients()
Dim MainL As Range: Set MainL = ML.Range("A2", ML.Range("A2").End(xlDown))
Dim ClientsF As Range
Dim cClientS As String
Dim cClient As Worksheet
Dim cClientNR As Range

For Each MainL In MainL.Cells
    If MainL.Value = "" Then Exit For
    Set ClientsF = CL.Range("A:A")
    If ClientsF.Find(MainL.Offset(0, 1).Value, , , xlWhole) Is Nothing Then
        MsgBox "Client not found: " & MainL.Offset(0, 1).Value, vbOKOnly, "ERROR: Client not found"
    Else
        cClientS = ClientsF.Find(MainL.Offset(0, 1).Value, , , xlWhole).Offset(0, 1).Value
        Set cClient = Sheets(cClientS)
        Set cClientNR = cClient.Range("A1").End(xlDown).Offset(1, 0)
        
        With cClientNR
            .Value = MainL.Offset(0, 3).Value
            .Offset(0, 1).Value = MainL.Offset(0, 5).Value
            .Offset(0, 2).Value = Mid(MainL.Offset(0, 6).Text, 1, 10)
            .Offset(0, 3).Value = MainL.Offset(0, 8).Value
            .Offset(0, 4).Value = MainL.Offset(0, 15).Value
            .Offset(0, 5).Value = MainL.Offset(0, 17).Value
            .Offset(0, 6).Value = MainL.Offset(0, 23).Value
            .Offset(0, 7).Value = MainL.Offset(0, 28).Value
        End With
    End If
Next MainL

End Sub

When it gets to set cClient = sheets(cClientS), it doesn't set the sheet for the worksheet variable. I've been searching Google for hours and I can't seem to figure out what I'm doing wrong.

What is the value of `cClientS` when the line executes that's giving you trouble? — Excel Hero, Dec 18 '20 at 00:50
Constructs like `cClientS = ClientsF.Find(MainL.Offset(0, 1).Value, , , xlWhole).Offset(0, 1).Value` are unpopular because they are fickle. `Set cClient = Sheets(cClientS)` cant succeed if `cClientS` wasn't found. Insert another test there. BTW, avoid syntax like `MainL.Offset(0, 1).Value`. Better, use syntax to address a cell when addressing a cell: You're talking about `ML.Cells(MainL.Row, "B")` or `ML.Cells(MainL.Row, 2).Value`. Apply this to *.Offset(0, 7).Value = MainL.Offset(0, 28).Value* and you will see the difference in transparency. — Variatus, Dec 18 '20 at 02:19

score 0 · Answer 1 · answered Dec 18 '20 at 01:02

0

Can you try the following:

debug.print(cClientS) ' < -- as requested by Excel Hero, let us know
set cClient = thisworkbook.sheets(cClientS) ' < -

answered Dec 18 '20 at 01:02

ko_00

118
7

Siddharth Rout · Answer 2 · 2020-12-18T06:19:11.373

Few tips

Optimizing the VBA Code and improve the performance
Whenever using .Find, always check if .Find was successful. You may want to read up on .Find and .FindNext
Avoid the use of xlDown to find the last row. You may want to read up on Error in finding last used cell in Excel with VBA
For Each MainL In MainL.Cells Use your objects carefully or you will end up confusing yourself. Like I got confused when I was going through your code.
When setting the name of the sheet, check if it is a valid name else your code will error out.
Use .Offset only when you are unsure of the the cell address. (Just my opinion. Feel free to use it if you still want to.). As @Variatus mentioned, if you know the address of the range then use that. The code will be easier to read and understand.

Is this what you are trying? (Untested)

I have commented the code below but if you still face a problem with it then simply ask.

Code

Option Explicit

Public Sub SeparateClients()
    Dim ML As Worksheet
    Dim CL As Worksheet
    
    '~~> Change these to relevant sheets
    Set ML = Sheet1
    Set CL = Sheet2
    
    '~~> Search range
    Dim ClientsF As Range
    Set ClientsF = CL.Range("A:A")
    
    Dim lRow As Long, i As Long
    Dim aCell As Range
    Dim cClientS As String
    Dim cClient As Worksheet
    
    With ML
        '~~> Find last row in column A of ML sheet
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the range
        For i = 2 To lRow
            Set aCell = ClientsF.Find(What:=.Range("B" & i).Value2, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
             
            If Not aCell Is Nothing Then
                cClientS = aCell.Value2
                
                On Error Resume Next
                Set cClient = Sheets(cClientS)
                On Error GoTo 0
                
                '~~> Check if we managed to get the sheet object
                If cClient Is Nothing Then
                    Debug.Print cClientS & " doesn't have a valid sheet name"
                Else
                    '~~> With the new sheet, find the last empty row to write
                    With cClient
                        lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        
                        '~~> Write your values
                        .Range("A" & lRow).Value = .Range("D" & i).Value2
                        .Range("B" & lRow).Value = Mid(.Range("E" & i).Value2, 1, 10)
                        
                        '
                        '~~> And so on...
                        '
                    End With
                    
                    '~~> Clear the object for the next loop
                    Set cClient = Nothing
                End If
            Else
                Debug.Print "Client not found: " & .Range("B" & i).Value2, vbOKOnly, "ERROR: Client not found"
            End If
        Next i
    End With
End Sub

Select sheet using a variable

2 Answers2