Amend Code Help Please

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

Hi everyone,

I have the following code ...

Option Explicit
Option Base 1

Public Sub Test()
Dim RowData As Range
Dim C As Long
Dim CombData As String
Dim CombNum As Integer

CombNum = 1 <---------
For Each RowData In ActiveSheet.UsedRange.Rows
With RowData
If C > 0 Then CombNum = CombNum + 1
CombData = ""
For C = 1 To 5
CombData = CombData & .Cells(1, C).Value & ","
Next C
CombData = CombData & .Cells(1, 6).Value
Debug.Print "Set " & CombNum & "," & CombData
End With
Next RowData

End Sub

.... which does produce the right information in the right format.
I would like to amend the code to pick up the groups on a sheet named
"Groups" and in Cells "B3:G?". The groups could be 1 to whatever.
The other thing is that I have had to set the CombNum to 1 when I
think it should start at zero.
If there is a better way to code this I would appreciate any pointers.

Thanks in dvance.
All the Best.
Paul
 
Public Sub Test()
Dim RowData As Range
Dim C As Long
Dim CombData As String
Dim CombNum As Integer

For Each RowData In ActiveSheet.UsedRange.Rows
With RowData
If RowData.Row > 2 Then
CombNum = CombNum + 1
CombData = ""
For C = 2 To 6
CombData = CombData & .Cells(1, C).Value & ","
Next C
CombData = CombData & .Cells(1, 7).Value
Debug.Print "Set " & CombNum & "," & CombData
End If
End With
Next RowData
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob,

I amended your code slightly to get the number of the group at the
beginning and ended up with this :-

Public Sub Test()
Dim RowData As Range
Dim C As Long
Dim CombData As String
Dim CombNum As Integer

For Each RowData In Worksheets("Data").UsedRange.Rows
With RowData
If RowData.Row > 2 Then ' Combinations start in cell "B3"
CombNum = CombNum + 1
CombData = ""
For C = 1 To 5
CombData = CombData & .Cells(1, C).Value & ","
Next C
CombData = CombData & .Cells(1, 6).Value
Debug.Print "Set " & CombNum & "," & CombData
End If
End With
Next RowData

End Sub

Thanks Again.
All the Best.
Paul
 
I thought you wanted B:G not A:F?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,

Yes, I want "B3:G?" whatever, but I wanted it to output the
combinations in the format ...

Set 1,1,2,3,4,5,6
Set 2,1,2,3,7,8,9
Set 3,3,5,6,7,8,9

.... where the first number after the Set is the combination number and
the other six numbers are the 6 number combination.

Thanks in Advance.
All the Best.
Paul
 
Back
Top