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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
That's true.

Where do you put the declaration? In a standard Module?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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