what went wrong?!
 

Hello people,

I'm new to VBA writing... and am trying to do a simple function, bu
it's proving rather complex. I'm wanting the macro to count the numbe
of "C" in the column A, and out put the value in a message box.

Below is what I have got so far, I dont get any error messages and th
msgbox pops up, but there are no values in the box..

I hope some one can give me some help.


Thank you!
Simon

Sub CommandButton1_Click()


Dim colA As Range
Dim emplCount As Integer
Set colA = ActiveSheet.Range("A:A")
Dim total_Employees

For Each cell In colA
If cell = "C" = True Then
emplCount = emplCount + 1
End If

Next
emplCount = total_Employees

MsgBox total_Employees & "Number of employees"

End su

--
simon1081
-----------------------------------------------------------------------
simon10810's Profile: http://www.excelforum.com/member.php...fo&userid=3531
View this thread: http://www.excelforum.com/showthread.php?threadid=55092

what went wrong?!
 

Hi Simon,

Try the following minor adaptation of your code:

'=============
Private Sub CommandButton1_Click()
Dim colA As Range
Dim emplCount As Integer
Dim total_Employees
Dim cell As Range

With ActiveSheet
Set colA = Intersect(.Range("A:A"), .UsedRange)
End With

For Each cell In colA
If cell.Value = "C" Then
emplCount = emplCount + 1
End If
Next

MsgBox "Number of employees = " & emplCount

End Sub
'<<=============


---
Regards,
Norman



"simon10810" wrote
in message ...

Hello people,

I'm new to VBA writing... and am trying to do a simple function, but
it's proving rather complex. I'm wanting the macro to count the number
of "C" in the column A, and out put the value in a message box.

Below is what I have got so far, I dont get any error messages and the
msgbox pops up, but there are no values in the box..

I hope some one can give me some help.


Thank you!
Simon

Sub CommandButton1_Click()


Dim colA As Range
Dim emplCount As Integer
Set colA = ActiveSheet.Range("A:A")
Dim total_Employees

For Each cell In colA
If cell = "C" = True Then
emplCount = emplCount + 1
End If

Next
emplCount = total_Employees

MsgBox total_Employees & "Number of employees"

End sub


--
simon10810
------------------------------------------------------------------------
simon10810's Profile:
http://www.excelforum.com/member.php...o&userid=35315
View this thread: http://www.excelforum.com/showthread...hreadid=550924

what went wrong?!
 

Hi Simon,

You are close:

Sub CommandButton1_Click()

Dim colA As Range
Dim cellA as Range
Dim emplCount As Integer
Set colA = ActiveSheet.Range("A:A")

For Each cellA In colA
If cellA.Value = "C" = True Then
emplCount = emplCount + 1
End If
Next

MsgBox emplCount & " Number of employees"


End Sub

HTH,

Executor


simon10810 wrote:
Hello people,

I'm new to VBA writing... and am trying to do a simple function, but
it's proving rather complex. I'm wanting the macro to count the number
of "C" in the column A, and out put the value in a message box.

Below is what I have got so far, I dont get any error messages and the
msgbox pops up, but there are no values in the box..

I hope some one can give me some help.


Thank you!
Simon

Sub CommandButton1_Click()


Dim colA As Range
Dim emplCount As Integer
Set colA = ActiveSheet.Range("A:A")
Dim total_Employees

For Each cell In colA
If cell = "C" = True Then
emplCount = emplCount + 1
End If

Next
emplCount = total_Employees

MsgBox total_Employees & "Number of employees"

End sub


--
simon10810
------------------------------------------------------------------------
simon10810's Profile: http://www.excelforum.com/member.php...o&userid=35315
View this thread: http://www.excelforum.com/showthread...hreadid=550924