Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
Hi All
Previously i have asked in the forum for help in counting: 1. No. of rows 2. No. of columns 3. No. of numeric cells 4. No. of alphanumeric cells 5. No. of alphabetic cells 6. No. of NULL cells The below are what i have used using excel meaning the results ar shown in excel file format (displayed in cells): 1. COUNTA(Table!A:A)) 2. COUNTA(Table!1:1)) 3. COUNT(Table!B2:Z60000)) 4. COUNTIF(Table!B2:Z60000,"*")) 5. CountAlphas(Table!B2:Z60000)) -- code below Function CountAlphas(CountRange As Range) As Long Dim cell As Range Dim iCtr As Long For Each cell In CountRange If Not IsEmpty(cell) Then If Not cell.Value Like "*[0-9]*" Then iCtr = iCtr + 1 End If End If Next CountAlphas = iCtr End Function 6. COUNTBLANK(Table!B2:Z60000) However the biggest problem now is that i have to switch fro displaying in excel to using vba so that the user interface using m program would look "nicer". I have created userforms that look lik menus for the program, but i am unable to use the above coding as the are not in vba codes. What i need now is them to be in vba codes and t display the results in msg boxes thus enabling the program to look mor professional. As i am totally new to excel vba, i can only hope tha those that are more proficient in it can help out by posting th solutions. Thanks in advanceD -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
Hi Wuming,
Sub Tester() '// (1) MsgBox "# Rows in Table =" _ & Activesheet.Range("Table").Rows.Count '// (2) MsgBox "# Columns in Table = " _ & Activesheet.Range("Table").Columns.Count '// (3) MsgBox "# Numeric cells in Table = " _ & Application.Count(Activesheet.Range("Table")) '// (4) MsgBox "# AlphaNumeric cells in Table = " _ & Application.CountA(Activesheet.Range("Table")) '// (5) MsgBox "#Alpha cells in Table = " _ & CountAlphas(Activesheet.Range("Table")) End Sub --- Regards, Norman "wuming " wrote in message ... Hi All Previously i have asked in the forum for help in counting: 1. No. of rows 2. No. of columns 3. No. of numeric cells 4. No. of alphanumeric cells 5. No. of alphabetic cells 6. No. of NULL cells The below are what i have used using excel meaning the results are shown in excel file format (displayed in cells): 1. COUNTA(Table!A:A)) 2. COUNTA(Table!1:1)) 3. COUNT(Table!B2:Z60000)) 4. COUNTIF(Table!B2:Z60000,"*")) 5. CountAlphas(Table!B2:Z60000)) -- code below Function CountAlphas(CountRange As Range) As Long Dim cell As Range Dim iCtr As Long For Each cell In CountRange If Not IsEmpty(cell) Then If Not cell.Value Like "*[0-9]*" Then iCtr = iCtr + 1 End If End If Next CountAlphas = iCtr End Function 6. COUNTBLANK(Table!B2:Z60000) However the biggest problem now is that i have to switch from displaying in excel to using vba so that the user interface using my program would look "nicer". I have created userforms that look like menus for the program, but i am unable to use the above coding as they are not in vba codes. What i need now is them to be in vba codes and to display the results in msg boxes thus enabling the program to look more professional. As i am totally new to excel vba, i can only hope that those that are more proficient in it can help out by posting the solutions. Thanks in advanceD! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
Hi
don't know if this is the best way, but i used this code recently to return the number of rows & columns ...and display the results in a message box Range("A1").Select MsgBox "the number of rows is " & ActiveCell.SpecialCells(xlLastCell).Row MsgBox "the number of columns is " & ActiveCell.SpecialCells(xlLastCell).Column Cheers JulieD "wuming " wrote in message ... Hi All Previously i have asked in the forum for help in counting: 1. No. of rows 2. No. of columns 3. No. of numeric cells 4. No. of alphanumeric cells 5. No. of alphabetic cells 6. No. of NULL cells The below are what i have used using excel meaning the results are shown in excel file format (displayed in cells): 1. COUNTA(Table!A:A)) 2. COUNTA(Table!1:1)) 3. COUNT(Table!B2:Z60000)) 4. COUNTIF(Table!B2:Z60000,"*")) 5. CountAlphas(Table!B2:Z60000)) -- code below Function CountAlphas(CountRange As Range) As Long Dim cell As Range Dim iCtr As Long For Each cell In CountRange If Not IsEmpty(cell) Then If Not cell.Value Like "*[0-9]*" Then iCtr = iCtr + 1 End If End If Next CountAlphas = iCtr End Function 6. COUNTBLANK(Table!B2:Z60000) However the biggest problem now is that i have to switch from displaying in excel to using vba so that the user interface using my program would look "nicer". I have created userforms that look like menus for the program, but i am unable to use the above coding as they are not in vba codes. What i need now is them to be in vba codes and to display the results in msg boxes thus enabling the program to look more professional. As i am totally new to excel vba, i can only hope that those that are more proficient in it can help out by posting the solutions. Thanks in advanceD! --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
Msgbox WorksheetFunction(COUNTA(Range("Table!A:A")))
Msgbox WorksheetFunction(COUNTA(Range("Table!1:1"))) Msgbox WorksheetFunction(COUNT(Range("Table!B2:Z60000"))) Msgbox WorksheetFunction(COUNTIF(Range("Table!B2:Z60000") ,"*")) Msgbox CountAlphas(Range("Table!B2:Z60000)") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "wuming " wrote in message ... Hi All Previously i have asked in the forum for help in counting: 1. No. of rows 2. No. of columns 3. No. of numeric cells 4. No. of alphanumeric cells 5. No. of alphabetic cells 6. No. of NULL cells The below are what i have used using excel meaning the results are shown in excel file format (displayed in cells): 1. COUNTA(Table!A:A)) 2. COUNTA(Table!1:1)) 3. COUNT(Table!B2:Z60000)) 4. COUNTIF(Table!B2:Z60000,"*")) 5. CountAlphas(Table!B2:Z60000)) -- code below Function CountAlphas(CountRange As Range) As Long Dim cell As Range Dim iCtr As Long For Each cell In CountRange If Not IsEmpty(cell) Then If Not cell.Value Like "*[0-9]*" Then iCtr = iCtr + 1 End If End If Next CountAlphas = iCtr End Function 6. COUNTBLANK(Table!B2:Z60000) However the biggest problem now is that i have to switch from displaying in excel to using vba so that the user interface using my program would look "nicer". I have created userforms that look like menus for the program, but i am unable to use the above coding as they are not in vba codes. What i need now is them to be in vba codes and to display the results in msg boxes thus enabling the program to look more professional. As i am totally new to excel vba, i can only hope that those that are more proficient in it can help out by posting the solutions. Thanks in advanceD! --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
I think Bob intended:
MsgBox WorksheetFunction.CountA(Range("Table!A:A")) MsgBox WorksheetFunction.CountA(Range("Table!1:1")) MsgBox WorksheetFunction.Count(Range("Table!B2:Z60000")) MsgBox WorksheetFunction.CountIf(Range("Table!B2:Z60000") , "*") MsgBox CountAlphas(Range("Table!B2:Z60000")) --- Regards, Norman "Bob Phillips" wrote in message ... Msgbox WorksheetFunction(COUNTA(Range("Table!A:A"))) Msgbox WorksheetFunction(COUNTA(Range("Table!1:1"))) Msgbox WorksheetFunction(COUNT(Range("Table!B2:Z60000"))) Msgbox WorksheetFunction(COUNTIF(Range("Table!B2:Z60000") ,"*")) Msgbox CountAlphas(Range("Table!B2:Z60000)") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
I knew it looked wrong, but wood for the trees<g.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Norman Jones" wrote in message ... I think Bob intended: MsgBox WorksheetFunction.CountA(Range("Table!A:A")) MsgBox WorksheetFunction.CountA(Range("Table!1:1")) MsgBox WorksheetFunction.Count(Range("Table!B2:Z60000")) MsgBox WorksheetFunction.CountIf(Range("Table!B2:Z60000") , "*") MsgBox CountAlphas(Range("Table!B2:Z60000")) --- Regards, Norman "Bob Phillips" wrote in message ... Msgbox WorksheetFunction(COUNTA(Range("Table!A:A"))) Msgbox WorksheetFunction(COUNTA(Range("Table!1:1"))) Msgbox WorksheetFunction(COUNT(Range("Table!B2:Z60000"))) Msgbox WorksheetFunction(COUNTIF(Range("Table!B2:Z60000") ,"*")) Msgbox CountAlphas(Range("Table!B2:Z60000)") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Cells Counting using VBA codes and displaying results in msg box
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 formula results not displaying | Excel Discussion (Misc queries) | |||
Counting cells containing formulas with blank results | Excel Discussion (Misc queries) | |||
counting cells in two columns that have the same results | Excel Worksheet Functions | |||
Counting codes | Excel Discussion (Misc queries) | |||
Counting Zip Codes | New Users to Excel |