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.

Hi Vince,

I would expect both code samples to fail since the worksheet only has 256
columns.

The code fails at the observed line because Cells(257,257) is invalid.


---
Regards,
Norman


wrote in message
oups.com...
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.

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.

wrote in message
oups.com...
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.

The line "Cells(i, i).Value = i" will fail when i 256. In the one_Click
Sub, I am guessing that the Exit For is called before i gets to 256, i.e the
first occurrence of "More" is in a row less then 257. I also think that you
are unlikely to intend that the numbers 27 to 999 (or 31 to 999) are output
diagonally through your worksheet. You probably want Cells(i, x).Value = i
in there instead. Change x to be the appropriate column number.

Also, in the way you are testing, "FRUIT" is different from "Fruit", and
"MORE" is different from "More", consider instead:

If UCase$(Cells(i, 2).Value) = "FRUIT" Then

to be case insensitive.

Stewart

So, I need to change the second i to something less than 256?
Believe it or not, the first piece of code works, but I want to do it
correctly.
And since my post, I got the second piece of code to work with the
Cells (i, i) = i code
But,
Since I am only searching one column on every row, can I use:
Cells(i, 2) = i
Because I tried that and it did not work

Thanks, all of the quick replies are most helpful!

Vince

thanks for your reply. (for all the replies)
I tried a col number and that did not correct the problem. (i,
27).Value = i

Maybe I don't understand the code. I thought the purpose of the
Cells(i, i). Value = i was to begin a counter.

Meanwhile, I got the second batch of code to work using (i, i) How
strange is that?

Thanks Stewart... I'm learning and ap[preciate the help. Vince

The Column I am search is either A or B, so the 256 rule I knew nothing
about was lucky programming on my part. Thanks

When your code encouters a "For", it immediately
looks for the key-word "Next" then proceeds with what you tell it, your
saying "the first time thru (between the For Line and the Next line) assign
i = 27; the next time assign i = 28,,, in your case when it gets to i = 257
excel crashes since there is no Column 257 (same as what Norman said).
HTH

wrote in message
oups.com...
thanks for your reply. (for all the replies)
I tried a col number and that did not correct the problem. (i,
27).Value = i

Maybe I don't understand the code. I thought the purpose of the
Cells(i, i). Value = i was to begin a counter.

Meanwhile, I got the second batch of code to work using (i, i) How
strange is that?

Thanks Stewart... I'm learning and ap[preciate the help. Vince

wrote in message
oups.com...
thanks for your reply. (for all the replies)
I tried a col number and that did not correct the problem. (i,
27).Value = i

Maybe I don't understand the code. I thought the purpose of the
Cells(i, i). Value = i was to begin a counter.
That code has nothing to do with the counter. The "For i = 27 to 999" code
is the counter.

The "Cells(i, i).Value = i" code outputs the numbers 27, 28, 29, 30 ... in
cells AA27, AB28, AC29, AD30 etc. on each loop of the counter, continuing
until the other code had found the desired match in column B.

Meanwhile, I got the second batch of code to work using (i, i) How
strange is that?

Thanks Stewart... I'm learning and ap[preciate the help. Vince

I don't think the "Cells(i, i).Value = i" code is adding anything useful to
your macro; you can probably safely comment it out.

Stewart

Re Stewart's reply .... I have removed the "Cell(i,i)=1' statements

Private Sub one_Click()
For i = 27 To 999
If Cells(i, 1).Value = "More" Then <=== Look in colum A ?
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
If Cells(i, 2).Value = "Fruit" Then <=== Look in Column B ?
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



" wrote:

So, I need to change the second i to something less than 256?
Believe it or not, the first piece of code works, but I want to do it
correctly.
And since my post, I got the second piece of code to work with the
Cells (i, i) = i code
But,
Since I am only searching one column on every row, can I use:
Cells(i, 2) = i
Because I tried that and it did not work

Thanks, all of the quick replies are most helpful!

Vince

I will study your suggestions and hopefully get a better understanding
of the code. I hate to admit it, but I'm hacking my way through based
on what I can find on the net and in these groups. I have yet to find a
good book that explains the foundations of excel VBA so 1 plus 1 leads
a person to build on principles 3, 4, and 5. It seems even the "basic"
books jump around the don't explain thngs that must be obvious to the
writer. Thanks again. I enjoy the learning process, nonetheless.
Vince