Multiple VBA script

[Pete Cherry]

Hello, I would like to run the same script many times in one sheet.

I am starting to write a football tipping comp record sheet. It has the home
and away teams, the person's pick, the winner then the ponts for the round.

If a person picks the winner, they get 2 points. If the teams draw, the
person gets 1 point. If the person does not pick the winner, they get 0
points and then if a person correctly picks a draw, they get four points.

I have set up a VBA formula to correctly enter in the points when comparing
the pick cell to the winner cell. However what I have found now is that I
would need to copy this formula 260 times and modify the exact cell range
for each cell. That is too much time wasted. I do nto know how to write a
VBA script that will either be dynamic or will automatically change the
ranges for me.

Can anyone help?
Here is my script for the first five games:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("F3") = "Draw" And Range("H3") = "Draw" Then
Range("J3").Value = 4
ElseIf Range("H3") = "Draw" Then
Range("J3").Value = 1
ElseIf Range("F3") = Range("H3") Then
Range("J3").Value = 2
Else: Range("J3").Value = 0

End If
If Range("F4") = "Draw" And Range("H4") = "Draw" Then
Range("J4").Value = 4
ElseIf Range("H4") = "Draw" Then
Range("J4").Value = 1
ElseIf Range("F4") = Range("H4") Then
Range("J4").Value = 2
Else: Range("J4").Value = 0

End If
If Range("F5") = "Draw" And Range("H5") = "Draw" Then
Range("J5").Value = 4
ElseIf Range("H5") = "Draw" Then
Range("J5").Value = 1
ElseIf Range("F5") = Range("H5") Then
Range("J5").Value = 2
Else: Range("J5").Value = 0

End If
If Range("F6") = "Draw" And Range("H6") = "Draw" Then
Range("J6").Value = 4
ElseIf Range("H6") = "Draw" Then
Range("J6").Value = 1
ElseIf Range("F6") = Range("H6") Then
Range("J6").Value = 2
Else: Range("J6").Value = 0

End If
If Range("F7") = "Draw" And Range("H7") = "Draw" Then
Range("J7").Value = 4
ElseIf Range("H7") = "Draw" Then
Range("J7").Value = 1
ElseIf Range("F7") = Range("H7") Then
Range("J7").Value = 2
Else: Range("J7").Value = 0

End If
End Sub


Thanks in advance,
Pete

 

[Don Guillett]

Multiposting is not necessary or desirable. Pls post in ONE group only.
Programming would have been appropriate.

You can use a for i loop but why a selection_change event.
for i=3 to 10
if cells(i,"f")="Draw" And cells(i,"H")="Draw" Then
cells(i,"j").Value = 4
'etc
ElseIf Range("H3") = "Draw" Then
Range("J3").Value = 1
ElseIf Range("F3") = Range("H3") Then
Range("J3").Value = 2
Else: Range("J3").Value = 0

next i

 

[Guest]

Pete, I'm not sure you really want this in the _SelectionChange() event since
it will run every time you click in a different cell, but for now we'll leave
it there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LC As Integer
Dim lastRow As Long

lastRow = Range("F" & Rows.Count).End(xlup).Row
For LC = 3 To lastRow
If Range("F" & LC) = "Draw" And Range("H" & LC) = "Draw" Then
Range("J" & LC).Value = 4
ElseIf Range("H" & LC) = "Draw" Then
Range("J" & LC).Value = 1
ElseIf Range("F" & LC) = Range("H" & LC) Then
Range("J" & LC).Value = 2
Else
Range("J" & LC).Value = 0
End If
Next ' LC loop end
End Sub

 

[Scoops]

Hello, I would like to run the same script many times in one sheet.

I am starting to write a football tipping comp record sheet. It has the home
and away teams, the person's pick, the winner then the ponts for the round.

If a person picks the winner, they get 2 points. If the teams draw, the
person gets 1 point. If the person does not pick the winner, they get 0
points and then if a person correctly picks a draw, they get four points.

I have set up a VBA formula to correctly enter in the points when comparing
the pick cell to the winner cell. However what I have found now is that I
would need to copy this formula 260 times and modify the exact cell range
for each cell. That is too much time wasted. I do nto know how to write a
VBA script that will either be dynamic or will automatically change the
ranges for me.

Can anyone help?
Here is my script for the first five games:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("F3") = "Draw" And Range("H3") = "Draw" Then
Range("J3").Value = 4
ElseIf Range("H3") = "Draw" Then
Range("J3").Value = 1
ElseIf Range("F3") = Range("H3") Then
Range("J3").Value = 2
Else: Range("J3").Value = 0

End If
If Range("F4") = "Draw" And Range("H4") = "Draw" Then
Range("J4").Value = 4
ElseIf Range("H4") = "Draw" Then
Range("J4").Value = 1
ElseIf Range("F4") = Range("H4") Then
Range("J4").Value = 2
Else: Range("J4").Value = 0

End If
If Range("F5") = "Draw" And Range("H5") = "Draw" Then
Range("J5").Value = 4
ElseIf Range("H5") = "Draw" Then
Range("J5").Value = 1
ElseIf Range("F5") = Range("H5") Then
Range("J5").Value = 2
Else: Range("J5").Value = 0

End If
If Range("F6") = "Draw" And Range("H6") = "Draw" Then
Range("J6").Value = 4
ElseIf Range("H6") = "Draw" Then
Range("J6").Value = 1
ElseIf Range("F6") = Range("H6") Then
Range("J6").Value = 2
Else: Range("J6").Value = 0

End If
If Range("F7") = "Draw" And Range("H7") = "Draw" Then
Range("J7").Value = 4
ElseIf Range("H7") = "Draw" Then
Range("J7").Value = 1
ElseIf Range("F7") = Range("H7") Then
Range("J7").Value = 2
Else: Range("J7").Value = 0

End If
End Sub

Thanks in advance,
Pete

Hi Pete

I know you asked for vba but just in case...

Would this formula do, in J3:

=IF(AND(F3=H3,H3="Draw"),4,IF(H3="Draw",1,IF(F3=H3,2,0)))

Copy the formula down your sheet (grab the handle in the bottom right
corner of the cell and pull down) and Excel will adjust the cell
references accordingly.

Regards

Steve

 

[Pete Cherry]

Hello Steve,
Sorry about my late reply. Thanks for the formula. I tried it out a couple
days ago and it does exactly what I need.
Thanks again,
Pete