MagicMike
10-12-2010, 06:32 AM
Hello all.
I am trying to group rows on a worksheet by a fixed week number when a user enters or inputs the start of a week and the end of a week for a duration.
For example:
When did you leave for the trip?
(Answer would be a week number) : week 10 or just the number "10"
When did you return from the trip?
(Answer would be a week number) : week 15 or just the number "15"
I have 53 rows already setup with column C numbered 1-53 to reflect week numbers and have started week "1" on row 11 in the worksheet to simplify the math if I need to add 10 to every variable to refer to a row.
I just want to select the rows that I'm asking the user to establish and then group them.
Heres what I have so far but I'm having problems with the part where i need to refer to the rows using the integer values.
Sub Group_Weeks()
Dim strlocation As String
Dim strpurpose As String
Dim bstart As Byte
Dim bend As Byte
'Get user input
bstart = InputBox(Prompt:="Please enter the week number that the trip began.", _
Title:="Start Week", Default:="1")
bend = InputBox(Prompt:="Please enter the week number that the trip ended.", _
Title:="End Week", Default:="5")
strlocation = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Location", Default:="Germany")
strpurpose = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Purpose", Default:="Reason for trip")
'Add 10 to reflect row numbers
bstart = strstart + 10
bend = strend + 10
Rows("bstart; bend").Select
Selection.Rows.Group
End Sub
I'm sure this is a simple thing but I can't figure this out.
Thanks in advance :)
Mike
I am trying to group rows on a worksheet by a fixed week number when a user enters or inputs the start of a week and the end of a week for a duration.
For example:
When did you leave for the trip?
(Answer would be a week number) : week 10 or just the number "10"
When did you return from the trip?
(Answer would be a week number) : week 15 or just the number "15"
I have 53 rows already setup with column C numbered 1-53 to reflect week numbers and have started week "1" on row 11 in the worksheet to simplify the math if I need to add 10 to every variable to refer to a row.
I just want to select the rows that I'm asking the user to establish and then group them.
Heres what I have so far but I'm having problems with the part where i need to refer to the rows using the integer values.
Sub Group_Weeks()
Dim strlocation As String
Dim strpurpose As String
Dim bstart As Byte
Dim bend As Byte
'Get user input
bstart = InputBox(Prompt:="Please enter the week number that the trip began.", _
Title:="Start Week", Default:="1")
bend = InputBox(Prompt:="Please enter the week number that the trip ended.", _
Title:="End Week", Default:="5")
strlocation = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Location", Default:="Germany")
strpurpose = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Purpose", Default:="Reason for trip")
'Add 10 to reflect row numbers
bstart = strstart + 10
bend = strend + 10
Rows("bstart; bend").Select
Selection.Rows.Group
End Sub
I'm sure this is a simple thing but I can't figure this out.
Thanks in advance :)
Mike