Untidy vba

[jumbo]

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

[spreadbetting]

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

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

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

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
  

[jumbo]

Thanks Spreadbet, thats an excellent explaination of how variables and a loops work , i'll have a go and see if i can put that into my code.

Mike..