Array ? Increment Counter on..

[jamesgreen55]

Hi All, I am fairly new to VBA and have what may be a complex issue?

In summary I need a counter in column "B" which is based on duplicate
values in column "A" see example below; (Data is already sorted).
Note; look for a macro to do this as opposed to a formula- unless of
course the forumla is in the Macro, so long as it is not by itself in
a cell if that makes sense at all .
A B
apple 1
apple 2
apple 3
orange 1
pear 1
pear 2
cherry 1
banana 1
banana 2
banana 3
banana 4

Any Ideas? or samples you can point me too?
Thanks in Advance!
James

 

[Tony29]

The easiest way is to put a formula in colB

=COUNTIF(A$2:A2,A2)

A$2 is the top cell in col A, ie. the one containing the first "apple"

Tony

 

[Zumble]

Use a Pivot table.
Since I discovered Pivot tables I have never written a single macro.
And all the Excel stuff that was using macros where I work was converted
into Pivot tables.
Macros ? All gone !
Pivot tables are not rocket science, but once you've got your brain around
the concept, your efficiency go exponential!!

Zumble.

 

[Zumble]

Here is the result of your data in a Pivot table
Sum of B
A Total
apple 6
banana 10
cherry 1
orange 1
pear 3
Grand Total 21


(This little table will probably messed up when it is diplayed as plain text
in the newsgroup.)


Total formula used ? Zero.
Total functions used ? Zero.
Total time spent ? 10 seconds.

HTH,
Zumble

 

[jamesgreen55]

Zumble, to be clear I am not looking for the sum of column B based on
the value of A. In my example Column B is the desired output... Nor do
I want a Pivot table that would count the number of observations, I
need it to increment based on the sort order which has other factors
applied to it, such as dates etc...

Hope this clears it up a little, thanks for your ideas. (I added
another field to better define the need. In this example Column C is
the desire result of an algorithm).

A B C
apple Jan.01.2008 1
apple Jan.02.2008 2
apple Jan.03.2008 3
orange Mar.01.2008 1
pear Feb.01.2008 1
pear Feb.02.2008 2
cherry Apr.01.2008 1
banana Jan.10.2008 1
banana Jan.11.2008 2
banana Jan.12.2008 3
banana Jan.13.2008 4


Sub exArray()

Dim varName() As Integer
Dim iCount As Integer
Dim Max As Integer
Dim counter As Integer
counter = 0

Worksheets("Raw").Activate
Max = Cells(Cells.Rows.Count, "A").End(xlUp).Row
ReDim varName(1 To Max) ' declares the array variable with the
necessary size
For iCount = 1 To Max
****************************** formula needed
here*************************
Next iCount

End Sub

 

[Zumble]

Forgive me if I get you wrong (again).
But I think you're looking for the maximum number of items in the Array so
you can redim it ???
D'ya know about UBound ?? (as in "Upper Boundary")

http://www.anthony-vba.kefra.com/vba/vbabasic3.htm#Resize_an_Array_With_Redim_Statement
It's way down near the middle of the page.

Z.


<[email protected]> a écrit dans le message de (e-mail address removed)...
Zumble, to be clear I am not looking for the sum of column B based on
the value of A. In my example Column B is the desired output... Nor do
I want a Pivot table that would count the number of observations, I
need it to increment based on the sort order which has other factors
applied to it, such as dates etc...

Hope this clears it up a little, thanks for your ideas. (I added
another field to better define the need. In this example Column C is
the desire result of an algorithm).

A B C
apple Jan.01.2008 1
apple Jan.02.2008 2
apple Jan.03.2008 3
orange Mar.01.2008 1
pear Feb.01.2008 1
pear Feb.02.2008 2
cherry Apr.01.2008 1
banana Jan.10.2008 1
banana Jan.11.2008 2
banana Jan.12.2008 3
banana Jan.13.2008 4


Sub exArray()

Dim varName() As Integer
Dim iCount As Integer
Dim Max As Integer
Dim counter As Integer
counter = 0

Worksheets("Raw").Activate
Max = Cells(Cells.Rows.Count, "A").End(xlUp).Row
ReDim varName(1 To Max) ' declares the array variable with the
necessary size
For iCount = 1 To Max
****************************** formula needed
here*************************
Next iCount

End Sub

 

[jamesgreen55]

Forgive me if I get you wrong (again).
But I think you're looking for the maximum number of items in the Array so
you can redim it ???
D'ya know about UBound ?? (as in "Upper Boundary")

http://www.anthony-vba.kefra.com/vba/vbabasic3.htm#Resize_an_Array_Wi...
It's way down near the middle of the page.

Z.

<[email protected]> a écrit dans le message de (e-mail address removed)...
Zumble, to be clear I am not looking for the sum of column B based on
the value of A. In my example Column B is the desired output... Nor do
I want a Pivot table that would count the number of observations, I
need it to increment based on the sort order which has other factors
applied to it, such as dates etc...

Hope this clears it up a little, thanks for your ideas. (I added
another field to better define the need. In this example Column C is
the desire result of an algorithm).

A           B                C
apple     Jan.01.2008  1
apple     Jan.02.2008  2
apple     Jan.03.2008  3
orange   Mar.01.2008  1
pear       Feb.01.2008  1
pear       Feb.02.2008  2
cherry    Apr.01.2008   1
banana   Jan.10.2008  1
banana   Jan.11.2008  2
banana   Jan.12.2008  3
banana   Jan.13.2008  4

Sub exArray()

Dim varName() As Integer
Dim iCount As Integer
Dim Max As Integer
Dim counter As Integer
counter = 0

    Worksheets("Raw").Activate
    Max = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    ReDim varName(1 To Max) ' declares the array variable with the
necessary size
    For iCount = 1 To Max
        ****************************** formula needed
here*************************
    Next iCount

End Sub






- Show quoted text -

I found the answer, Zumble & Tony pointed me in the right direction
*and it appears I was wayyyyyyyy off on my approach, thanks also to
Dave Peterson who provided a similar example via the archives.
(Knowing what to search for is half the battle!).

Sub testme()
Dim LastRow As Long
Dim myFormula As String
Dim wks As Worksheet

myFormula = "=COUNTIF(R2C[-1]:RC[-1],RC[-1])"

Set wks = Worksheets("raw")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
myFormula = Replace(myFormula, "###", LastRow)
.Range("B2").FormulaArray = myFormula
.Range("B2:B" & LastRow).FillDown
End With

End Sub