ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Amend Code Help Please (https://www.excelbanter.com/excel-programming/395992-amend-code-help-please.html)

Paul Black
Amend Code Help Please
 
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

Bob Phillips
Amend Code Help Please
 
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)



"Paul Black" wrote in message
oups.com...
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

Paul Black
Amend Code Help Please
 
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

On Aug 21, 11:26 am, "Bob Phillips" wrote:
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)

"Paul Black" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Bob Phillips
Amend Code Help Please
 
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)



"Paul Black" wrote in message
ups.com...
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

On Aug 21, 11:26 am, "Bob Phillips" wrote:
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)

"Paul Black" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Paul Black
Amend Code Help Please
 
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

On Aug 21, 1:09 pm, "Bob Phillips" wrote:
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)

"Paul Black" wrote in message

ups.com...



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

On Aug 21, 11:26 am, "Bob Phillips" wrote:
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)

"Paul Black" wrote in message

groups.com...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 03:45 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com