First post for a long while, Im trying to create a sheet to count the odds movement of each runner, i have made it work but wondered if it could be done better as my attempt is very long winded, can anyone point me in the right direction?
Any help would be appreciated.
example :
If [BA9].Value <> [BN9].Value Then ' if max odds <> last saved max odds then
[BB9].Value = [BB9].Value + 1 ' count plus 1
[BN9].Value = [BA9].Value ' last saved odds overwriten with new max odds
[BC9].Value = 0 ' oppposite value cleared
End If
If [BA11].Value <> [BN11].Value Then
[BB11].Value = [BB11].Value + 1
[BN11].Value = [BA11].Value
[BC11].Value = 0
End If
If [BA13].Value <> [BN13].Value Then
[BB13].Value = [BB13].Value + 1
[BN13].Value = [BA13].Value
[BC13].Value = 0
End If
Untidy vba
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
All you need to do is replace the numbers with a variable and then loop thru them. I don't actually use BA so you might have to alter some of the cells but the notion of looping thru data should get you started.
So to start we'd look for the last cell with data so we can set our outer limit of the loop, we already know our lower limit is 9 (i.e. BA9) . As BA seems to send the runners to column B we find the last populated cell by using the following
That value "FinalRow" will hold the number of the row of the last populated cell i.e last cell with data is B19 that would be 19.
So now we have that last cell we can loop thru BA9 to BA + FinalRow in steps of 2
So to start we'd look for the last cell with data so we can set our outer limit of the loop, we already know our lower limit is 9 (i.e. BA9) . As BA seems to send the runners to column B we find the last populated cell by using the following
Code: Select all
Dim FinalRow As Long 'declare a variable FinalRow to hold the number of the last cell
FinalRow = Cells(Rows.Count, "B").End(xlUp).Row
So now we have that last cell we can loop thru BA9 to BA + FinalRow in steps of 2
Code: Select all
Dim i As Long ' declare a variable i for our loop
Dim FinalRow As Long 'declare a variable FinalRow to hold the number of the last cell
FinalRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 9 To FinalRow Step 2
If Range("BA" & i).Value <> Range("BN" & i).Value Then ' if max odds <> last saved max odds then
Range("BB" & i).Value = Range("BB" & i).Value + 1 ' count plus 1
Range("BN" & i).Value = Range("BA" & i).Value ' last saved odds overwriten with new max odds
Range("BC" & i).Value = 0 ' oppposite value cleared
End If
Next i