Counting Cells
 

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'

Enter and run this macro:

Sub Macro1()
Dim r As Range
Dim count As Long
Cells.Select
For Each r In Selection
If r.Value = "end" Then
Exit For
Else
count = count + 1
End If
Next
MsgBox (count)
End Sub
--
Gary''s Student


"dave" wrote:

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'

Thanks for that, if i wanted to be more specific in what i count, do i just
changed the Cells.Select statement ?

"Gary''s Student" wrote:

Enter and run this macro:

Sub Macro1()
Dim r As Range
Dim count As Long
Cells.Select
For Each r In Selection
If r.Value = "end" Then
Exit For
Else
count = count + 1
End If
Next
MsgBox (count)
End Sub
--
Gary''s Student


"dave" wrote:

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'

The Cells.select statement selects all the cells in the worksheet. This is
the range that is being examined. Note that, as coded, we start in A1 and go
across rows and then down, counting as we go.

If B2 contains "end", the routine will return 257
--
Gary's Student


"dave" wrote:

Thanks for that, if i wanted to be more specific in what i count, do i just
changed the Cells.Select statement ?

"Gary''s Student" wrote:

Enter and run this macro:

Sub Macro1()
Dim r As Range
Dim count As Long
Cells.Select
For Each r In Selection
If r.Value = "end" Then
Exit For
Else
count = count + 1
End If
Next
MsgBox (count)
End Sub
--
Gary''s Student


"dave" wrote:

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'

How would i get it to populate a cell with the total instead on getting it to
appear in a message box ?

"Gary''s Student" wrote:

The Cells.select statement selects all the cells in the worksheet. This is
the range that is being examined. Note that, as coded, we start in A1 and go
across rows and then down, counting as we go.

If B2 contains "end", the routine will return 257
--
Gary's Student


"dave" wrote:

Thanks for that, if i wanted to be more specific in what i count, do i just
changed the Cells.Select statement ?

"Gary''s Student" wrote:

Enter and run this macro:

Sub Macro1()
Dim r As Range
Dim count As Long
Cells.Select
For Each r In Selection
If r.Value = "end" Then
Exit For
Else
count = count + 1
End If
Next
MsgBox (count)
End Sub
--
Gary''s Student


"dave" wrote:

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'

You could replace the Msgbox with a
Cells(1,1).Value = count
to put the result in A1, for example.
--
Gary's Student


"dave" wrote:

How would i get it to populate a cell with the total instead on getting it to
appear in a message box ?

"Gary''s Student" wrote:

The Cells.select statement selects all the cells in the worksheet. This is
the range that is being examined. Note that, as coded, we start in A1 and go
across rows and then down, counting as we go.

If B2 contains "end", the routine will return 257
--
Gary's Student


"dave" wrote:

Thanks for that, if i wanted to be more specific in what i count, do i just
changed the Cells.Select statement ?

"Gary''s Student" wrote:

Enter and run this macro:

Sub Macro1()
Dim r As Range
Dim count As Long
Cells.Select
For Each r In Selection
If r.Value = "end" Then
Exit For
Else
count = count + 1
End If
Next
MsgBox (count)
End Sub
--
Gary''s Student


"dave" wrote:

I need to count the number of cells in a worksheet until it reaches a
specific value.
i.e count the number of cells until it reaches a cell containing the word
'end'