# Thread: Countif in Macro (Excel 2002)

1. ## 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. ## 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. ## 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. ## 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. ## Re: Countif in Macro (Excel 2002)

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

6. ## 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

7. ## 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. ## Re: Countif in Macro (Excel 2002)

Hi Jerry,
Thanks for your help!
Lana

9. ## Re: Countif in Macro (Excel 2002)

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

10. ## 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
•