|
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 |
Multiple VBA script
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 "Pete Cherry" wrote: 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 |
Multiple VBA script
On 4 Jun, 12:49, "Pete Cherry"
wrote: 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 |
Multiple VBA script
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 "Scoops" wrote in message ups.com... On 4 Jun, 12:49, "Pete Cherry" wrote: 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 |
| All times are GMT +1. The time now is 09:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com