Mystery problem: cells not being pasted.

[ProphetofProfit]

Hello! The code below runs fine for 800 rows, and then on row 801 it mysteriously ceases to operate. What it does is reads a value in column C of worksheet "games", finds this same value in "teams" and then pastes some values from "teams" to "games". As I said, it works like a dream for 800 rows and then for this particular row, empty cells are being pasted across worksheets.

I've checked that there is numbers to paste from "teams" to "games"
The identifying values are identical. The macro sees 'Verona' in "games" then looks up "Verona" in sheets and they are spelt the same.

When the above happens, my spreadsheet spits out a lot of #DIV/0! errors which leads to a runtime error further down the line. The original problem is the above.

Sub macro1()
Dim i
Dim r
For r = Sheets("Parameters").Range("B9") To Sheets("Parameters").Range("B10")
'for home team
For i = Sheets("Parameters").Range("B11") To Sheets("Parameters").Range("B12")
'Update the Division if you find the team and if the division is left blank (Home Team)
If Sheets("teams").Cells(i, 1).Value = Sheets("games").Cells(r, 4).Value And Sheets("teams").Cells(i, 2).Value = "" Then
 Sheets("teams").Cells(i, 2).Value = Sheets("games").Cells(r, 1).Value
End If
Next i
For i = Sheets("Parameters").Range("B11") To Sheets("Parameters").Range("B12")
'Update the home team values
If Sheets("teams").Cells(i, 1).Value = Sheets("games").Cells(r, 4).Value Then
 'if divisions are same
 If Sheets("games").Cells(r, 1).Value = Sheets("teams").Cells(i, 2).Value Then
 
  Sheets("games").Cells(r, 16).Value = Sheets("teams").Cells(i, 3).Value
  Sheets("games").Cells(r, 17).Value = Sheets("teams").Cells(i, 4).Value
  Sheets("games").Cells(r, 18).Value = Sheets("teams").Cells(i, 5).Value
  Sheets("games").Cells(r, 19).Value = Sheets("teams").Cells(i, 6).Value
  Sheets("games").Cells(r, 20).Value = Sheets("teams").Cells(i, 7).Value
  Sheets("games").Cells(r, 21).Value = Sheets("teams").Cells(i, 8).Value
  Sheets("games").Cells(r, 22).Value = Sheets("teams").Cells(i, 9).Value
  Sheets("games").Cells(r, 23).Value = Sheets("teams").Cells(i, 10).Value
  Sheets("games").Cells(r, 24).Value = Sheets("teams").Cells(i, 11).Value
  Sheets("games").Cells(r, 25).Value = Sheets("teams").Cells(i, 12).Value
 End If
 
 
 'D1 and D2 combination
 If Sheets("games").Cells(r, 1).Value = "D1" And Sheets("teams").Cells(i, 2).Value = "D2" Then
 
  Sheets("games").Cells(r, 16).Value = Sheets("teams").Cells(i, 3).Value * Sheets("parameters").Range("f3").Value
  Sheets("games").Cells(r, 17).Value = Sheets("teams").Cells(i, 4).Value * Sheets("parameters").Range("g3").Value
  Sheets("games").Cells(r, 18).Value = Sheets("teams").Cells(i, 5).Value * Sheets("parameters").Range("f3").Value
  Sheets("games").Cells(r, 19).Value = Sheets("teams").Cells(i, 6).Value * Sheets("parameters").Range("g3").Value
  Sheets("games").Cells(r, 20).Value = Sheets("teams").Cells(i, 7).Value
  Sheets("games").Cells(r, 21).Value = Sheets("teams").Cells(i, 8).Value
  Sheets("games").Cells(r, 22).Value = Sheets("teams").Cells(i, 9).Value
  Sheets("games").Cells(r, 23).Value = Sheets("teams").Cells(i, 10).Value
  Sheets("games").Cells(r, 24).Value = Sheets("teams").Cells(i, 11).Value
  Sheets("games").Cells(r, 25).Value = Sheets("teams").Cells(i, 12).Value

 

[ProphetofProfit]

Uploaded with ImageShack.us

Above the missing numbers is where the code has worked perfectly, in fact, everything in the orange cells is a result of the code working perfectly.

 

[wigi]

Limit your loops and look at the code executing using F8. Inspect the variables and also what happens on the sheet (take half a screen for VBA-code, half a screen for the Excel sheet). Follow closely what happens.

Also, use formulas like:

=A1=B1

to test whether A1 is equal to B1 (change the cell ranges to the Verona cells of course). There is perhaps a trailing space in the cell.

Wigi

 

[ProphetofProfit]

The =X=Y I tried and was TRUE.

Then I did what you suggested and ran the macro with F8 to see what was happening and all of a sudden the macro starts working! I am starting to think that there is a gremlin in my computer.

 

[ProphetofProfit]

Firstly, I would put money on suicide rates for professional bug fixers being well above national average. I can't take much more myself.

On to the problem.

I am getting #DIV/0! errors in the orange cells when pasting from "teams" to "games" when there is definitely nothing in the macro which is being divided by 0. The only calculation is that the copied number is divided by either 2/3 or 3/2 and that is all, nothing more.

I have checked the code, and the values are divided by one of four cells, these cells are not blank. These references are written as $F$2 in all cases. And, like my previous problem, the code runs fine for hundreds of rows and then BAM! there is a problem out of nowhere.