LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
external usenet poster
 
Posts: 4
Default Multiple VBA script

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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple VBA script Pete Cherry New Users to Excel 4 June 24th 07 02:16 AM
help with the VB script Igneshwara reddy[_2_] Excel Worksheet Functions 4 March 6th 07 08:54 PM
VB Script Krista Excel Worksheet Functions 4 May 20th 06 03:12 PM
VB script help - please!! Anthony Excel Discussion (Misc queries) 1 July 13th 05 01:19 AM
VBA script help..Please !!!! Anthony Excel Discussion (Misc queries) 6 June 6th 05 01:40 PM


All times are GMT +1. The time now is 11:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"