Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Use count to stop macro (2000)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use count to stop macro (2000)

    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.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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?

    <code>
    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
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use count to stop macro (2000)

    It needs to be after the

    Next intCol

    since the "for i" statement is before the "For intCol"

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use count to stop macro (2000)

    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:

    <pre>Select Case Cells(intRow) / Range(F6)</pre>


    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use count to stop macro (2000)

    Is this what you are after?
    Steve

    <pre>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</pre>


  10. #10
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use count to stop macro (2000)

    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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Use count to stop macro (2000)

    I explained why in <post#=695,252>post 695,252</post#> and Hans gave some indication in <post#=695,264>post 695,264</post#>

    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. [I could have just eliminated the intRow loop, but I liked the variable name better as it was more descriptive....]

    Steve

  13. #13
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    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!! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

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

  14. #14
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Use count to stop macro (2000)

    it helps when one makes the attachment

    sorry about that !
    cvbs
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use count to stop macro (2000)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •