Newbie Seeking Help with Ranges, Loops, and Select

[USNA91]

Greetings,

It has been a LONG time since I programmed in BASIC. I'm talking "Commodore 64 circa 1986" long time (!!!).

I am now learning VBA because I want to have Excel do things that formulas just won't do, and have some questions that I just can't seem to figure out.

WORKBOOK STRUCTURE:

My workbook has 32+ sheets:

• "Setup" contains up to 30 people's names, entered as first and last names in different cells.
• "Lookups and Calculations" performs calculations on the names given in "Setup", and determines what the Worksheet Name(s) will be. It concatenates full names as well as calculates the first initial and last name of each person. If the cells in "Setup" are blank, the calculated Worksheet Name for each worksheet defaults to "TM 01", "TM 02", and so forth up to "TM 30". ("TM" stands for "Team Member").
• The other 30 sheets are identical, and serve as the data-entry sheet for each Team Member.
• There are several other sheets that take data from these 30 sheets and do all sorts of things, but none of those are relevant to the problem at hand.

USER REQUIREMENTS:

When a list of names has been entered in the "Setup" worksheet, I need a routine that will do the following on command:

• If the calculated Worksheet Name is "TM 01" or any other number, it deletes any data entries in the sheet, sets the "Latest Update" field to "Pending", selects the home cell, and hides the worksheet.
• If the calculated Worksheet Name is that of a Team Member, it sets the Tab Name of that worksheet to the Team Member's Name and shows the worksheet.

CURRENT CODE:

The code I have written to do this its as follows:

Sub UpdateWorksheets()
Application.ScreenUpdating = False

Sheet11.Name = Worksheets("Lookups and Calculations").Range("K4").Value
If Sheet11.Name = Worksheets("Lookups and Calculations").Range("J4").Value Then
Sheet11.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet11.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet11.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet11.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet11.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet11.Range("C4") = "Pending"
Sheet11.Range("C7").Select
Sheet11.Visible = False
Else
Sheet11.Visible = True
End If

Sheet12.Name = Worksheets("Lookups and Calculations").Range("K5").Value
If Sheet12.Name = Worksheets("Lookups and Calculations").Range("J5").Value Then
Sheet12.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet12.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet12.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet12.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet12.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet12.Range("C4") = "Pending"
Sheet12.Range("C7").Select
Sheet12.Visible = False
Else
Sheet12.Visible = True
End If

