Use count to stop macro (2000)

Topic

New Reply

Hello All,

I have gone brain dead this morning …. On the attached simple sheet I have created a macro to put sequential numbers in column b. I am trying to use the IsEmpty formula to look at the cells in column A –a1 to a7 and when the macro would encounter the first blank cell it should stop and choose cell c1. Does IsEmpty work in this instance?

Thanks,
cvbs

Reply | Quote

Replies

You could use

Sub ss()
Dim i As Long
Range(“B1”) = 1
For i = 2 To Range(“A” & Rows.Count).End(xlUp).Row
Range(“B” & i).FormulaR1C1 = “=R[-1]C+1”
Next i
End Sub

Note that the macro doesn’t select cells. It sets the value or formula directly, without selecting the cell. This is generally more efficient.

Reply | Quote

No, you have several problems. First, ISEMPTY only works on a single cell not a range. Second, your IF statement doesn’t do anything if ISEMPTY returns true. Does this do what you want?


Sub ss()
Dim i As Integer
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Select
For i = 1 To Range("a1:a7").Cells.Count
If IsEmpty(ActiveCell.Offset(0, -1)) Then
Exit For
End If
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Offset(1, 0).Select
Next i
Range("C1").Select
End Sub


Reply | Quote

Good Morning Legare and Hans,

Both approaches work just great. Thanks again and I will study each line by line to get a solid understanding for what each part of the code does so I can adapt in the future. And the best thing is knowing that if I just don’t understand…. I can come to ask for help at anytime.

VERY Much appreciated !
cvbs

Reply | Quote

Good Morning again All,

I have tried to set the attached to code to work on this sheet and I constantly get an “invalid Next …”error message no matter where I place the “Next i” Any Ideas to correct this are appreciated

Thanks again
cvbs

Reply | Quote

It needs to be after the

Next intCol

since the “for i” statement is before the “For intCol”

Steve

Reply | Quote

Thanks Steve ,

that allows the macro to run, however I thought it should stop have stopped at row 17 not go all the way to 20 based on the IsEmpty statement and it doesn’t …. any ideas?

cvbs

Reply | Quote

You go through the rows twice. the frist time ( in the introw loop) you don’t check. You only check in the second go around in the i loop.

What is the purpose of the 2 loops? why have the introw loop at all?

I also don’t understand this line:

Select Case Cells(intRow) / Range(F6)

Do you mean Range(“F6”) since F6 is not a defined variable? But the cell F6 is blank so it will yield an division by zero error. Also Cells(introw) are cells A1:A20 which have text so this will also give a type mismatch error when you try to divide…

Steve

Reply | Quote

Is this what you are after?
Steve

Option Explicit
Sub ColorForAll()
  Dim intRow As Integer
  Dim intCol As Integer
  Dim ColChoice As Integer
  For intRow = 1 To 20
    If IsEmpty(Cells(intRow, 1)) Then
      Exit For
    End If
    For intCol = 2 To 4
      Select Case Cells(intRow, intCol) / Range("F1")
        Case Is >= 1.1
          ColChoice = 6
        Case Is >= 0.95
          ColChoice = 35
      End Select
      Cells(intRow, intCol).Interior.ColorIndex = ColChoice
    Next intCol
  Next intRow
End Sub

Reply | Quote

Steve,

This will do the trick… Thanks.

You are right I should have the range set for column “B” instead of “A” and it should have been Range(“F6”) …. but I am still perplexed as to why the IsEmpty statement did not work and stop the macro at row 17 instead of going all the way to 20 each time? Can you explain please.
cvbs

Reply | Quote

The first loop in your code exits when it finds an empty cell, but you don’t do anything with the loop index i – you start a new loop with loop index intRow. So you might as well have omitted the first loop, it serves no purpose whatsoever.

Steve reuses the loop index intRow from the first loop in the second loop, so that you know where the first loop was exited.

Reply | Quote

Gentleman,

Knowing this may sound VERY stupid to all that are in the know I still want to say it just for those like me who are just starting out and do not have the total grasp on vba.

I was thinking that since I set the cell to “c1” by “Select” that that was the constant “active cell ” while the macro performed, and thus I set the IsEmpty to refer to -2 thinking all along that this was referring to Column 1. for each row checked as it proceeded from 1 to 20.

In the attached code… using the “active cell. offset statement , why do the cells get colored all the way down to row 20 in column “B”. I could see it in happening in “C” and “D” but why doesn’t the 0, -1 refer to Column A when the macro is working on column B?

Just to make sure I understand what you have already said…. does this paraphrase it?
So what you are saying is when looking at something like this one needs to know that the “active cell ” remains fluid and as such the “offset” statement will not work., and one MUST declare the the column itself (ie “A”) to check for blank cell. Is this correct ?

As to the 2 declarations of “For” statements…. I see that now after Hans pointed it out AGAIN and as many times as I looked at it before I still did not see that I had declared it twice!!

