I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

CountIf is a Worksheetfunction, and you need to define the range properly.

Sub counting()

Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = WorksheetFunction.CountIf _
(Range("U3", ActiveCell.Offset(-1, 0)), "Equity")
End Sub


Alternatively, another way to do this, since you are looping anyway:

Sub counting()
xCount = 0
Range("U3").Activate
Do Until ActiveCell = ""
If ActiveCell.Value = "Equity" then
xCount = xCount +1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = xCount
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

You dont need to loop...Try the below...

Sub counting()
Dim lngRow As Long
lngRow = Cells(Rows.Count, "U").End(xlUp).Row
Range("U" & lngRow + 1) = "=COUNTIF(U3:U" & lngRow & ",""Equity"")"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

Try this:

Countif "Mark Roll" = Subset(0.0);
Then "Squirrel Master" = "Get back to work"

"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

hi
if all you are trying to do is put the value of the count in last cell,
this might be a better way
Sub countit()
Dim c As Long
c = 0
Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "Equity" Then
c = c + 1
End If
Loop
ActiveCell.Value = c
end sub

regards
FSt1

"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

that worked great.

do you know what is wrong with this. I am trying to sum the four cells
above my activecell

ActiveCell.Value = "Total" & " - " & Application.WorksheetFunction.Sum _
Range(Acivecell.Offset(-4, 0), ActiveCell.Offset(-1, 0)))

I am getting a syntax error


"Luke M" wrote:

CountIf is a Worksheetfunction, and you need to define the range properly.

Sub counting()

Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = WorksheetFunction.CountIf _
(Range("U3", ActiveCell.Offset(-1, 0)), "Equity")
End Sub


Alternatively, another way to do this, since you are looping anyway:

Sub counting()
xCount = 0
Range("U3").Activate
Do Until ActiveCell = ""
If ActiveCell.Value = "Equity" then
xCount = xCount +1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = xCount
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

Typo on the 2nd line. Change "Acivecell" to "ActiveCell".
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

that worked great.

do you know what is wrong with this. I am trying to sum the four cells
above my activecell

ActiveCell.Value = "Total" & " - " & Application.WorksheetFunction.Sum _
Range(Acivecell.Offset(-4, 0), ActiveCell.Offset(-1, 0)))

I am getting a syntax error


"Luke M" wrote:

CountIf is a Worksheetfunction, and you need to define the range properly.

Sub counting()

Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = WorksheetFunction.CountIf _
(Range("U3", ActiveCell.Offset(-1, 0)), "Equity")
End Sub


Alternatively, another way to do this, since you are looping anyway:

Sub counting()
xCount = 0
Range("U3").Activate
Do Until ActiveCell = ""
If ActiveCell.Value = "Equity" then
xCount = xCount +1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = xCount
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

Couple of typos in your posted code

Try this version.................

ActiveCell.Value = "Total" & " - " & Application.WorksheetFunction.Sum _
(Range(ActiveCell.Offset(-4, 0), ActiveCell.Offset(-1, 0)))


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 11:43:01 -0700, cluckers
wrote:

that worked great.

do you know what is wrong with this. I am trying to sum the four cells
above my activecell

ActiveCell.Value = "Total" & " - " & Application.WorksheetFunction.Sum _
Range(Acivecell.Offset(-4, 0), ActiveCell.Offset(-1, 0)))

I am getting a syntax error


"Luke M" wrote:

CountIf is a Worksheetfunction, and you need to define the range properly.

Sub counting()

Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = WorksheetFunction.CountIf _
(Range("U3", ActiveCell.Offset(-1, 0)), "Equity")
End Sub


Alternatively, another way to do this, since you are looping anyway:

Sub counting()
xCount = 0
Range("U3").Activate
Do Until ActiveCell = ""
If ActiveCell.Value = "Equity" then
xCount = xCount +1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = xCount
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers

Thanks

dah

"Luke M" wrote:

Typo on the 2nd line. Change "Acivecell" to "ActiveCell".
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

that worked great.

do you know what is wrong with this. I am trying to sum the four cells
above my activecell

ActiveCell.Value = "Total" & " - " & Application.WorksheetFunction.Sum _
Range(Acivecell.Offset(-4, 0), ActiveCell.Offset(-1, 0)))

I am getting a syntax error


"Luke M" wrote:

CountIf is a Worksheetfunction, and you need to define the range properly.

Sub counting()

Range("U3").Activate
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = WorksheetFunction.CountIf _
(Range("U3", ActiveCell.Offset(-1, 0)), "Equity")
End Sub


Alternatively, another way to do this, since you are looping anyway:

Sub counting()
xCount = 0
Range("U3").Activate
Do Until ActiveCell = ""
If ActiveCell.Value = "Equity" then
xCount = xCount +1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = xCount
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to count the number of occurences that the word "equity" appears
in a range. The range is always going to be column U. However, U3 will
always be the first cell in the range but the last cell varies depending on
the data. So I am running a loop to get to the bottom of the range. Once
there I want to run a count command that will count the number of times
"equity" appears in the column.

I am getting an error on my "countif" part that says sub or function not
defined. Not sure if I am even using this correctly. Any ideas?


Sub counting()

Range("U3").Activate

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = CountIf("U3, ActiveCell.Offset(-1, 0)", "Equity")

End Sub


Thanks

Cluckers