Scope of variables question

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
If I've defined a variable outside of all procedures.....which I though made it a global variable

Then when I've set a value to that variable in one procedure, shouldn't another procedure remember what the other one set it to?


Thanks
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
That's true.

Where do you put the declaration? In a standard Module?
 

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
Thanks Wigi

Yes in a standard module

Code:
ActiveWorkbook.Sheets("Data").Activate    
    For GroupNo = 2 To NumGroups
 
        Uniquecount = 0
        Membernumber = 1
        For Membernumber = 1 To NumMembers
 
            If Cells(Membernumber + 4, 3 * GroupNo + 5) > 0 Then
                With ActiveWorkbook.Sheets("Gr" & GroupIDsArray(GroupNo, 1)).Cells(1, 1)
                    .Offset(Uniquecount + 2, 87) = ActiveWorkbook.Sheets("Data").Cells(Membernumber + 4, 3 * GroupNo + 5).Value
                    .Offset(Uniquecount + 2, 2) = ActiveWorkbook.Sheets("Data").Cells(Membernumber + 4, 1).Value
                    .Offset(Uniquecount + 2, 78) = ActiveWorkbook.Sheets("Data").Cells(Membernumber + 4, 3 * GroupNo + 7).Value
                End With
                Uniquecount = Uniquecount + 1   
            End If
        Next Membernumber
    Next Group No

In this spreadsheet, members have values under each Group ID. For some Group ID's some members have no value. I've created a worksheet for each Group and I now want VBA to test whether each member has a value under that group's column and if it has then paste it in that group's worksheet.

If I click in the left hand side of the screen and put a red dot by the first End With and click Run, then hover the mouse over Membernumber, it says Membernumber = 2 as if that's fixed in it's brain?? Even if before the
Code:
For Membernumber = 1 To NumMembers
loop I put
Code:
Membernumber = 1
, it still shows Membernumber = 2
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello

You use Membernumber = 1, but after that line you're starting a loop with the same variable. Meaning that you execute the code for Membernumber = 1, then Membernumber = 2, Membernumber = 3, until Membernumber = NumMembers. So the variable Membernumber increments with 1 each time the loop is executed.
 

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
That's what I'm wanting but it seems to be skipping Member = 1 even though member 1 does have a value in Group 1
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
1) Use Option Explicit at the top of your modules to ensure they are all properly declared.
2) I'm suspicious about using a global variable as a loop counter in a For loop. For that matter, your post does not clarify the declaration of your variables - the declarations are nowhere in the code.
3) The break point you have chosen will not break on that line if code does not enter the If clause. For instance, on the first loop, if Cells(Membernumber + 4, 3 * GroupNo + 5) > 0 evaluates to False, then execution passes the If block, and membernumber increments to 2, without breaking.
If you are working on multiple worksheets, I would not leave Cells unqualified:
Code:
Cells(Membernumber + 4, 3 * GroupNo + 5)
Would be better amended to:
Code:
ActiveWorkbook.Sheets("Data").Cells(Membernumber + 4, 3 * GroupNo + 5)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

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
Top