Using VB6, how can I count group of items in excel spreadsheet fields?

[cassidybklyn]

Gentlemen and ladies please assist.
I populated an excel spreadsheet with data from access table, using "CopyFromRecordset" function.
The excel spreadsheet has been populated accurately. My problem is that in the xls spreadsheet, there are 5 fields and each field has multiple occurence of item: example, the name field has "John" appear five times and so does other fields.
Which vb code can I use to count each field items that has multiple entries?
Thanks a mil.
C.

 

[Bong]

countif?

_________________
Bob Rashkin

 

[SkipVought]

More specifically

Set xl = createobject("excel.application")
'...
nCount = xl.countif(objNameDataRange,strName)

Skip,

_[red][/red]

 

[cassidybklyn]

Please, could you be more specific if you don't mind?

I have already done the "Createobject", opened and populated my xls spreadsheet. Now the spreadsheet have data. Do I have to do that again?

What did you define the "nCount" "strName" as? string or what?
Also what are you referencing with "objNameDataRange" and "strName"?
Thanks.

 

[SkipVought]

dim ncount as integer, stName as string
...
strName = "John"
set objNameDataRange = Range("[i]whatever range has the names in it[/i]")

Skip,

_[red][/red]

 

[cassidybklyn]

Thanks skip. I am getting it. Do I have to define objNameDataRange as Object or is it a built-in function?

Another thing is that "John" is not the only item I want to count. I want to count all the five fields in the spreadsheet.

Example: Values in Excel spreadsheet

Nameass#:Amount:
John:22222:500.00
John:22222:500.00
John:22222:500.00
John:22222:500.00
-----------------
4 4 4
-----------------
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
-----------------
4 4 4
-----------------

The above is exactly what I want to accomplish after the code runs.
Thanks.
C.

 

[SkipVought]

Did you check out Data/Subtotals or Data/PivotTable reports?

Skip,

_[red][/red]

 

[cassidybklyn]

Hi Skip.
Yes, I'm aware of the Data/Subtotal report in excel. But I don't want to do this process manually inside the excel spreadsheet. I want to do the process from vb code because there is a button that the user will click on the form and the process will run.

Also, after running the code which you gave me: =====>
nCount = xl.countif(objNameDataRange,strName), it produced a run-time error 424 - Object Required.

This is what I did:

strName = "3600"
Set objNameDataRange = Rannge("G2")
This line--> nCount = xl.Countif(objNameDataRange,strName)

The code above produced a runtime error 424 - Object Required.
Thanks.
C.

 

[PHV]

xl should be an instantiated Excel.Application object.
If your code is Excel VBA:
nCount = Application.CountIf(objNameDataRange, strName)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Thanks PHV.
What about "objNameDataRange", is this define object also or a built-in function?
C.

 

[PHV]

I think it should be an Excel.Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Yes, it is an Excel Range Object: the range that contains the range of names that you are counting

Skip,

_[red][/red]

 

[SkipVought]

Assuming that you defined and set xl as the Excel.Application Object...

strName = "3600"
Set objNameDataRange = xl.objTheWorkbook.objTheSheet.Range("G2")
 nCount = xl.Countif(objNameDataRange,strName)

BUT...

Why would you look for that string in a range of ONE CELL G2 only? Don't you have many cells to look in, like G2:G999?

Skip,

_[red][/red]

 

[cassidybklyn]

Yes Skip and PHV. It worked. It gave me the count in the variable "nCount". But the thing is that this spreadsheet has 5 fields and each field contains multiple items. I want to record the count of each items right after each item breaks. I want to count items under each field (Name, Pass# and Amount). Like below.

Nameass#:Amount:
John:22222:500.00
John:22222:500.00
John:22222:500.00
John:22222:500.00
-----------------
4 4 4
-----------------
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
-----------------
4 4 4
-----------------
Thanks a million for your help so far(Skip and PHV)

 

[PHV]

Have a look at the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

I've looked at the "Split function", but I don't see its relation to what I want to do in the spreadsheet. More insight please.
Thanks.
C.

 

[PHV]

I meant splitting a field in an items array with ":" as delimiter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Hello PH, sorry to sound like a broken record, but I haven't done this before, could you be kind enough to show me example of what how it's done.
I assume you've seen my excel spreadsheet format above.
Thanks.
C.

 

[PHV]

What is YOUR actual code and where are you stuck ?
Tip: the F1 key shouldn't crash your computer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Here is my actual code:

strName = "3600"
Set objNameDataRange = xl.objTheWorkbook.objTheSheet.Range("G2")
nCount = xl.Countif(objNameDataRange,strName)

And I am not stuck. It is giving me the count to the items "John" in the name field, but I have 5 fields in the spreadsheet that I need to count. Also, I after getting the count for each group of items, I want to write it in a cell right below the group ot items.
Example:
Nameass#:Amount:
John:22222:500.00
John:22222:500.00
John:22222:500.00
John:22222:500.00
-----------------
4 4 4
-----------------
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
-----------------
4 4 4

Does this help?
Thanks.
C.