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
Uniquecount = Uniquecount + 1
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
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.
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: