PDA

View Full Version : Help with Macro code



Barryj
07-03-2008, 05:08 PM
I am trying to modify this code to get it to put data into some sheets that I have added, NCR ,NCR1, NCR2, NCR3.What needs to happen is that when stroke is selected if a player has NCR nect to their name then the player ends up in sheet NCR.If 1st Round Championships is selected and the player has NCR next their name then the player ends up in sheet NCR1 and the same principal for rounds 2 and 3.The rest of the code works fine except for this and I am getting an error on this line of the code.
Set wks = Worksheets(strSheet(i))

This is the full code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Dim r As Range
Dim x As Range
Dim strSheet(0 To 1) As String
Dim sn As Variant
Dim i As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("O2")) Is Nothing Then Exit Sub
sn = Array("Gross1", "A Grade1", "B Grade1", "A Grade2", "B Grade2", _
"A Grade3", "B Grade3", "A GradeNett1", "B GradeNett1", "A GradeNett2", _
"B GradeNett2", "A GradeNett3", "B GradeNett3", "NCR", "NCR1", "NCR2", "NCR3")
Application.ScreenUpdating = False ' ONLY clear data in Gross1
Sheets("Gross1").Cells.ClearContents
With Sheets("Single Stroke")
For Each r In .Range("a11", .Range("a65536").End(xlUp))
If r.Value = "" Then GoTo SkipIt1 ' values are always written to Gross1 sheet
With Sheets("Gross1")
Set x = .Range("a" & Cells.Rows.Count).End(xlUp).Offset(1) x.Value = r.Offset(, 1).Value
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 22).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
x.Offset(, 6).Value = r.Offset(, 25).Value
x.Offset(, 7).Value = r.Offset(, 26).Value
x.Offset(, 8).Value = r.Offset(, 27).Value
x.Offset(, 9).Value = r.Offset(, 28).Value
End With
' set the target worksheet based on the round and player grade
Select Case Sheets("Single Stroke").Range("O2").Value
Case "1st Round Championships"
strSheet(0) = r.Value & " Grade1" strSheet(1) = r.Value & " GradeNett1"
Case "2nd Round Championships"
strSheet(0) = r.Value & " Grade2" strSheet(1) = r.Value & " GradeNett2"
Case "3rd Round Championships"
strSheet(0) = r.Value & " Grade3" strSheet(1) = r.Value & " GradeNett3"
Case Else ' if round is "Stroke", don't need to write the data ' anywhere else GoTo SkipIt1 End Select
' write values
For i = 0 To UBound(strSheet) Set wks = Worksheets(strSheet(i))
With wks Set x = .Range("a" & Cells.Rows.Count).End(xlUp).Offset(1) x.Value = r.Offset(, 1).Value
If i = 0 Then x.Offset(, 1).Resize(, 2).Value = r.Offset(, 22).Resize(, 1).Value
Else
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 23).Resize(, 1).Value
End If
x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
x.Offset(, 6).Value = r.Offset(, 25).Value
x.Offset(, 7).Value = r.Offset(, 26).Value
x.Offset(, 8).Value = r.Offset(, 27).Value
x.Offset(, 9).Value = r.Offset(, 28).Value
End With
Next iSkipIt1:
Next r
End With
Application.ScreenUpdating = True
End Sub

Can anybody give me any thoughts on this?

Thanks for any assistance.

Barryj
07-03-2008, 05:30 PM
I am trying to alter this code so that when a player has a NCR net to their name that they will end up in the appropiate sheet.

If stroke is selected and the player has NCR next to their name then they will end up in sheet NCR.

If 1st Round Championships is selected and the player has NCR next to their name then they will end up in sheet NCR1.

The same principal for 2nd and 3rd round of Championships.

The rest of the code works fine it's just the NCR part that is bugging out.

This is the line that the code is stopping at the moment.

Set wks = Worksheets(strSheet(i))

This is the entire code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Dim r As Range
Dim x As Range
Dim strSheet(0 To 1) As String
Dim sn As Variant
Dim i As Integer


If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Range("O2")) Is Nothing Then Exit Sub

sn = Array("Gross1", "A Grade1", "B Grade1", "A Grade2", "B Grade2", _
"A Grade3", "B Grade3", "A GradeNett1", "B GradeNett1", "A GradeNett2", _
"B GradeNett2", "A GradeNett3", "B GradeNett3", "NCR", "NCR1", "NCR2", "NCR3")

Application.ScreenUpdating = False

' ONLY clear data in Gross1
Sheets("Gross1").Cells.ClearContents

With Sheets("Single Stroke")
For Each r In .Range("a11", .Range("a65536").End(xlUp))

If r.Value = "" Then GoTo SkipIt1

' values are always written to Gross1 sheet
With Sheets("Gross1")
Set x = .Range("a" & Cells.Rows.Count).End(xlUp).Offset(1)
x.Value = r.Offset(, 1).Value
x.Offset(, 1).Resize(, 2).Value = r.Offset(, 22).Resize(, 1).Value
x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
x.Offset(, 6).Value = r.Offset(, 25).Value
x.Offset(, 7).Value = r.Offset(, 26).Value
x.Offset(, 8).Value = r.Offset(, 27).Value
x.Offset(, 9).Value = r.Offset(, 28).Value
End With

