Macro to Count and insert values

[Rowan]

Hello all,

I am trying to write a macro that will run from a vb6 application as
part of a report function. The report includes 18 columns including
InvoiceNumber and ItemNumber. I am having a hard time explaining what
I need. I am going to give an example of what I need instead...

InvoiceNumber ItemNumber InvoiceCount ItemCount
Invoice1 Item1 1 1
Invoice1 Item2 1 2
Invoice1 Item3 1 3
Invoice2 Item1 2 1
Invoice3 Item1 3 1
Invoice3 Item2 3 2

From this example, what I need is to come up with the values for
InvoiceCount and ItemCount and insert them. But I haven't a clue how
to go about doing this. I have been messing with this:

Sub FindDupsInsert()

invCount = 0

ScreenUpdating = False
Range("J2").Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value

Do While ActiveCell <> ""
If SecondItem <> FirstItem Then invCount = 0
invCount = invCount + 1
Columns.Select
ActiveCell.Cells = invCount
ActiveCell.Value = invCount
Range("J + invCount").Select
ActiveCell.Offset(1, 0).Select
End Sub

But I think I am way off. Could anybody give me an idea how to go
about doing this? I would very much appreciate it.

--Rowan

 

[Jim Rech]

I cannot tell what you want to do from this post. You should describe it in
words. Like "I want to go down the InvoiceNumber column until the end,
which is an empty cell. There are no empty cells in this column. Each time
the invoice number changes I want to ....."

 

[Tushar Mehta]

Have you looked at creating a PivotTable? It might make your life a
little easier.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions