Dim help needed

[MagicMike]

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.

[VBA]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[/VBA]

I'm sure this is a simple thing but I can't figure this out.

Thanks in advance

Mike

[YasserKhalil]

Try this

[VBA]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 = bstart + 10
bend = bend + 10
Rows(bstart & ":" & bend).Select
Selection.Rows.Group
End Sub
[/VBA]

[MagicMike]

ok great, that worked. Although I did have some problem with it saying that the worksheet was protected when it got to the grouping function when the sheet was not protected.

I made all cells "unlocked" in the formatting and it worked.

Now to get the strings in the A and B columns

I'll be back with an update...

Thanks Yasserkhalil

-Mike

[MagicMike]

ok, when using this code and selecting cancel from any of the input boxes I get an error.

I think I need some error handling in here somewhere. Can someone suggest something for me

Thanks

[Bob Phillips]

[vba]

Sub Group_Weeks()
Dim strlocation As String
Dim strpurpose As String
Dim bstart As Byte
Dim bend As Byte

'Get user input
On Error Resume Next
bstart = InputBox(Prompt:="Please enter the week number that the trip began.", _
Title:="Start Week", Default:="1")
On Error GoTo 0
If bstart = 0 Then Exit Sub

On Error Resume Next
bend = InputBox(Prompt:="Please enter the week number that the trip ended.", _
Title:="End Week", Default:="5")
On Error GoTo 0
If bend = 0 Then Exit Sub

On Error Resume Next
strlocation = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Location", Default:="Germany")
On Error GoTo 0
If strlocation = "" Then Exit Sub

On Error Resume Next
strpurpose = InputBox(Prompt:="Please enter the final location of the trip.", _
Title:="Purpose", Default:="Reason for trip")
On Error GoTo 0
If strpurpose = "" Then Exit Sub

'Add 10 to reflect row numbers
bstart = bstart + 10
bend = bend + 10
Rows(bstart & ":" & bend).Select
Selection.Rows.Group
End Sub
[/vba]

[MagicMike]

[VBA]Sub Group_Weeks()

Dim strlocation As String
Dim strpurpose As String
Dim strstart As String
Dim strend As String



'Get user input
strstart = InputBox(Prompt:="Please enter the week number that the trip began.", _
Title:="Start Week", Default:="1")
If strstart = "" Then Exit Sub

strend = 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
strstart = strstart + 10
strend = strend + 10

Range("A" & strstart).Value = strlocation
Range("B" & strstart).Value = strpurpose

strstart = strstart + 1
Rows(strstart & ":" & strend).Select
Selection.Rows.Group


End Sub[/VBA]

This works

[MagicMike]

lol I was posting at the same time. I had troubles using the Bytes so I changed them to strings. Going to try your code.

[MagicMike]

All is good! thank you

[MagicMike]

All is good! thank you

[MagicMike]

All is good! thank you