Is it possible to set all variables to zero at once?

[raeannb]

Hi! I thought for sure someone would have asked about this already, but I didn't have any luck searching the message board. I'd like to know if it's possible to simultaneously set all of my variables (or at least, my integer variables) to zero with a single line of code.

Right now I'm counting a number of things (each described by an integer variable) with a loop that exists within a bigger loop (bigger loop cycles through different worksheets). The little loop counts get reported to the active worksheet. I'd like all of my variables to re-set to zero before starting my little loop again on the next worksheet. The variables are named after what they describe, so their names are unique. I'd like to keep it this way, though I know if I just turned them into an array I could easily clear the values. Right now I have a looooong list of variables getting set to zero (var1=0, var2=0...var3847583=0...YUCK).

Is there a way to do what I'd like here? Thank you!!

 

[jproffer]

I think all variables return to zero on Exit, so one way might be to call a second sub then call back to the first.

Any new value will over-write the old value anyway though so I don't know if I'd bother, myself......


3 million???, seriously?

 

[raeannb]

Haha that variable number might have been an exaggeration...

I'll have to try the sub-calling thing. Since these are counting with a loop, they just grow and grow (and growwww) unless I reset them. Unless there's a better way to count than what I'm using? I have it set up like this:

For a = 1 to Sheets.Count
   var1 = 0
   var2=0 '...and so on
   For b = 1 to Sheets(a).Range("A1").End(xlDown).Row
      If Sheets(a).Range("A"&b).Value = "Yes" Then
         var1 = var1 + 1
         var2 = var2 + 1  'etc
      End if
   Next b
   Sheets(1).Range("A5").Value = var1
   Sheets(1).Range("A6").Value = var2
Next a

Thoughts? If I don't reset them, the output becomes the count over all sheets, but I want them split up, ideally.

 

[jproffer]

I see...makes sense.

You could put the loop into a routine by itself. What's above it into one by itself.

Run the "code above" sub, call the loop sub, then call a "resetting" sub after the loop which only purpose is to call the loop sub again.

?? I may be way off too, I'm not sure how exactly you have it set up.

 

[jkpieterse]

Having so many variables would trigger me to look for a different approach. Can you post a bit more about your goal perhaps?

 

[pgc01]

Hi

If you are just counting you may avoid the loop using a worksheet function.

For ex, for var1, you have the loop:

   For b = 1 To Sheets(a).Range("A1").End(xlDown).Row
      If Sheets(a).Range("A" & b).Value = "Yes" Then
         var1 = var1 + 1
      End If
   Next b
   Sheets(1).Range("A5").Value = var1

you may do it without the loop like this (not tested):

    Sheets(1).Range("A5").Value = Application.WorksheetFunction.CountIf( _
        Sheets(a).Range("A1:A" & Sheets(a).Range("A1").End(xlDown).Row), "Yes")

In this case you don't even use the variable and so the problem of resetting it just disappears.

 

[raeannb]

Sure! Thanks for your help, both of you!

I started writing a reply about my goals for the code that quickly became verryyy long (I'm beginning to notice a theme here...perhaps I'm just a longwinded type of person?). My best abridged version:

I'm making a summary of 6 "tracker" files that serve as inventories for 6 different projects. Customers are listed in the rows of each tracker, and the services they've purchased are listed in columns. Some services have multiple components. Many services are the same across trackers, but some projects have unique services, as well. Customers are identified by a number of the form "XXXXX-XXX" where the first five digits represent a site ID number (for example, the 3rd customer from site 00001 would have a customer ID number 00001-003).

The summary has sites listed in rows, and services listed in columns. My loop is going through each project to count how many customers from each site have completed each type of service (or component of that service). The variables I'm using in this part of the code (I called them var1 and var2 above) are named for the type of service (so if the service was "laundry" and had components "lights" and "darks", the variables might be "laundrylights" and "laundrydarks"). These variables are then reported in the summary in appropriate site row and service column.

Does this make sense? I'm totally winging it, so if you vba-whizzes have any suggestions I would definitely love to hear them.

Thanks again!!

 

[raeannb]

Hi

If you are just counting you may avoid the loop using a worksheet function.

For ex, for var1, you have the loop:

   For b = 1 To Sheets(a).Range("A1").End(xlDown).Row
      If Sheets(a).Range("A" & b).Value = "Yes" Then
         var1 = var1 + 1
      End If
   Next b
   Sheets(1).Range("A5").Value = var1

you may do it without the loop like this (not tested):

    Sheets(1).Range("A5").Value = Application.WorksheetFunction.CountIf( _
        Sheets(a).Range("A1:A" & Sheets(a).Range("A1").End(xlDown).Row), "Yes")

In this case you don't even use the variable and so the problem of resetting it just disappears.

Hi pgc01! I hadn't thought of doing it that way - I might have to play around with it a bit, because the "if" statement is a bit more complicated than the example I gave. It's a good idea though. I'll give a whack and report back.

Thanks!

 

[jkpieterse]

This description does give me the impression you should be able to pull this off with no VBA, but with a pivot table, perhaps with some helper columns next to the data table with formulas to extract some properties.

 

[raeannb]

This description does give me the impression you should be able to pull this off with no VBA, but with a pivot table, perhaps with some helper columns next to the data table with formulas to extract some properties.

Hmm..is it possible to do that when there's information in different workbooks? Also, I'm handing this off to an even less-experienced Excel user to create the same (but updated) summary next quarter. I was hoping a macro might make it easier...bleh. Oh well. I'll keep playing and see what happens.

Thanks everyone!