Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Count Occurances In Column

I have 2 columns. Column A contains values of 'Yes' & 'No'. Column B contains names that repeat throughout. In code, I need to count the number of times Name A appears with 'Yes' and 'No'. Same for Name B and so on. No set limit of names.


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Count Character Occurances
Is there a function or something to count the number of occurances of a specific character in a string?

I can implement it using a loop and InStr(), but a builtin would definitely be quicker.

I'm using VB 6.0.

Thanks, Jenda

Count Occurances In Large File...
good morning...

i have the following setup:

there is a large file (> 25.000 rows) which contains status-data in each row, starting in column g2 to g26000, for example

the column is 160 characters wide

"2005. G5338 VAL: 13.69491525".., this a shortened example

i like to know how many entries start with letter G, whereas G may sometimes be followed by another letter with only 3 numbers afterwards, but the length will always be 5 digits

the length to the left of parameter G will always be the same

is there a way to count the occurance of this parameter?

thank you very much

best regards,

Count Word Occurances In Text
Hi there,

I (noob) am currently writing a routine to count the occurences of different words in a given text (see code below). It still has some glitches, but works ...

(The code is merged from different sources (Thinkers too, I think))

The only problem is, that it's a bit slow. Do you have any suggestions how to optimize it for speed?

comments welcome

have fun

Public Function CountWords(strSource As String, strDelim As String, _
Optional Stype As VbCompareMethod = vbBinaryCompare) As Long

Dim strCurrWord As String
Dim lngNext As Long
Dim lngLen As Long
Dim lngStart As Long
Dim lngString As Long

Dim arrString() As String
Dim i As Long
Dim bolFound As Boolean
Dim lngUbound As Long
Dim intArrLoop As Long

' count Spaces to approx. max for Progressbar
Dim position As Long
Dim lngSpaceCount As Long
position = 1
Do While InStr(position, strSource, strDelim)
position = InStr(position, strSource, strDelim) + 1
lngSpaceCount = lngSpaceCount + 1

'init progbar
prgExtract.min = 0
prgExtract.max = lngSpaceCount

lngLen = Len(strDelim)
lngNext = InStr(1, strSource, strDelim, Stype)
lngStart = 1
lngUbound = 0

ReDim Preserve arrString(1, lngUbound)
Do Until lngNext = 0

CountWords = CountWords + 1 'count all words

strCurrWord = Mid$(strSource, lngStart, lngNext - lngStart)

If LenB(Trim$(strCurrWord)) > 0 Then 'only count when not empty
bolFound = False 'init

For i = LBound(arrString) To lngUbound 'loop through existing array

'compare current word and value in current array position
If arrString(0, i) = strCurrWord Then
arrString(1, i) = arrString(1, i) + 1
bolFound = True
Exit For 'exit, cause there is no need to search any further
End If

Next 'i

If bolFound = False Then 'didn't find word in array, so add it to array
lngUbound = lngUbound + 1
ReDim Preserve arrString(1, lngUbound)
arrString(0, lngUbound) = strCurrWord 'word
arrString(1, lngUbound) = 1 'init counter
End If

End If

lngStart = lngNext + 1
lngNext = InStr(lngNext + lngLen, strSource, strDelim, Stype)

prgExtract.value = CountWords

If lngUbound Mod 100 = 0 Then
StaBar.Panels(2).Text = "Checking against " & lngUbound & " words"
StaBar.Panels(3).Text = Format$(Timer - dblStartTimer, "0.00") & " s"
End If


End Function

Quickest Way To Count Number Of Occurances...

What would be the quickest method of counting the number of occurances of a particular character in a text file?

Any examples would be appreciated..


Best Way To Count All Occurances Of A Word In A File
Hello Everyone
i need to be able to count the total number of times a string appears in a text would i go about doing that

Count The Number Of Occurances A Char In A String
Hi all, it's been a long while since I've coded and I wish to count the number of times a specific character appears in a string, I know this is easy but I've drawn a complete blank

eg. search char = *
string to serch = njhbjg**hjg**bhb*

