Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conducting a count on unknown number of records
Hi, I have the relatively simple code below to conduct a count of uniquely
identified records in excel spreadsheet: count = 1 name = "simple_excel_export_table" numrows = 19 For s = 2 To numrows With Worksheets(name) cellb = .Cells(s, 1).Value cella = .Cells(s + 1, 1).Value If cella cellb Then count = count + 1 End If End With Next s For example, this code on the following spreadsheet would result in a count of 4 (there are many more columns in my spreadsheet, this is just a sample): ID UserSiteID 1 61051 1 61051 1 61051 1 61051 1 61051 2 61050 2 61050 2 61050 3 61048 3 61048 3 61048 3 61048 3 61048 4 61043 4 61043 4 61043 4 61043 4 61043 4 61043 however, this code is to be run on multiple sheets with different amounts of records from sheet to sheet. How can I enhance the code to assign the proper number of records, or rows, to the "numrows" variable for each spreadsheet, without having to manually go change the number assigned to "numrows" each time? Or is there a better way to do this?? any help appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conducting a count on unknown number of records
Change Col to point to the column you want to check. This assumes there are
no blank cells in the data in the column. Sub countunique() Dim sh As Worksheet Dim rng As Range Dim sStr As String Dim sForm As String Dim col As Long col = 2 For Each sh In ThisWorkbook.Worksheets Set rng = sh.Range(sh.Cells(2, col), _ sh.Cells(Rows.Count, col).End(xlUp)) sStr = "'" & rng.Parent.Name & "'!" & _ rng.Address(0, 0, xlA1, False) sForm = "SUM(1/COUNTIF(" & sStr & "," & _ sStr & "))" num = Evaluate(sForm) Debug.Print sh.Name, col, num Next End Sub See the result in the immediate window of the VBE. -- Regards, Tom Ogilvy "Giz" wrote in message ... Hi, I have the relatively simple code below to conduct a count of uniquely identified records in excel spreadsheet: count = 1 name = "simple_excel_export_table" numrows = 19 For s = 2 To numrows With Worksheets(name) cellb = .Cells(s, 1).Value cella = .Cells(s + 1, 1).Value If cella cellb Then count = count + 1 End If End With Next s For example, this code on the following spreadsheet would result in a count of 4 (there are many more columns in my spreadsheet, this is just a sample): ID UserSiteID 1 61051 1 61051 1 61051 1 61051 1 61051 2 61050 2 61050 2 61050 3 61048 3 61048 3 61048 3 61048 3 61048 4 61043 4 61043 4 61043 4 61043 4 61043 4 61043 however, this code is to be run on multiple sheets with different amounts of records from sheet to sheet. How can I enhance the code to assign the proper number of records, or rows, to the "numrows" variable for each spreadsheet, without having to manually go change the number assigned to "numrows" each time? Or is there a better way to do this?? any help appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conducting a count on unknown number of records
Hi Tom
This looks like a very neat way of doing this calculation so I was playing around with it to make sure I understand how it works. I was getting strange results (no matter how much data I had on the sheet the range evaluated was always B2:B3). I then changed the sheet name but noticed in the immediate window that the name printed was still Sheet1. Changing the line: For Each sh In ThisWorkbook.Worksheets to For Each sh In Workbooks("MyWorkBookName").Worksheets fixed the problem and now it works fine. I have no other workbook open. Any ideas how this was happening? Regards Rowan "Tom Ogilvy" wrote: Change Col to point to the column you want to check. This assumes there are no blank cells in the data in the column. Sub countunique() Dim sh As Worksheet Dim rng As Range Dim sStr As String Dim sForm As String Dim col As Long col = 2 For Each sh In ThisWorkbook.Worksheets Set rng = sh.Range(sh.Cells(2, col), _ sh.Cells(Rows.Count, col).End(xlUp)) sStr = "'" & rng.Parent.Name & "'!" & _ rng.Address(0, 0, xlA1, False) sForm = "SUM(1/COUNTIF(" & sStr & "," & _ sStr & "))" num = Evaluate(sForm) Debug.Print sh.Name, col, num Next End Sub See the result in the immediate window of the VBE. -- Regards, Tom Ogilvy "Giz" wrote in message ... Hi, I have the relatively simple code below to conduct a count of uniquely identified records in excel spreadsheet: count = 1 name = "simple_excel_export_table" numrows = 19 For s = 2 To numrows With Worksheets(name) cellb = .Cells(s, 1).Value cella = .Cells(s + 1, 1).Value If cella cellb Then count = count + 1 End If End With Next s For example, this code on the following spreadsheet would result in a count of 4 (there are many more columns in my spreadsheet, this is just a sample): ID UserSiteID 1 61051 1 61051 1 61051 1 61051 1 61051 2 61050 2 61050 2 61050 3 61048 3 61048 3 61048 3 61048 3 61048 4 61043 4 61043 4 61043 4 61043 4 61043 4 61043 however, this code is to be run on multiple sheets with different amounts of records from sheet to sheet. How can I enhance the code to assign the proper number of records, or rows, to the "numrows" variable for each spreadsheet, without having to manually go change the number assigned to "numrows" each time? Or is there a better way to do this?? any help appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
conducting a count on unknown number of records
Thisworkbook refers to the workbook that contains the code. If you only
have one workbook open, then that should be the workbook containing the code. -- Regards, Tom Ogilvy "Rowan" wrote in message ... Hi Tom This looks like a very neat way of doing this calculation so I was playing around with it to make sure I understand how it works. I was getting strange results (no matter how much data I had on the sheet the range evaluated was always B2:B3). I then changed the sheet name but noticed in the immediate window that the name printed was still Sheet1. Changing the line: For Each sh In ThisWorkbook.Worksheets to For Each sh In Workbooks("MyWorkBookName").Worksheets fixed the problem and now it works fine. I have no other workbook open. Any ideas how this was happening? Regards Rowan "Tom Ogilvy" wrote: Change Col to point to the column you want to check. This assumes there are no blank cells in the data in the column. Sub countunique() Dim sh As Worksheet Dim rng As Range Dim sStr As String Dim sForm As String Dim col As Long col = 2 For Each sh In ThisWorkbook.Worksheets Set rng = sh.Range(sh.Cells(2, col), _ sh.Cells(Rows.Count, col).End(xlUp)) sStr = "'" & rng.Parent.Name & "'!" & _ rng.Address(0, 0, xlA1, False) sForm = "SUM(1/COUNTIF(" & sStr & "," & _ sStr & "))" num = Evaluate(sForm) Debug.Print sh.Name, col, num Next End Sub See the result in the immediate window of the VBE. -- Regards, Tom Ogilvy "Giz" wrote in message ... Hi, I have the relatively simple code below to conduct a count of uniquely identified records in excel spreadsheet: count = 1 name = "simple_excel_export_table" numrows = 19 For s = 2 To numrows With Worksheets(name) cellb = .Cells(s, 1).Value cella = .Cells(s + 1, 1).Value If cella cellb Then count = count + 1 End If End With Next s For example, this code on the following spreadsheet would result in a count of 4 (there are many more columns in my spreadsheet, this is just a sample): ID UserSiteID 1 61051 1 61051 1 61051 1 61051 1 61051 2 61050 2 61050 2 61050 3 61048 3 61048 3 61048 3 61048 3 61048 4 61043 4 61043 4 61043 4 61043 4 61043 4 61043 however, this code is to be run on multiple sheets with different amounts of records from sheet to sheet. How can I enhance the code to assign the proper number of records, or rows, to the "numrows" variable for each spreadsheet, without having to manually go change the number assigned to "numrows" each time? Or is there a better way to do this?? any help appreciated |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
conducting a count on unknown number of records
thanx for the response.
When I run the code I get the error "variable not defined" on the "num" variable, in the line of code: "num = evaluate (sForm)". I am not sure what to define num, although I think it is a integer or other type of number. So I tried to define it as a integer, then I ran it. When I did that I got a "type mismatch" on the same line of code for "(sForm)". I am not sure what is up so any help would be appreciated. thanx "Tom Ogilvy" wrote: Change Col to point to the column you want to check. This assumes there are no blank cells in the data in the column. Sub countunique() Dim sh As Worksheet Dim rng As Range Dim sStr As String Dim sForm As String Dim col As Long col = 2 For Each sh In ThisWorkbook.Worksheets Set rng = sh.Range(sh.Cells(2, col), _ sh.Cells(Rows.Count, col).End(xlUp)) sStr = "'" & rng.Parent.Name & "'!" & _ rng.Address(0, 0, xlA1, False) sForm = "SUM(1/COUNTIF(" & sStr & "," & _ sStr & "))" num = Evaluate(sForm) Debug.Print sh.Name, col, num Next End Sub See the result in the immediate window of the VBE. -- Regards, Tom Ogilvy "Giz" wrote in message ... Hi, I have the relatively simple code below to conduct a count of uniquely identified records in excel spreadsheet: count = 1 name = "simple_excel_export_table" numrows = 19 For s = 2 To numrows With Worksheets(name) cellb = .Cells(s, 1).Value cella = .Cells(s + 1, 1).Value If cella cellb Then count = count + 1 End If End With Next s For example, this code on the following spreadsheet would result in a count of 4 (there are many more columns in my spreadsheet, this is just a sample): ID UserSiteID 1 61051 1 61051 1 61051 1 61051 1 61051 2 61050 2 61050 2 61050 3 61048 3 61048 3 61048 3 61048 3 61048 4 61043 4 61043 4 61043 4 61043 4 61043 4 61043 however, this code is to be run on multiple sheets with different amounts of records from sheet to sheet. How can I enhance the code to assign the proper number of records, or rows, to the "numrows" variable for each spreadsheet, without having to manually go change the number assigned to "numrows" each time? Or is there a better way to do this?? any help appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula should I use to count a number of records? | New Users to Excel | |||
How do I count the number of records within a date range | New Users to Excel | |||
Excel - count the number of records between two specific dates. | Excel Worksheet Functions | |||
Count the number of AutoFiltered records | Excel Worksheet Functions | |||
Count the number of unique records | Excel Worksheet Functions |