Newbie Seeking Help with Ranges, Loops, and Select

U

USNA91

New Member
Joined
Mar 21, 2024
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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! :)
 
Sort by date Sort by votes
not quite. some info in a nutshell below, TTBOMK.
SET is used when you assign an object to a variable or you want to instantiate a class (create an "actual" object from a class "definition"):
VBA Code: 
dim sh as Variant, rng as Range, obj as Object
set sh = Thisworkbook.Worksheets("[I]Lookups and Calculations[/I]")
set rng = sh.cells(5,4)
set obj = New MyClass
'to be continued below ...
You can assign an object to any variable, if their types match e.g. you cannot assign a Worksheet object to the rng variable above, but you can use the sh or obj to do so.
The objects have properties and methods exposed. The properties have values.
When you are dealing with values you only use = to assign to a variable or change a value of a property or variable.
VBA Code: 
'... continues from above
dim i as long, t as String
i = 5
i = i +1
t ="5"
t = t & sh.Name
rng.value = i
rng.value = t
sh = i
sh = t
sh = rng.value
t = sh
'...
Again types should match or a on-the-fly conversion should be possible e.g.
VBA Code: 
'...
i = 10
t=i ' now t is "10"
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
My brain hurts, and it's only 10:00 AM... 🤪

Ok... I'll get back to that one.

Next problem... This one may be simpler...

CURRENT CODE

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
ActiveSheet.Range("C7").Select
ActiveSheet.ScrollRow = 1
ActiveSheet.ScrollColumn = 1
LastUpdate.Value = "Pending Data Entry"

INTENDED FUNCTION

If the name of the worksheet is the default name, then it:
  • Wipes all existing data entries (via the ClearContents commands)
  • Selects Cell C7
  • Scrolls to the top of the worksheet
  • Scrolls to the left of the worksheet
  • Resets a cell in another worksheet to read "Pending Data Entry"
PROBLEMS
  1. The command to select cell C7 either doesn't work at all or it throws a "Method Of Range Class Failed" error. All I want is that the next time the worksheet is selected, the cursor be on Cell C7.
  2. The ActiveSheet.ScrollRow = 1 line throws a "Object doesn't support this property or method" error.
I suspect I am either using the wrong method to get the desired result, or else I have a syntax error(s) plaguing the attempt. Everything else seems to work...

ETA: Hey! Editing came on! Hooray!
 
Upvote 0
I read on another thread that using "select" can be problematic. Maybe that's the issue here...
 
Upvote 0
Well, SELECT is better avoided if not intended for something. It slows things down, is not really necessary for most of the methods, must be able to select with your cursor the thing you want to select with code.
But it's not a "stay-away-no-matter-what" type of method.
try this code:
VBA Code: 
'I assume at this point the code is in a With some_sheet block
    If CalcSheetName = DfltSheetName And .Visible = True Then
        .Activate ' you need this if you have not done this earlier in your code - otherwise select will fail, _
                    and scroll will scroll the active sheet, which may not be the one you want
      
        .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
      
        ActiveWindow.ScrollRow = 1 ' This is a member of the the Window object, not of the Sheet object
        ActiveWindow.ScrollColumn = 1 'Same as above
        '.Range("A1").Select ' this can replace the previous 2 lines, but it must be selected before C7
      
        .Range("C7").Select ' you are already in a WITH block so you don't need Activesheet
        LastUpdate.Value = "Pending Data Entry"
      
        .Visible = False 'move this to the end otherwise you cannot perform SELECT on something you don't see
    End If
read the comments in green
 
Upvote 0
Ah! Excellent! Let me try that and see.

Makes perfect sense that scroll belongs to an active WINDOW not SHEET.

I like the two Select commands. Saves a row of code...

Hope you don't get too tired of my saying "Thanks!". ;)
 
Upvote 0
GRRRR.... :mad:

Now I'm getting a "1004: Select Method of Range Class Failed" error at the ".Range("A1").Select" statement. I've included the whole code set this time...

VBA Code: 
Option Explicit

Sub UpdateWorksheets()

Dim RefSheet As Worksheet
  
    Application.ScreenUpdating = False
    Set 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("A1").Select
            .Range("C7").Select
            LastUpdate.Value = "Pending Data Entry"
                  
        Else
          
            .Visible = True
      
        End If
  
    End With

End Sub

ETA: Ah! So THAT'S how you present code here! Cool!
 
Upvote 0
you missed this from my previous post:
VBA Code: 
        .Visible = False 'move this to the end otherwise you cannot perform SELECT on something you don't see
hide the sheet just before the ELSE, when you're done selecting:
VBA Code: 
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
          
            .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("A1").Select
            .Range("C7").Select
            .Visible = False
            LastUpdate.Value = "Pending Data Entry"
                  
        Else
          
            .Visible = True
      
        End If
  
    End With

End Sub
 
Upvote 0
Oh, goodness! I did miss that!

Thanks!


ETA: BINGO!! Working perfectly! Thanks again! 😁
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
644
Alex Blakenburg
A
M
  • Question Question
Need help to count duplicates as one with multiple criteria
Replies
0
Views
404
Mr Rice
M
J
  • Solved
Finding max value in a range based on a certain row
Replies
6
Views
2K
jungle125
J
E
  • Solved
Sum Cells and IF Statement to add times
Replies
7
Views
665
Fluff
Fluff
P
  • Question Question
Procedure too long - help!!
Replies
3
Views
724
philscrase
P

Forum statistics

Threads
1,224,812
Messages
6,181,094
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top