result returns = 5

thanks for any memory jogging answers

Count No Blank Cell In Column AC Based Var In Column Y
With acode in VBA please....

How to count all non blank cells in column AC based a Var filled ="PUGLIA" and return the result of count in var My_count...
In thsi case the coun = 6
I use this formula but difficult to translate in VBA....


Count Value Based Column G And Count It...
with a macro...

in GAF sheet column H with data.
In sheet Corporate, Retail_Poe ecc... have a column G with other data.

I want count how many value are present in column H of GAF refered column G
of CORPORATE and store the result of count in a variable named CORPORATE...

I want count how many value are present in column H of GAF refered column G
of RETAIL_POE and store the result of count in a variable named RETAIL_POE...


The rest of value in GAF not finded store in a vaiable OTHER....


Column Count
I have one recordset.How can i get the number of columns in that recordset?

Count Cells And Add Value In Other Column

I have made a macro to import a textfile in column B(2:n) In column A I want numbers starting from 1 to n (depends on the textinput) I have some macro-experience but to so many.... Some help would be appreciated.


A textfile with 10 numbers is imported so I want the numbers 1 to 10 to be automaticly be generated in column A

1 13
2 230
3 56
4 87
. .
. .
. .
10 10

Thanks in advance.


Line Count Column, How Can I Do This??
please, i need to know a way to show the number of the line you are (like most html editors) in a column aside the textbox, does anyone have any idea?? i made one, but it doesnt work well....

Listview Column 2 Count
I have the sizes in kb of all different files in a directory in the second column of a listview, and i want to add them all up so i can put the full size in a caption.

Row And Column Count In DataGrid
Is there a way of using 'Count' in a DataGrid like the RecordCount I need to be able to read every Cell in the DataGrid and convert into a Text File.

The Text File I can do with ease I know that the DataGrid can be read by using

DataGride1.Row = 0
DataGride1.Cols = 0
StrText = DataGrid1.Text

Or something simular but how do I Know how many Rows and Columns there are

Thanks in advance

How To Count By Column Wise?
Dear Friends,
I have recordset in access2000. I have attendence table and I want to count P or A by columnwise. If it is in row wise than easy to count but how can I count P or A in 31 columns. If there is P and A in columns than I want to count total P and A at the end of month..

Please help me soon.

With regfards,
pratik mehta            

Column To Count Specified At Run-time

To create my report, I dump all of the necessary data into an Access table, and build my report from there. In the report I am currently working on, I need to perform a count of a column. The trick is that I don't know which column the count should be performed on until run-time. The user is able to specify the sort order by adding columns to a list box. My report needs to perform the count based on the first column specified in the list box. Does anyone know how this can be done? I know that you can specify the columns to sort on through VB i.e. frmRfsMain.RfsReport.SortFields(k) = "+{ReportGen.Num1}"
Is there something similar that can be done in VB to specify the column to count? Or does it need to be done through Crystal, and if so, how?

Thx for any and all help.


Line && Column Count In Rtf Box
Hello !!
I want an API function which will count LINE and COLUMN in a rich text box.
Pls help me !!

Thanks alot !!

Excel Column Count
I have one excel file with 28 columns.i want to get the column count of that file thru a recordset from vb?

Getting Row And Column Count For Arbitrary Arrays
I know this is a layup so I thought it'd be faster to ask....

For an arbitrary array, can someone help me get a row and column count?

I'm working with:

Public Function ArrayRows(MyArray() as Variant) as Integer

Dim i

i = 0

For Each i in MyArray()

i = i +1

Next i

ArrayRows = i

End Function

However, it occurs to me that I haven't specified what "i"s are in the array....suggestions?

MSflexgrid Vary Column Count
Is there any way at all to have 3 colums on row 1 and 1 column on row 2 or even a trick to make it look that way? If so I would love to know how.

Column Count Doesn't Match...
I get mysql error "Column count doesn't match value count at row 1"

I know 100% the problem isn't the mysql tables/entries because it worked fine yesterday. It tends to work and not work every now and then. Very mind boggling!