' set the target worksheet based on the round and player grade
Select Case Sheets("Single Stroke").Range("O2").Value
Case "1st Round Championships"
strSheet(0) = r.Value & " Grade1"
strSheet(1) = r.Value & " GradeNett1"

Case "2nd Round Championships"
strSheet(0) = r.Value & " Grade2"
strSheet(1) = r.Value & " GradeNett2"

Case "3rd Round Championships"
strSheet(0) = r.Value & " Grade3"
strSheet(1) = r.Value & " GradeNett3"

Case Else
' if round is "Stroke", don't need to write the data
' anywhere else
GoTo SkipIt1
End Select

' write values
For i = 0 To UBound(strSheet)
Set wks = Worksheets(strSheet(i))

With wks
Set x = .Range("a" & Cells.Rows.Count).End(xlUp).Offset(1)
x.Value = r.Offset(, 1).Value

If i = 0 Then
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 22).Resize(, 1).Value
Else
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 23).Resize(, 1).Value
End If

x.Offset(, 2).Value = r.Offset(, 24).Value
x.Offset(, 3).Value = r.Offset(, 21).Value
x.Offset(, 4).Value = r.Offset(, 20).Value
x.Offset(, 5).Value = r.Offset(, 19).Value
x.Offset(, 6).Value = r.Offset(, 25).Value
x.Offset(, 7).Value = r.Offset(, 26).Value
x.Offset(, 8).Value = r.Offset(, 27).Value
x.Offset(, 9).Value = r.Offset(, 28).Value
End With
Next i
SkipIt1:
Next r
End With

Application.ScreenUpdating = True
End Sub


Can anybody provide any thoughts on this?
Thanks for any assistance.

Simon Lloyd
07-03-2008, 06:02 PM
Threads merged, same question with two slightly different codes.

mdmackillop
07-04-2008, 07:43 AM
Should the range sizes here not be the same size?
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 22).Resize(, 1).Value

Can you post a workbook with sample data?

Barryj
07-04-2008, 05:02 PM
I have included a workbook of what is happening I have changed the following lines in the code but still get the code stopping at,


Set wks = Worksheets(strSheet(i))


Changed Dim strSheet(0 To 1) As String toDim strSheet(0 To 2) As String

and changed
Case "1st Round Championships"
strSheet(0) = r.Value & " Grade1"
strSheet(1) = r.Value & " GradeNett1"

Case "2nd Round Championships"
strSheet(0) = r.Value & " Grade2"
strSheet(1) = r.Value & " GradeNett2"

Case "3rd Round Championships"
strSheet(0) = r.Value & " Grade3"
strSheet(1) = r.Value & " GradeNett3"


to
Case "1st Round Championships"
strSheet(0) = r.Value & " Grade1"
strSheet(1) = r.Value & " GradeNett1"
strSheet(2) = r.Value & " NCR1"
Case "2nd Round Championships"
strSheet(0) = r.Value & " Grade2"
strSheet(1) = r.Value & " GradeNett2"
strSheet(2) = r.Value & " NCR2"
Case "3rd Round Championships"
strSheet(0) = r.Value & " Grade3"
strSheet(1) = r.Value & " GradeNett3"
strSheet(2) = r.Value & " NCR3"

I also change
If i = 0 Then
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 22).Resize(, 1).Value
Else
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 23).Resize(, 1).Value


To
If i = 0 Then
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 22).Resize(, 1).Value
Else
x.Offset(, 1).Resize(, 2).Value = _
r.Offset(, 22).Resize(, 1).Value

As suggested by mdmackillop.

On the workbook sheet single stroke if the dropdown box changes it will fire the macro, and the firs player shoud end up in the relevant NCR sheet.

Thanks for your help on this.

Bob Phillips
07-05-2008, 01:22 AM
You need to change



Set wks = Worksheets(strSheet(i))


to



Set wks = Worksheets(strSheet(i + 1))


but then that fails as you are creating sheet names in the array that do not exist, such as NCR GradeNett2

Barryj
07-06-2008, 12:23 AM
What might I need to change to get this code working?

Bob Phillips
07-06-2008, 02:15 AM
I don't know because I am not sure what/how you are going about this. Why/how do you generate a non-existant name?

Barryj
07-06-2008, 02:45 AM
What supposed to happen is if in the single stroke sheet and the event is stroke then the information is written to the gross sheet only whether or not they are A or B Grade, if the player has NCR next to their name then they go to sheet NCR.

If the event is 1st Round of Championships then all information is written to the gross sheet as well, if a player has a A in column a then the information is written to sheet A Grade1 and A gradeNett1 if a player has a B next to their name the information is written to B Grade1 and B GradeNett1, if a player has NCR next to their name then that player will go to NCR and NCR1.

This is the same principal for rounds 2 and 3.

If this need a different approach that ok, I don't mind listening to different approaches.

I hope this has made it a little clearer.