Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

cassidybklyn

Programmer
Apr 23, 2007
82
US
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.
 
countif?

_________________
Bob Rashkin
 




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


Skip,

[glasses] [red][/red]
[tongue]
 
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.


 


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


Skip,

[glasses] [red][/red]
[tongue]
 
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

Name:pass#: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.
 



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

Skip,

[glasses] [red][/red]
[tongue]
 
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.
 
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
 
Thanks PHV.
What about "objNameDataRange", is this define object also or a built-in function?
C.
 
I think it should be an Excel.Range object.

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




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

Skip,

[glasses] [red][/red]
[tongue]
 

Assuming that you defined and set xl as the Excel.Application Object...
Code:
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,

[glasses] [red][/red]
[tongue]
 
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.

Name:pass#: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)

 
Have a look at the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
I meant splitting a field in an items array with ":" as delimiter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
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
 
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:
Name:pass#: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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top