Anyway, here is the code causing the error:

VB Code:
CoInitialize (0) ' using CoInitialize because project is .DLL based    If Not Login(txtalias.Text, txtpass.Text) Then        MsgBox "Incorrect Login", vbInformation + vbOKOnly, "Error"    CoUninitialize ' same..    Else   MsgBox "Works"   End If

And the "Login" function:

VB Code:
Private Function Login(ByVal pstralias As String, ByVal pstrpass As String) As Boolean    Open_dbcon    Set cmdCommand = New ADODB.Command    With cmdCommand        Set .ActiveConnection = dbcon        .CommandType = adCmdText        .CommandText = "SELECT COUNT(*) AS Total FROM users WHERE alias = '" & pstralias & "' AND pass = '" & pstrpass & "' "        Set adoRec = .Execute        Login = (adoRec.Fields.Item("Total").Value > 0)        Set adoRec = Nothing    End With    Set cmdCommand = Nothing    Close_dbconEnd Function

Any spotted problems?

Also, I tried it in a .exe project and it works without any errors. It worked fine yesterday in .DLL format

Sure has me puzzled.

Count Number Of Fields In A Column
How could i make an output parameter that will count the number of rows that return a null value.

I have rows and one of the columns vendor_id can return a null value. I need to count how many have a null value in the query i perform as an output parameter?


DataGrid Total Column Count
Here is a question in VB6 you could do something like this to go through all the cells in a MSHFlexGrid1

PHP Code:

 With MSHFlexGrid1
      For iCol = 0 To MSHFlexGrid1.Cols - 1
        If iCol = 1 Then
          mstTitle = .TextMatrix(iRow, iCol)
        End If
        If iCol = 2 Then
          mstDescription = .TextMatrix(iRow, iCol)
        End If
        If iCol = 13 Then
          mstRecordID = .TextMatrix(iRow, iCol)
        End If
        stColumnHeader = MSHFlexGrid1.ColHeaderCaption(0, iCol) & ": "
        stDisplayText = stDisplayText & stColumnHeader & .TextMatrix(iRow, iCol) & vbCrLf & vbCrLf
    End With 

Now in VB.NET I am trying to do something like that when a user clicks a cell in a DataGrid.

PHP Code:

 With DataGrid2.Item(iCol, iRow)
            For iCol = 0 To DataGrid2.VisibleColumnCount
                stColumnHeader = DataGrid2.CaptionVisible.ToString & ": "
                stDisplayText = stDisplayText & stColumnHeader & DataGrid2.Item(iRow, iCol) & vbCrLf & vbCrLf
        End With 

But in this line "For iCol = 0 To DataGrid2.VisibleColumnCount" instead of DataGrid2.VisibleColumnCount I want the total column count any ideas?

Adodb.recordset Column Count?
I'm using asp to try to build a table dynamically when passed in a sql select string. The problem i'm having is that the table doesn't know how many columns to have. Is there a way to get a count of columns in the recordset so i could put that in a for next loop in my asp vbscript?


Loop To Count The Number Of Items In A Column

Please can you help. I have a spreadsheet containing a large set of data, and I want to be able to count the number of rows of data that I have using a VB script. ( I want to do this to reduce the amount of time that it takes to do calculations and vlookups etc).

Can anyone help please?

MS Excel Column Count Containing Mixed Data Type
Hi all

i wants to take Column Count containing Mixed Data Type ie Number & Text
in Ms Excel

1252 | 12512 | Raj |

Suppose there r 3 cols In 4 th Col i wants to take count of cols ie. should be 3

Raj - IDEA Cellular ltd

Column Count In EXCEL Having Mixed Data Type
Hi All

How do i Do Column count in MS Excel Which Contains Text as well as Numbers

125 | 250 | Raj

In Fourth Col I should get Count ie. 3

Thanks In Advance

Can Datacombo/dbcombo List Heigth/width/column Count Be Change?
ITtoolbox Portal for Visual Basic -


New to VB programming, coming from Access development environment.

