What's wrong with this VBA code please?

[danjuma]

Would be very grateful if somebody could please point out what's wrong with this VBA code please?

Code: Select all

If RPdatacopied = False Then
 Sheets("Win").Range("BS5:BT40").Value = ""
 endR = Sheets("RPData").Range("A1").Value
 r2 = 5
For r = 3 To endR
  If Sheets("RPData").Cells(r, 7).Value = 1 Then
     Sheets("Win").Cells(r2, 71).Value = Sheets("RPData").Cells(r, 3).Value
     Sheets("Win").Cells(r2, 72).Value = Sheets("RPData").Cells(r, 6).Value
  End If
  r2 = r2 + 1
Next r
 RPdatacopied = True
End If

Basically, data is meant to be copied from sheet "RPData" to sheet "Win" starting from row 5, anytime I change markets (i.e. the variable 'RPdatacopied' becomes False. Data is being copied alright, but for some reason it is not starting from row 5 in sheet "Win", and just keeps starting from different rows randomly - sometimes row 30, sometimes row 57 and so on. So, the problem would seem to be with the variable r2, but I can't understand as I thought I have made it to start from row 5 by r2 = 5. Help please!

Thanks
Dan

[alrodopial]

I don't think it solves your problem but should't the r2=r2+1 be inside the previous if statement?

Code: Select all

If Sheets("RPData").Cells(r, 7).Value = 1 Then
     Sheets("Win").Cells(r2, 71).Value = Sheets("RPData").Cells(r, 3).Value
     Sheets("Win").Cells(r2, 72).Value = Sheets("RPData").Cells(r, 6).Value
     r2 = r2 + 1
  End If
 

[danjuma]

I don't think it solves your problem but should't the r2=r2+1 be inside the previous if statement?

Code: Select all

If Sheets("RPData").Cells(r, 7).Value = 1 Then
     Sheets("Win").Cells(r2, 71).Value = Sheets("RPData").Cells(r, 3).Value
     Sheets("Win").Cells(r2, 72).Value = Sheets("RPData").Cells(r, 6).Value
     r2 = r2 + 1
  End If
 

Thanks for the reply alrodopial. I won't have thought so. Basically, I have 7 columns and any number of rows up to 500 of data in sheet 'RPData'. Column 4 contains the race details (in the format exactly as in sheet 'Win' A1). Column 3 contains horse's names, and column 6 contains the RP odds. When I select a race on BA (which is linked to my workbook), I have an excel formula in column 7 that inserts 1 in the cell against the name of a horse that's in that race e.g. if sheet 'RPData' cell D3 = sheet 'Win' cell $A$1, cell sheet 'RPData'cell F3 = 1. So my code above is to loop through the rows in RPData starting from row 3 to the number of rows with data, and copy the horse's name and odds that have got 1 in column 7 (F) to columns 71 (BS) and 72 (BT) in sheet 'Win'. But to copy this data to sheet 'Win' starting from row 5, and next row until no more selections. Hence reason why I think inserting r2=r2 + 1 where you have suggested won't work. Like I already stated, it is copying the correct data ok, but just not copying to sheet 'Win' starting from row 5 for some reason, and just starting from random rows. Thanks

[alrodopial]

Sorry, I can't help you but the way I see your code, the purpose of r2 is to find/declare the next free row (where the next data will be copied to) and so it must be increasing only if data has JUST been copied (Sheets("RPData").Cells(r, 7).Value = 1) , that's why I suggested it should be inside the if statement ( data just copied, increase the r2 so next data can be copied at free row)
This is my view

[danjuma]

Sorry, I can't help you but the way I see your code, the purpose of r2 is to find/declare the next free row (where the next data will be copied to) and so it must be increasing only if data has JUST been copied (Sheets("RPData").Cells(r, 7).Value = 1) , that's why I suggested it should be inside the if statement ( data just copied, increase the r2 so next data can be copied at free row)
This is my view

Thanks for your reply alrodopial, much appreciated. I will try your suggestion tomorrow and see if it solves the issue. Cheers

[danjuma]

I don't think it solves your problem but should't the r2=r2+1 be inside the previous if statement?

Code: Select all

If Sheets("RPData").Cells(r, 7).Value = 1 Then
     Sheets("Win").Cells(r2, 71).Value = Sheets("RPData").Cells(r, 3).Value
     Sheets("Win").Cells(r2, 72).Value = Sheets("RPData").Cells(r, 6).Value
     r2 = r2 + 1
  End If
 

alrodopial, I eat my words! I followed your suggestion above, and it's solved the problem. Many thanks for the suggestion. Happy new year!

[alrodopial]

Glad I helped,
that's why we are here, to help each other.
Happy new year to everyone!