Multiple VBA script

P

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
 
S

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
Click to expand...

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
VBA multiple list of values for variable 1
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
VBA Coding Help for Beginner 0
VBA for Dependents shortcut menu 0
change cell color depending on two cell values 1
Pasting a cell into bookmarked table from excel vba 1
Finding the right style field in VBA 0

Top