Is it possible to format VB's datacombo like Access's combobox, ie rows
displayed, columns displayed, list widths?

Thanks much!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~

Odd Occurances
Anyone ever notice that some times, you know you have your code precisely right... and no matter what happens it Just won't WORK?

Then you copy the code into a text file, trash your form, make a new one and paste it back in and the damned thing works???

I hate that.

I realize VB's doing a lot of **** behind the scenes so we don't have to deal with it. I mean a simple text box could be like 5,000 lines of C code before we even see it, so that's not surprising.

VB just worries me some times when weird **** starts happening. Like with the MID$, LEFT$ and RIGHT$ functions that stopped working right in SP3. Or APP.PATH, when sometimes it puts the terminating "" at the end of the path and sometimes it doesn't.

I mean, what if the something like that ceases to function in production, then what???

On the up side... these little 'design features' will keep us in business too... cuz there'll always be something to fix..


Multiple OCX Occurances

With in the Project/Components menu I have multiple occurrances of a user control. I have unregistered them all with regsvr32, removed all related keys in the registry and deleted all the ocx's, but the references to them are still in the componets menu. Does any one know how to clear this list?

Several Occurances Of Same Client Application
I'm writing my first client-server application. I want users to be able to run several occurances (say up to 10) of the same client application. So now I'm trying to invent a good way to keep apart the UDP replies from the server. I'm thinking of using different port numbers, say port 55001 for the first occurance, 55002 for the second occurance etc... On the other hand I'm not sure of a simple way for a program to check whether another occurance is already running...

Another way would be to make the first occurance of the client application start a service or similar, which receives the replies from the server and passes each one on to the correct occurance... But I don't know how to create services, and it seems unnecessarily complicated...

What do you more experienced people think? How would you do it?

Counting How Many Occurances Of A Letter
I need to count the number of occurances of a particular letter occur in a string, IE

A is found 10 times in StrX
B is found 2 times in StrX

etc etc etc...

I'm pretty sure theres a basic command for this, I tried to use InStr, that doesnt work. Basicly I'm aiming to build a Frequency Analizer, Someone gave me a Code to Crack...

Number Of Occurances In A String
i have a string liek the following


I have to find the number of occurances of the word , and whether each word is a number or character like

hello: Varchar
world: varchar
15247 : number
Pls help me

Counting Occurances In A String
I have a string that contains text. How do I count the number of occurances of one string in the other.

e.g. How do I count how many times "word" is in "These are my word examples. My word on it."

Number Of Occurances In Array ?
Just wondering if anyone happens to have any code to hand to check for the number of occurances of each element in an array ?

So if :

a(0) = "a"
a(1) = "b"
a(2) = "c"
a(3) = "a"
a(4) = "d"

Then it would return "a" : 2, "b" : 1, "c" : 1 etc.

Find # Of Occurances In A String
if i want to find how many times say the letter "A" is in a string, how do i do that? thanks

Return The Occurances From A String

is there any function that returns the number of occurances of a character in a string.

i have a string that contains the character "form feed", chr(12), i want to know how many characters of chr(12) are in that string.

thank you very much!!!


Counting Occurances In A Database Field?
Hi there,

I have an access database, and i'd like to have my program count how many times a word appears in a particular field throughout the recordset. How do I go about doing this in VB6?



Strange VB 6.0 Systray Occurances... - Resolved
Well, I did a lot of searching on the forum, and I couldnt find anything similar to my experience so here goes....

I do much of my coding in VB 6.0 on a system running windows 2000 Professional, however sometimes I work on my projects on an XP machine... once i do that, the systray menu almost NEVER works again, which means I have to copy and paste the code and controls into a new project on a win2k machine to fix it, after doing that, everything works fine again.... this makes no sense to me as to why I would need to do this.. an explination, or maybe a better way to do this would be much appreciated


Code I use:

This goes in a module

