loop through each cell in range not looping through whole range

[josvajensen]

My code is supposed to create a new sheet for each entry that is marked in a list of task codes.

This is how it is supposed to function.

In my workbook I have a list of task codes. For each task code I've left a space for the user to indicate which task codes the user wants to create a sheet for. The code is supposed to loop through a range and check if each cell has been marked. If the cell is marked, then the code continues to create a sheet for that task code as long as that sheet doesn't already exist.

Currently my code will only create new sheets for a portion of the tasks that are marked. I have attempted three different ways of selecting the range and looping through each cell in that range and no version includes all marked task codes.


I've checked using msgbox to make sure that the entire range I want is indeed being selected. Somehow, though, in a list of 16 task codes I want to include, only 4 were created.

Please help!

Dim rng1 As Range
Dim cell1 As Range
Dim i As Long
Dim shName As String
Dim exists As Boolean

'determine range of marked task codes from column C
Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)

For Each cell1 In rng1
If cell1 <> "" Then
'sheet names can only be 31 characters, so i'm only using the first 31 characters of a task code _
to name the new sheet
shName = left(cell1.Offset(0, 3).Value, 31) 'list of task code names is 3 columns over

For i = 1 To Sheets.Count

If Sheets(i).Name = shName Then
exists = True
End If
Next i

If Not exists Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = shName
Call FormatNewSheet 'adds some things to the new sheet
End If
End If
Next cell1

 

[Trebor76]

Hi josvajensen,

Welcome to MrExcel!!

I think the issue is the bolded section of this line of code...

Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)

...because it will be based on the active sheet from which its last row maybe lower than the last row in Task Codes. See if replacing the above with the following does the trick (note I use C instead of 3 as it's totally clear that the range is being set from Col. C in my humble opinion):

Set rng1 = Worksheets("Task Codes").Range("C1:C" & Worksheets("Task Codes").Cells(Rows.Count, 3).End(xlUp).Row)

Regards,

Robert

 

[6StringJazzer]

I agree with @Trebor76 , and this is a common error. The code can be streamlined a bit like so:

With Worksheets("Task Codes")
   Set rng1 = .Range("C1:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
End With

 

[josvajensen]

Thank you both @Trebor76 and @6StringJazzer for such prompt replies!

Forgive me, but I must disagree with the thought that that line is not capturing the correct range. For three reasons:
1. The code is executed from the "Task Codes" sheet by the click of a button, making it the active sheet when the VBA code is triggered, meaning that the line
Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
is sourced only from one sheet
2. the worksheet is specified before the range, meaning that the range container would be confined to that one sheet
3. Probably the only important reason. I used a msgbox over and over with different entries selected and I always had the correct range selected.

Being a self-taught excel/VBA user, I am not entirely sure what your suggestion streamlines, @6StringJazzer . Is using "With" somehow a better way to do what I am already accomplishing on one line of code?

If there are no other objections with my code I am forced to think there is something wrong with my workbook data instead of the code. Thank you both for taking the time to respond to my request for help!

 

[Trebor76]

I emphasized the word "think" in my reply as without having your workbook at hand it was an estimated guess. On face value I cannot see any glaring issues with the code.

A couple of things to try:

• Ensure there are no hidden sheets already named with the first 31 characters of the text in Col. F
• Step through each line of your code by continually pressing F8 from within the macro to see what each line is doing

 

[Trebor76]

the worksheet is specified before the range, meaning that the range container would be confined to that one sheet

Incorrect. The last row being set by this Cells(Rows.Count, 3).End(xlUp).Row which is based on the active sheet. The fact that the code is being run via a button on the relevant sheet means it works. I would still use either my or Jeff's solution because if the macro needs to be executed from another sheet in the future it should still work.

 

[Alex Blakenburg]

@josvajensen
My guess is that you have a sheet that already exists at which point the macro stops working.
It sets exists = True when a sheet exists so everything after that has exists = True
I have added 2 lines to your code
exists = False '<---- Add this line - must have
Exit For '<---- Add this line - Optional

Sub test()

    Dim rng1 As Range
    Dim cell1 As Range
    Dim i As Long
    Dim shName As String
    Dim exists As Boolean
    
    'determine range of marked task codes from column C
    Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    
    For Each cell1 In rng1
        exists = False                  '<---- Add this line - must have
        If cell1 <> "" Then
            'sheet names can only be 31 characters, so i'm only using the first 31 characters of a task code _
            to name the new sheet
            shName = Left(cell1.Offset(0, 3).Value, 31) 'list of task code names is 3 columns over
            
            For i = 1 To Sheets.Count
                If Sheets(i).Name = shName Then
                    exists = True
                    Exit For              '<---- Add this line - Optional
                End If
            Next i
                
            If Not exists Then
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = shName
                Call FormatNewSheet 'adds some things to the new sheet
            End If
                
        End If
    Next cell1
End Sub

 

[6StringJazzer]

Forgive me, but I must disagree with the thought that that line is not capturing the correct range. For three reasons:
1. The code is executed from the "Task Codes" sheet by the click of a button, making it the active sheet when the VBA code is triggered, meaning that the line
Set rng1 = Worksheets("Task Codes").Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
is sourced only from one sheet
2. the worksheet is specified before the range, meaning that the range container would be confined to that one sheet

The property Cells does not care what worksheet was specified to qualify Range. If you do not qualify Cells, then it defaults to ActiveSheet. In your situation, just by luck Task Codes is also the ActiveSheet, so in fact this line of code does work. However, this is still a coding error as it is not a good practice to specify a sheet in one place, and rely on what is ActiveSheet in another place. In addition, we had no way of knowing from your original post that the code is executed from the "Task Codes" sheet.

Being a self-taught excel/VBA user, I am not entirely sure what your suggestion streamlines, @6StringJazzer . Is using "With" somehow a better way to do what I am already accomplishing on one line of code?

It is a matter of style. I prefer it but you may prefer a single line that repeats Worksheets("Task Codes") everywhere. The With has the following advantages.

1. Specifying the sheet name in one place avoids the possibility of an error when repeating it multiple times. That is, it is guaranteed that all references will be the same sheet.
2. It makes the line of code shorter and easier to read
3. Using With allows VBA to resolve a pointer to the sheet once, instead of every time it is referenced. This increases the efficiency of the code, although to be honest that is rarely an issue.

 

[6StringJazzer]

My guess is that you have a sheet that already exists at which point the macro stops working.
It sets exists = True when a sheet exists so everything after that has exists = True

Nice diagnosis. I completely missed this because I was focusing on the Range.

 

[josvajensen]

Thank you all for your promptness, eagerness, and willingness to help me.

I have made revisions that have gotten me the results I was looking for. The primary issue was something pointed out by @Alex Blakenburg. Some of the task codes in the list shared the first 31 characters, which resulted in the code skipping over those items (different sizes for the same kind of item). I have altered the task codes to work around this, and added Alex's suggested lines. The Cells(Rows.Count, 3).End(xlUp).Row sheet not being specified is not necessarily a problem with this code, but I have ammended it anyway and hope to take the lesson to heart from @Trebor76 that the cell container is not restricted by the sheet used on the range container. This I can certainly see as an oversight.

Thanks again!