Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Countif in Macro (Excel 2002)

    Hi,
    I have the code below in my macro and it works great... now I want to ADD another function to the loop, but I have no clue how to write it in the macro... the following is the formula I have in Excel (which works) and I'd like to add it to my macro?!?!?
    =IF(I5=I4,G4+1,COUNTIF($I$5:$I$65536,I5)-COUNTIF($I$5:$I$65536,I5)+1)

    Thanks!!
    Lana
    lngRow = 5
    Do While Sheets("GL").Range("F" & lngRow) <> ""
    Sheets("GL").Range("I" & lngRow) = Left(Sheets("GL").Range("E" & lngRow), 2)
    lngRow = lngRow + 1
    Loop

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

    Re: Countif in Macro (Excel 2002)

    Your formula doesn't make sense to me. COUNTIF($I$5:$I$65536,I5)-COUNTIF($I$5:$I$65536,I5) always cancels out to 0, so why is it included?

    Perhaps someone can help if you explain what you want to accomplish, and why you need to use VBA instead of a formula.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Countif in Macro (Excel 2002)

    Good point... what the formula does in Excel is this:
    I have the company number in column I. I'm needing to number the lines for each company number. For example, I might have 9 lines of data that all have company number 99 listed in column I. I need column G to number them 1 thru 9. Then I may have 5 lines of data for company number 85, and then column G will need to number them 1 thru 5, etc. My Excel formula does this, and I'm wanting to add it to my macro. Hope this makes sense. Sorry for my vagueness in the first message.
    Thanks!
    Lana

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

    Re: Countif in Macro (Excel 2002)

    Do I understand correctly that all rows for the same company are grouped together? Or can company 99 occur in row 22,23,24 and 46, for example?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Countif in Macro (Excel 2002)

    You are correct, all the company 99's are together, etc.
    Thanks,
    Lana

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Countif in Macro (Excel 2002)

    Hi Lana

    Hans will probably come up with a better idea but here a simple little formula to put in G

    =IF(I2=I1,G2+1,1)

    You will have to put a one in the first cell in the column G (G1 for my example) and have all the values in order in column I

    HTH
    Jerry

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

    Re: Countif in Macro (Excel 2002)

    Try this:

    lngRow = 5
    Do While Sheets("GL").Range("F" & lngRow) <> ""
    Sheets("GL").Range("I" & lngRow) = Left(Sheets("GL").Range("E" & lngRow), 2)
    If Sheets("GL").Range("I" & lngRow) = Sheets("GL").Range("I" & (lngRow - 1)) Then
    Sheets("GL").Range("G" & lngRow) = Sheets("GL").Range("G" & (lngRow - 1)) + 1
    Else
    Sheets("GL").Range("G" & lngRow) = 1
    End If
    lngRow = lngRow + 1
    Loop

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Countif in Macro (Excel 2002)

    Hi Jerry,
    Thanks for your help!
    Lana

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Countif in Macro (Excel 2002)

    Bingo... this works awesome! Thanks bunches Hans!!! Have a great weekend!
    Lana

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

    Re: Countif in Macro (Excel 2002)

    To avoid endlessly repeating Sheets("GL"), you could use
    <code>
    With Sheets("GL")
    lngRow = 5
    Do While .Range("F" & lngRow) <> ""
    .Range("I" & lngRow) = Left(.Range("E" & lngRow), 2)
    If .Range("I" & lngRow) = .Range("I" & (lngRow - 1)) Then
    .Range("G" & lngRow) = .Range("G" & (lngRow - 1)) + 1
    Else
    .Range("G" & lngRow) = 1
    End If
    lngRow = lngRow + 1
    Loop
    End With</code>

Posting Permissions

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