Results 1 to 10 of 10
Thread: Countif in Macro (Excel 2002)

20070720, 21:32 #1
 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

20070720, 21:39 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070720, 21:45 #3
 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

20070720, 21:52 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20070720, 21:53 #5
 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

20070720, 21:58 #6
 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
HTHJerry

20070720, 22:04 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20070720, 22:09 #8
 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

20070720, 22:11 #9
 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

20070720, 22:45 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>