One error is that columns only go to 256 in Excel so if i 256 before
anything is found, it will fail. Your first one obviously finds "More" before
i 256 and exits the FOR loop..


Cells(i, i).Value = i '<=== second subscript i must be <= 256

Is there an error here?

" wrote:

I don't understand why this code works:

Private Sub one_Click()
For i = 27 To 999
Cells(i, i).Value = i
If Cells(i, 1).Value = "More" Then
Cells(i, 1).Value = Sheets("Prices").Range("A3")
Cells(i, 3).Value = Sheets("Prices").Range("B3")
Cells(i, 4).Value = Sheets("Prices").Range("C3")
Exit For
End If
Next i
End Sub

But this code does not:

Private Sub CommandButton6_Click()
For i = 31 To 999
Cells(i, i).Value = i <<<<<<HERE IS WHERE IT STOPS

If Cells(i, 2).Value = "Fruit" Then
Range("AD12").Select
Application.CutCopyMode = False
Selection.Copy
Range ("i, 2"). Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Exit For
End If
Next i

Range("AA1:AD10").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B30").Select
End Sub

What is confusing is the same line of code works in the fist example
but does not in the second.

Any help is most welcome. In the second example I am searching for a
certain value in the next row with value FRUIT in col B, when I find
that cell, copy the value of another cell into that cell, clear a
certain range so I can begin again.