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.

 

[SkipVought]

Where is the code where you set objTheWorkbook and objTheSheet?

Skip,

_[red][/red]

 

[cassidybklyn]

Skip,
Below is the entire code I am executing. Mind you the code worked and gave me the count for only one column. But I have items in other columns. In otherwords, the spreadsheet contains 5 fields and I need to count unique items in each field (column). Also, after the count I need to write the count of each unique item under that item. I hope I'm clear enough.


Private Sub cmdgo_Click()
Dim rs1 As Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWksht As Object

Dim recArray As Variant

Dim dbs As Database
Dim DbPath1 As String
Dim DbName1 As String
Dim StrDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Dim strSQL As String
Dim qdf As QueryDef
Dim strHead As String
Dim strSheetName As String
Dim strListAddress As String
Dim nCount As Integer
Dim strName As String
Dim objNameDataRange As Object
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim EndRow As Long

'Define path to the database either on C:\ or Server
DbPath1 = "C:\"

'Define the name of the database
DbName1 = "Unclaimed Checks.mdb"

'Connect to the database
Set dbs = OpenDatabase(DbPath1 & DbName1)

strSQL = "SELECT tblUnclaimed.PassNumber,tblUnclaimed.EmployeeName, " _
& "tblUnclaimed.Original_Check_Number,tblUnclaimed.Amount_of_Check,tblUnclaimed.Original_Check_Date, " _
& "EmployeeInfo.Status,tblL3Desc.L3,tblL3Desc.L3_Desc " _
& "FROM (EmployeeInfo Right JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 " _
& "AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) LEFT JOIN tblL3Desc " _
& "ON EmployeeInfo.L1 = tblL3Desc.L1 AND EmployeeInfo.L3 = tblL3Desc.L3 " _
& "AND EmployeeInfo.L5 = tblL3Desc.L5 " _
& "WHERE tblUnclaimed.Original_Check_Date < #2/8/2007# " _
& "AND tblUnclaimed.Status = 'U'; "


'Open the recordset 'based on the input table from your database
Set rsin = dbs.OpenRecordset(strSQL)

'Create and instance of Excel application and add an Excel Workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWksht = xlWb.Worksheets("Sheet1")

'Create header fields to the first row of the Excel worksheet
xlWksht. Range("A1").Value = "Pass#"
xlWksht.Range("b1").Value = "Employee Name"
xlWksht.Range("C1").Value = "Check #"
xlWksht.Range("D1").Value = "Check Amount"
xlWksht.Range("E1").Value = "Check Date"
xlWksht.Range("F1").Value = "Status"
xlWksht.Range("G1").Value = "L3"
xlWksht.Range("H1").Value = "L3 Description"

xlWksht.Range("A1:G1").Font.Bold = True

xlWksht.Range("A2").CopyFromRecordset rsin

xlApp.Visible = True
xlApp.UserControl = True

strName = "3600"
Set objNameDataRange = Range("G2:G143")
nCount = xlApp. CountIf(objNameDataRange, strName)


End Sub

 

[SkipVought]

     Set objNameDataRange = [b]xlWksht.[/b]Range("G2:G143")

It works for me.

Skip,

_[red][/red]

 

[cassidybklyn]

Okay, I will definitely do that. But how do I get it to count all the items in the five columns I have in my spreadsheet then write the count after each item?
I have items in columns "A2 thru H2" all the way down to row 143 is filled with data imported from access table.

Thanks.
C.

 

[SkipVought]

dim r as range
Set objNameDataRange = xlWksht.Range("G2:G143") 
for each r in xlWksht.Range("A2:A143") 
     xlWksht.Cells(r.row, "I").value = xlApp.CountIf(objNameDataRange, r.value)
next

Skip,

_[red][/red]

 

[cassidybklyn]

Thanks skip. In the loop, shouldn't it be "Next r" or is it simply "Next"? Also, I don't see "I" defined anywhere, what does that "I" stand for?
Thanks.
C.

 

[SkipVought]

Can be Next r or Next -- either works.


Column "I", or it could be 9, as "I" is the 9^th column.

Skip,

_[red][/red]

 

[cassidybklyn]

I got it Skip.
Thanks and God bless you.
C.

 

[SkipVought]

So how's Brooklyn in May?

Skip,

_[red][/red]

 

[cassidybklyn]

Brooklyn is "Kicking".

 

[cassidybklyn]

Hi Skip,
I've set up the code you me as so:

dim r as range
Set objNameDataRange = xlWksht.Range("A2:H143")
for each r in xlWksht.Range("A2:H143")
xlWksht.Cells(r.row, "I").value = xlApp.CountIf(objNameDataRange, r.value)
next

And it's working and producing the count,but its arrangement in the spreadsheet is a bit out of place. For example this is what it is doing:

Nameass#:Count:
John:77881:4
John:11881:4
John:77881:4
John:77881:4
jane:55555:3
jane:55555:3
jane:55555:3
Pete:99999:1

As you can see, it's recording the group count on every line. Is there a way I count get it to record the count of each group just once. I mean like a control break on a different group of item, then record the count below the item. Like so:

Nameass#:Count:
John:77881:
John:11881:
John:77881:
John:77881:4
jane:55555:
jane:55555:
jane:55555:3
Pete:99999:1

Or record the count below each group of items. Like so:

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
-----------------
3 3 3
-----------------

Thanks.

 

[SkipVought]

Have you looked at the Subtotal FEATURE (not the function) -- Data/Subtotal...

Alternatively, you could use the Confitional Formatting FEATURE -- Format/Conditional Formatting...

Or you could modify your code...

dim r as range, i as integer
Set objNameDataRange = xlWksht.Range("A2:H143") 
i = 1
for each r in xlWksht.Range("A2:H143") 
  if objNameDataRange(i) <> objNameDataRange(i).offset(1) then
     xlWksht.Cells(r.row, "I").value = xlApp.CountIf
(objNameDataRange, r.value)
  end if
next

Skip,

_[red][/red]

 

[cassidybklyn]

Good morning Skip.
Yeah, I've seen the Data/Subtotal thing. But I do not want to manually do it from the spreadsheet, that's the problem, and that's why I want to do it from VB. I just want the user to click a button on the form (GUI) and it does everything. However, I'm getting there, using your expertise and tweaks.
I gonna give it a shot and I'll get back to you.
Thanks.
C.

 

[SkipVought]

"...I do not want to manually do it from the spreadsheet..."

Don't have to. It can be programmed. Turn on your macro recorder, go thru it once, observe your code and modify to suite.

post back with your recorded code if your need help.

Skip,

_[red][/red]

 

[cassidybklyn]

Hi Skip,
The code below worked;

dim r as range, i as integer
Set objNameDataRange = xlWksht.Range("A2:H143")
i = 1
for each r in xlWksht.Range("A2:H143")
if objNameDataRange(i) <> objNameDataRange(i).offset(1) then
xlWksht.Cells(r.row, "I").value = xlApp.CountIf
(objNameDataRange, r.value)
end if
next


But it gave me the same result as before. It listed the coount on every cell in Cells("I"), as opposed to listing the count after a break on each unique items(records). However, earlier, you gave me a hint on using "Subtotal", so I researched it and used it and it worked.See code below:

Selection.Subtotal groupBy:=7, Function:=xlCount, TotalList:=Array(7), _
PageBreak:=False, SummaryBelowData:=True

However, it is giving the count of each group of unique items in several locations. For example:
the item "3600" 5 occurences and counted and listed the count as 5. Down the items "3600" has 3 more occurences so it counted that batch and listed the count again.
Like so:
3600
3600
3600
3600
3600
===>3600 Count 5
3700
3700
===>3700 Count 2
3600
3600
3600
===>3600 Count 3.

Is there a code I could use to sort all the records based on that field, so that all "3600" stay in one location and "3700" stays in a location.
Then after sorting, I will then do my count routine.
Thanks.
C.

 

[SkipVought]

First sort on the field(s) necessary to properly perform a Subtotal.

Is this all for objNameDataRange? If so..

with objNameDataRange 
  .sort. _
     Key1:=.find("[i]name of heading being sorted[/i]"), Order1:=xlAscending, _
     Header:= xlYes, _
     OrderCustom:=1, _
     MatchCase:=False, _
     Orientation:=xlTopToBottom, _
     DataOption1:=xlSortNormal
   .Subtotal _
     groupBy:=7, _
     Function:=xlCount, _
     TotalList:=Array(7), _
     PageBreak:=False, _
     SummaryBelowData:=True
end with

Skip,

_[red][/red]

 

[cassidybklyn]

Skip,
Ikinda tweaked it with ur code and it work real good. Thnx a mil. Still owe you that "diet best stuff on earth" Snapple.
Have a great day.
Cassidy.

 

[SkipVought]

That's wut it all 'bout -- Tweakin'

Tweakin' during tweak, until tweakin', an' takin' tweakin off!

Skip,

_[red][/red]