Sheet13.Name = Worksheets("Lookups and Calculations").Range("K6").Value
If Sheet13.Name = Worksheets("Lookups and Calculations").Range("J6").Value Then
Sheet13.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29,C31:I32,C34:I35,C37:I38,C40:I41,C43:I44").ClearContents
Sheet13.Range("C46:I47,C49:I50,C52:I53,C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77,C79:I80,C82:I83").ClearContents
Sheet13.Range("C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101,C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119").ClearContents
Sheet13.Range("C121:I122,C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143,C145:I146,C148:I149,C151:I152").ClearContents
Sheet13.Range("C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
Sheet13.Range("C7").Select
Sheet13.Range("C4") = "Pending"
Sheet13.Visible = False
Else
Sheet13.Visible = True
End If

Application.ScreenUpdating = True
End Sub

Note: The code has been truncated to show only the first 3 of 30 worksheets. The other 27 are identical. Also, I am using the worksheet Code Name vs. Sheet Name because the Sheet Name is being changed.


WHERE HELP IS REQUESTED:

I am seeking help with the following issues:

1. With the exceptions below, the code above works, but obviously it is not very efficient. I have had significant trouble trying to code a simple loop that would go from 1-30 (If using Code Name numbers, I reckon it would be from 11 to 40) and just perform the same steps. If I use "Count" as the variable, I don't know how to get the equivalent of "If Sheet(Count)=...". The Range reference with all of them together throws an error. Yes, I compounded them correctly (commas, colons, etc.), but it refuses to work. Is there a length limit to compound ranges?
2. The command SheetXX.Range("C7").Select (where XX is the sheet number) throws an error. If I precede it with SheetXX.Select, then THAT statement throws an error. I can't figure out why.
3. I tried using a "With Sheet XX" grouping, but it threw the same errors regarding the Select functionality.

If I remove the problematic Select functions, the code above correctly names the worksheets, deletes exiting contents as required/desired, and hides/unhides sheets correctly.

So, there it is.... A big post for a first post, but I've prowled around on here long enough to know I can find the help I need.

Thanks in advance!

 

[bobsan42]

Ok... So if I'm reading this correctly...

Yes, you did read it correctly.

So just for chuckles, how could I use a loop to further simplify the UpdateWorksheets sub to avoid having the same row 30 times?

using objects directly by their "real names" is not adequate for looping, esp. since you have each sheet tied to a specific range in the main sheets
loop with counter:

dim i as long
For i = 1 to worksheets.count
    Worksheets(i).Name = ....
    ...
Next i

loop each:

dim sh as worksheet
for each sh in worksheets
    sh.Name = ....
    ...
Next sh

the practical problem with these loops is when you get the worksheet object - you need a way to know which K and J cells on Lookups And Calculations are defining it.
Because Worksheets(11) is not necessarily Sheet11.
And you have to check if the current sheet is Setup or Lookups And Calculations.
Let's take it a step at a time and not change everything at once...

 

[USNA91]

Fair enough. What I have right now is working perfectly!

Thanks again!

 

[USNA91]

I tried it this way:

Private Sub UpdateTMSheet(ByRef WrkSheet As Worksheet, ByVal DfltSheetName As String, ByVal CalcSheetName As String)

'Called by Subroutine "UpdateWorksheets", updates a specified Team Member Worksheet (WrkSheet).

Dim RowCounter As Long
Dim ColCounter As Long

With WrkSheet

.Activate
.Name = CalcSheetName 'Set Worksheet Name to the name calculated by the "Lookups and Calculations" Worksheet.

If CalcSheetName = DfltSheetName And .Visible = True Then 'IF... the Worksheet will no longer be in use but is currently visible, THEN... wipe all existing data, home the cursor to Cell C7, and hide the Worksheet.

For RowCounter = 7 To 161 Step 3
For ColCounter = 3 To 9
.Cells(RowCounter, ColCounter).ClearContents
.Cells(RowCounter + 1, ColCounter).ClearContents
Next ColCounter
Next RowCounter
.Range("J6:L161").ClearContents
.Range("C7").Select
.Visible = False

ElseIf CalcSheetName <> DfltSheetName And .Visible = False Then 'IF... the Worksheet will now be in use but is currently hidden, THEN... make the Worksheet visible.

.Visible = True

End If

End With

End Sub

The problem I am having is that this way takes FOREVER, as in "force-quit Excel and start over" forever (haven't figured out how to force-stop VBA on a Mac keyboard yet).

Yes, the version using all the ranges took up to two minutes to run, but the arrangement above never seems to stop. Any suggestions?

 

[bobsan42]

this way takes FOREVER, as in "force-quit Excel and start over" forever

I am not sure I understand what you mean here. Does Excel restart ? If yes -which line forces it to do so?

the version using all the ranges took up to two minutes to run, but the arrangement above never seems to stop

Which is that version? did you try the one I posted under #10.

• A way to speed up SIGNIFICANTLY all data operations on ranges is to turn off automatic calculations before starting, like so:

    Application.Calculation = xlCalculationManual '-4135

... then restore to automatic when you're done:

    Application.Calculation = xlCalculationAutomatic '-4105

2 things to remark in your last code:
- in the code itself I see no obvious endless loop or any immediate problems that will force excel to quit (or restart);
REMARK after re-reading the code: I am not sure, but maybe use the .Activate method only of the sheet is Visible; Normally this will only have no effect, but who knows.
- altering cells one at a time is quite slow - it takes almost the same time for excel to access a single cell as it takes for a range of cells:

here are some rounded results from a quick test with range.ClearContents:​

• 1 cell: avg 1 ms
• 1000 cells at once: 1.1 ms
• 1000 cells one at a time: 125 ms

Also send the other procedure, "UpdateWorksheets", to see if anything strikes as odd or if it has any potential for endless loops or errors.

 

[bobsan42]

compare these two:

Sub test2()
Dim tt As Variant
tt = VBA.Timer
    With ActiveSheet
        Dim i As Long, j As Long, x As Long
        For i = 7 To 161 Step 3
            For j = 3 To 9
                    .Cells(i, j).ClearContents
                    .Cells(i + 1, j).ClearContents
            
                x = x + 2
            Next j
        Next i
        .Range("J6:L161").ClearContents
        Debug.Print x & " cells cleared + " & .Range("J6:L161").Cells.Count
    End With
Debug.Print (VBA.Timer - tt) * 1000 & " ms"
                
            
End Sub

Sub test3()
Dim tt As Variant
tt = VBA.Timer
    With ActiveSheet
        Dim i As Long, j As Long, x As Long, rng As Range, cc As Range
            Set rng = .Range("J6:L161")
            Set cc = .Range("C7:I8")
            Do
                Set rng = Union(rng, cc)
                Set cc = cc.Offset(3, 0)
            Loop Until cc.Row > 160
            rng.ClearContents
        Debug.Print rng.Cells.Count & " cells cleared "
    End With
Debug.Print (VBA.Timer - tt) * 1000 & " ms"
                
            
End Sub

both clear the contents of 1196 cells; test2 takes 120-125 ms on average while test3 takes less than 2 which I think is significant, especially on larger datasets.
if you turn off automatic calculations:

Sub test2()
Application.Calculation = xlCalculationManual
Dim tt As Variant
tt = VBA.Timer
    With ActiveSheet
        Dim i As Long, j As Long, x As Long
        For i = 7 To 161 Step 3
            For j = 3 To 9
                    .Cells(i, j).ClearContents
                    .Cells(i + 1, j).ClearContents
            
                x = x + 2
            Next j
        Next i
        .Range("J6:L161").ClearContents
        Debug.Print x & " cells cleared + " & .Range("J6:L161").Cells.Count
    End With
Debug.Print (VBA.Timer - tt) * 1000 & " ms"
Application.Calculation = xlCalculationAutomatic
End Sub

test2 may drop to 90 ms on average, test3 results are inconclusive and vary a lot, but I am testing this on empty cells. If you have real data and potentially dependant formulas - the effect is noticeable.

 

[USNA91]

Bob, I'm going to circle back with you on your last post when I get this thing working again. It's getting exasperating for me...

With your and other's help, I have reached the following point:

CURRENT CODE:

Option Explicit

Sub UpdateWorksheets()

Dim RefSheet As Worksheet

Application.ScreenUpdating = False
RefSheet = Worksheets("Lookups and Calculations")

UpdateTMSheet Sheet11, RefSheet.Range("E4").Value, RefSheet.Range("F4").Value, RefSheet.Range("I4")
UpdateTMSheet Sheet12, RefSheet.Range("E5").Value, RefSheet.Range("F5").Value, RefSheet.Range("I5")
UpdateTMSheet Sheet13, RefSheet.Range("E6").Value, RefSheet.Range("F6").Value, RefSheet.Range("I6")

Sheets("Setup and Update Status").Activate
Application.ScreenUpdating = True

End Sub

Private Sub UpdateTMSheet(ByRef WrkSheet As Worksheet, _
ByVal DfltSheetName As String, _
ByVal CalcSheetName As String, _
ByVal LastUpdate As Range)

With WrkSheet

.Activate
.Name = CalcSheetName

If CalcSheetName = DfltSheetName And .Visible = True Then

.Visible = False
.Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29").ClearContents
.Range("C31:I32,C34:I35,C37:I38,C40:I41,C43:I44,C46:I47,C49:I50,C52:I53").ClearContents
.Range("C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77").ClearContents
.Range("C79:I80,C82:I83,C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101").ClearContents
.Range("C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119,C121:I122").ClearContents
.Range("C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143").ClearContents
.Range("C145:I146,C148:I149,C151:I152,C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
.Range("C7").Select
ActiveSheet.ScrollRow = 1
ActiveSheet.ScrollColumn = 1
LastUpdate.Value = "Pending Data Entry"

ElseIf CalcSheetName <> DfltSheetName And .Visible = False Then

.Visible = True

End If

End With

End Sub

PROBLEM:

For some reason, I am getting Run-Time Error 91: "Object Variable or With block variable not set" at the underlined statement above.

Everything was working reasonably well and suddenly this popped up, and I can't for the life of me understand what the issue is.

I need to get this working reliably before I can even consider improving efficiency or speed.

 

[bobsan42]

Set RefSheet = Worksheets("Lookups and Calculations")

Set obj1 = obj2 is used for objects/classes;

 

[USNA91]

Oh good grief...

THANK YOU!

I'm getting over this learning curve if it kills me...

 

[bobsan42]

I'm getting over this learning curve if it kills me...

It never ends, I think
Cheers.

 

[USNA91]

I just want it to seem more like a steady climb uphill than a scaling of K2.

So to clarify, because I'm dealing with other errors that MAY be caused by the same thing...

If I am dealing with a VARIABLE such as Counter, I can say:

Counter = Counter +1

However, statements such as:

Worksheets("Lookups and Calculations").Cells(Row, 9) = Now

or

LastUpdate.Value = "Pending Data Entry"

should be preceded by "Set" because they are dealing with OBJECTS.

Correct?