VB Code:
'user defined type required by Shell_NotifyIcon API call      Public Type NOTIFYICONDATA       cbSize As Long       hwnd As Long       uId As Long       uFlags As Long       uCallBackMessage As Long       hIcon As Long       szTip As String * 64     End Type        'constants required by Shell_NotifyIcon API call:      Public Const NIM_ADD = &H0      Public Const NIM_MODIFY = &H1      Public Const NIM_DELETE = &H2      Public Const NIF_MESSAGE = &H1      Public Const NIF_ICON = &H2      Public Const NIF_TIP = &H4      Public Const WM_MOUSEMOVE = &H200      Public Const WM_LBUTTONDOWN = &H201     'Button down      Public Const WM_LBUTTONUP = &H202       'Button up      Public Const WM_LBUTTONDBLCLK = &H203   'Double-click      Public Const WM_RBUTTONDOWN = &H204     'Button down      Public Const WM_RBUTTONUP = &H205       'Button up      Public Const WM_RBUTTONDBLCLK = &H206   'Double-click       Public Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long      Public Declare Function Shell_NotifyIcon Lib "shell32.dll" Alias "Shell_NotifyIconA" _      (ByVal dwMessage As Long, pnid As NOTIFYICONDATA) As Boolean       Public nid As NOTIFYICONDATA

This is on Form_Load()

VB Code:
Me.Show       Me.Refresh       With nid        .cbSize = Len(nid)        .hwnd = Me.hwnd        .uId = vbNull        .uFlags = NIF_ICON Or NIF_TIP Or NIF_MESSAGE        .uCallBackMessage = WM_MOUSEMOVE        .hIcon = Me.Icon        .szTip = Form1.ProgVer & vbNullChar       End With       Shell_NotifyIcon NIM_ADD, nid

This is on Form_MouseMove

VB Code:
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) 'Minimize to tray script Dim Result As LongDim Msg As LongIf Me.ScaleMode = vbPixels Then    Msg = XElse    Msg = X / Screen.TwipsPerPixelXEnd IfSelect Case Msg    Case WM_LBUTTONUP        Me.Show        Me.WindowState = vbNormal        Result = SetForegroundWindow(Me.hwnd)    Case WM_LBUTTONDBLCLK        Me.WindowState = vbNormal        Result = SetForegroundWindow(Me.hwnd)    Case WM_RBUTTONUP        Result = SetForegroundWindow(Me.hwnd)        Me.PopupMenu Me.mPopupsysEnd SelectEnd Sub

I'm pretty sure I grabbed everything, if I missed anything let me know

Counting Number Of Occurances In File

I have a text file that I want VB to read and count the number of occurances of a particular word and then place that number in a varibable.

How can this be done?



Counting Occurances Of A String In A Text Document
I need to perform an operation like the find /c (count the total number of occurances of a specific string in a file) operation in the DOS prompt with the ability to place the result within a cell in MS Excel using a macro from within MS Excel.

Haven't been able to find a function that will allow me to do this.

Thanks for the help.


Finding Multiple Occurances Of Characters In A String
I'm trying to make a Hangman program for a project, and there are two main things that are stumping me so far. The first I'll have another go at and retreat back to the boards if it beats me

As for the second, I'm using this code to check if a letter is in the word they have to find:

strGuessL = frmGame.txtGuessL.Text 'Put the pleyers' guess into a variable
intAnswer = InStr(strTarget, strGuessL) 'Check if the answer is in the string

If intAnswer = "" Then 'If the answer box is blank, then..
frmGame.txtGuessL = "" 'Clear it (just to make sure) and
frmGame.txtGuessL.SetFocus 'Re-set the focus
ElseIf intAnswer <> 0 Then 'If there's something in it, and it's correct,
blnCorrect = True 'Set blnCorrect to true
Else: blnCorrect = False 'Otherwise, make it false
End If

The target word is in strTarget, strGuessL is where the letter that the person guessed is stored.

This works ok at the moment, but the problem is: When there is more than occurance of the letter in the string, how do I tell that?

E.g, in the word 'Soccer', there are two c's. How do I tell the user "There are two c's" as opposed to "There's at least one c"?.