Thank you again for the excellent help and the major learns.

Reply | Quote

it helps when one makes the attachment

sorry about that !
cvbs

Reply | Quote

Which of the macros in your attachment are you asking about?

Reply | Quote

Steve,
Thanks for the detail….

Hans,
Sub ColorForAll

Thanks for the help

cvbs

Reply | Quote

Sorry, there is no macro named ColorForAll in your latest attachment.

Reply | Quote

Because the ActiveCell is always C1 and thus
ActiveCell.Offset(0, -1) is always B1 and B1 is never empty.

If you notice in my code in post 695,255 I moved the cell to check down the rows as the intRow increased [IsEmpty(Cells(intRow, 1))] so that A1, A2…A17, A18 were checked.

Steve

Reply | Quote

Steve, Hans

I was thinking that the code:
For intRow = 1 To Range(“a1:a20”).Cells.Count
If IsEmpty(ActiveCell.Offset(0, -1)) Then
Exit For

My thoughts were this: I thought [For intRow = 1 To Range(“a1:a20”) …] not only set the area to check but also changed the “active cell” in column “b” as it worked down the rows.. (ie) b1 checked against a1, then b2 checked against a2 …. and so on using the code: — [ If IsEmpty(ActiveCell.Offset(0, -1)) Then
Exit For ] until it would see cell A 17 empty and quit at that time.

What I hear you saying is that “b1” remains constant as the “active cell” and thus the full 20 rows get colored because “b1” compared to “a1” is never empty. Is that what you are saying here.

Is there something that I could have added to this line that would have made the “active cell” move since this did not? I am not trying to be a pain I am just trying to really understand and learn.

Sub ColorForAll()
Dim intRow As Integer
Dim intCol As Integer
Dim ColChoice As Integer
Range(“b1”).Select
For intCol = 2 To 4
For intRow = 1 To Range(“a1:a20”).Cells.Count
If IsEmpty(ActiveCell.Offset(0, -1)) Then
Exit For
End If
Select Case Cells(intRow, intCol) / Range(“F1”)
Case Is >= 1.1
ColChoice = 6
Case Is >= 0.95
ColChoice = 35
End Select
Cells(intRow, intCol).Interior.ColorIndex = ColChoice
Next intRow
Next intCol
End Sub

Reply | Quote

The line For intRow = 1 To Range(“a1:a20”).Cells.Count has nothing to do with the active cell, it only increases a counter intRow from 1 to 20. Nothing more, nothing less. To move the active cell, you need code that selects a cell, thereby making that cell the active cell. Since your code doesn’t select a cell, it doesn’t move the active cell. You could have used this:

For intRow = 1 To Range(“a1:a20”).Cells.Count
‘ Test if active cell is blank
If IsEmpty(ActiveCell.Offset(0, -1)) Then
‘ If so, get out
Exit For
End If
‘ Code to color cell
…
‘ Make next cell active
ActiveCell.Offset(1, 0).Select
Next intRow

But there are far more efficient ways to do this – see replies higher up in this thread.

Reply | Quote

Hans,

Many THANKS again to you and the team…. and I agree there are far more efficient ways… as in the one that Steve wrote….. and perhaps someday I too will have all that knowledge to be the teacher .. until then thoe I do remain the humble, inquisitive, pain in the neck student.

Reply | Quote

I explained why in post 695,252 and Hans gave some indication in post 695,264

But to be a little more explict. If you step through the code you see:

i = 1, check for Isempty (A1) – it is not
Then intCol = 2
Introw =1
Introw =2
…
introw = 18
introw = 19
introw = 20

While i = 1 and the isempty has been checked, you go through rows 1-20 with intRow and in this loop you do not check. You won’t end the for until i = 18 and that has a long way to go:

with i = 1, Then intCol = 2, then intRow = 1-20
Then (while i still is 1), intCol = 3, intRow = 1-20
Then (while i still is 1), intCol = 4, intRow = 1-20
Then (while i still is 1), intCol = 5, intRow = 1-20

Now i = 2 check for Isempty (A2) – it is not
Then intCol = 2, then intRow = 1-20
Then (while i still is 2), intCol = 3, intRow = 1-20
Then (while i still is 2), intCol = 4, intRow = 1-20
Then (while i still is 2), intCol = 5, intRow = 1-20

loop some more (i =3 to 16) … until:

i = 17 check for Isempty (A17) – it is not
Then intCol = 2, then intRow = 1-20
Then (while i still is 17), intCol = 3, intRow = 1-20
Then (while i still is 17), intCol = 4, intRow = 1-20
Then (while i still is 17), intCol = 5, intRow = 1-20

Then finally:
i = 18, check for isempty (A18) yes it is
FOR now ends….

You are going through the rows a lot more than you have to, which is why I changed the original i-loop to intRow loop and eliminated the original intRow loop.

Steve

Reply | Quote