Vasant's approach can also be implemented in VBA if you must do this as part
of a macro.

With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),.Range("A1"),.Colu mns(2))
End with

where cell A1 contains the number you are looking for, but that could be in
any cell. For example Z22

With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),.Range("Z22"),.Col umns(2))
End with

or just in a variable

num = 32
With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),num,.Columns(2))
End with

--
Regards,
Tom Ogilvy



"Incidental" wrote:

Hi

Not entirely sure what you are asking for but i have decided to go
with for each of the used cells in column A if the cell contains a
number then take the number in column B for that row add it to a
running total that will show at the end???
The code below will do just that though if it's not what you are
looking for it should at least give you somewhere to start.

Option Explicit
Dim MyCell, MyRng As Range
Dim SumTotal As Integer
Dim LstRow As Integer

Private Sub CommandButton1_Click()

SumTotal = 0

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

For Each MyCell In MyRng

If IsNumeric(MyCell.Value) = True Then

SumTotal = SumTotal + MyCell.Offset(0, 1).Value

End If

Next MyCell

MsgBox SumTotal

End Sub

hope this is of some help

S