Any help would be awesome

Filtering In A MSFlexGrid Table, And Finding The Number Of Occurances Of The Filtered Item
Hello all,

I am currently devoloping an application that retrieves data from an access database, and then outputs it onto an MSFlexGrid. The problem I have run into, is that I need to filter each column. I have the database setup on the MSFlexGrid. There a a total of 7 columns, and I need to filter each one with a different value. For example, Column 1 has all different brand names of computers. I need to filter out all brands except "sony", so only occurances of sony would show up in the column. How would I do this? Also I need to have a variable that would tell me how many "Sony's" are in the column, as I will not know how many there are. So far on my form I have a data control that contains the database, and that is linked to the MSFlexGrid. I can display the data on the MSFlexGrid, but I need to filter it like I mentioned above.

I really appreciate your help

Have a good night

Edited by - vbcorvette on 5/3/2003 8:41:24 PM

Workbooks.Count Doesn't Return Count
For some reason, Workbooks.Count is returning 0, although a workbook is open. I can force the error by calling it without a workbook open. The call has worked before on the same machine (win2k).

Anybody have any idea why this erratic behavior?


If Workbooks.Count > 0 Then MsgBox "open WBs = " & Workbooks.Count

Count TIFF Count Pages

Can anyone help me how to count pages of TIFF file. Up to now i dont have idea or clue pls help thanks.

Having My Program Count Up Without Wiping The Count Already
hi everyone. i have a kind of easy question.

ive gotten most of my forms to do this the way that i need. but im having problems with a few forms. what im trying to do is this. as a person enters a report # it is suppose to update the count on the main form for both the Total count and the count for that specific type of report.

now like i said i have this working when i do all the codeing from right there on the main form. but when i try it on this employee injury type of report i can get it to keep count like it should but what its doing is erasing the total count for all reports instead of adding to it.

the code that i have been useing for this is
counter = counter + 1
frmeom.lblfour.caption = counter + spark

the sPark is a variable holding the count amount that comes from an ini file.

i know this seems kind of hard to understand but i can post the code is you would like but its quite alot of code. let me know what would be better.thanks sirbum69

Character Count && Word Count
How Do I do this with a richtextbox and make it display either character or word count in another textbox

Thanks for your help,


Record Count With DAO - Stops At 501 Count
I am trying to get the record count of a table using DAO. When I click the button below to add records, I am refreshing the count in a caption. It works fine until I exceed 5001 records and it stops at 501. If I ave 3000 records in the table, it still says 501. If the table is empty, I can see the change by adding a few at a time and it increments. But stops at 501.

Any suggesitons?

Private Sub Command1_Click()
Dim db1 As Database
Set db1 = OpenDatabase("c:database.mdb")
db1.Execute "INSERT INTO Table2 SELECT field1, field2 FROM PR1 WHERE field1 = '" & DBList1.Text & "'"
Data3.RecordSource = "SELECT * FROM Table2"
Label3.Caption = "SELECTED CONTACTS: " & Data3.Recordset.RecordCount
End Sub


Please Help With Getting Part Of A String From A Big String, With Multiple Occurances
id="Span7">SOME DATA</span>

this appears numerous times in a string, with SOMEDATA being different each time..

how can u retrieve the SOME DATA from a string like that?

but there are multiple occurances...

blahblahid="Span7">SOME DATA A</span>blah blah blahid="Span7">SOME DATA B</span> blah blah blah
id="Span7">SOME DATA C</span>
id="Span7">SOME DATA D</span>blah

how can u return SOME DATA A, B, C and D?

Count Of Document In VB6 Mismatch With Word Count When Opend In Microsoft Word
Can anyone kindly help me.

I have written program in VB6 to take counts of Word document - Characters, lines, paragraphs, words. They are not matching when document opend in Microsoft word and seen word count (in tools menu). Also can any one guide me in knowing the command to get page count of word document in VB6. Thanks a lot in advance.


Edited by - sarikondak on 6/9/2005 8:34:36 PM

Copyright 2005-08, All rights reserved