CODEWITHSUNDEEP
excelvbanested
Greg
Greg

Reputation: 43

Nested For Loop Stops Midstream

In the code below LastCellR = 49 and LastSheet is 61 (i.e. there are 61 sheets in the workbook and 49 client names in column 1 of the active sheet) The code runs fine till i gets to 46 and CS gets to 61. Then in Debug, the code stops and highlights "If Worksheets(CS).Name = ClientName Then".

The goal here is to go down a list of client names and determine if they are existing clients or new ones. So I simply get each client's name and search through the sheet names in the workbook.
Any ideas why it stops dead in it's tracks?

Thanks for any insight you can provide.

For i = 2 To LastCellR
    ClientName = Cells(i, 1).Value
    If Trim(ClientName) = "" Then Exit Sub

' Check for existing client
    For CS = 1 To LastSheet
        If Worksheets(CS).Name = ClientName Then
           NewClient = False
           Exit For
        End If
    Next CS
Next i

Upvotes: 0

Views: 49

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Not the answer to your issues, may be another option to check for the sheet

Public Function WorksheetExists(strName As String) As Boolean
On Error GoTo eHandle

Dim ws As Excel.Worksheet

WorksheetExists = True

Set ws = ThisWorkbook.Worksheets(strName)

Set ws = Nothing

Exit Function

eHandle:

    WorksheetExists = False
    Resume Next
    
End Function

Then newclient = not worksheetexists(clientname)

Upvotes: 1

Related Questions