Try this

myVal = Application.CountIf(Range(Selection, Selection.End(xlToRight)),
agentid)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"auntie spam" wrote in message
...
hi all
i have a problem in counting a value within a selected range of cells
the value i do not know beforehand but the first instance of this value is
allways at a certain cell away from the selected cell
i want to count the number of times that this value appears within a range
and
then use this within a loop. this is the variable that i named agentId


this range is start and finish times each day for a week plus two cells
(16 cells) at the moment i am counting all the cells and then dividing
this
but
this value the agent id value does no appear all the time so this creates
an
error

i have at the moment

Range(Selection, Selection.End(xlToRight)).Select
numfull = Selection.Count

'want to get rid of this line and count the instances of agentId
agentIdCount = (numfull / 8) / 2

'selecting agentID
ActiveCell.Offset(0, 15).Range("A1").Select

'here i get the value that i am loking for
agentid = Selection.Value

'selecting week to cut
ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut

For clindex = 2 To agentIdCount + 1

'back home then down 1
Range("A1").Select
'expression.Offset(RowOffset, ColumnOffset)
Cells(clindex, 1).Select
''ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste
Cells.Find(What:=agentid, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate

ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut
'Selection.Copy

Next clindex

thanks for the quick relpy
sorry forgot to add my name
will let you know how i get on

thanks

kevin

"Bob Phillips" wrote in message
...
Try this

myVal = Application.CountIf(Range(Selection,
Selection.End(xlToRight)),
agentid)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"auntie spam" wrote in message
...
hi all
i have a problem in counting a value within a selected range of cells
the value i do not know beforehand but the first instance of this value
is
allways at a certain cell away from the selected cell
i want to count the number of times that this value appears within a
range
and
then use this within a loop. this is the variable that i named agentId


this range is start and finish times each day for a week plus two cells
(16 cells) at the moment i am counting all the cells and then dividing
this
but
this value the agent id value does no appear all the time so this
creates
an
error

i have at the moment

Range(Selection, Selection.End(xlToRight)).Select
numfull = Selection.Count

'want to get rid of this line and count the instances of agentId
agentIdCount = (numfull / 8) / 2

'selecting agentID
ActiveCell.Offset(0, 15).Range("A1").Select

'here i get the value that i am loking for
agentid = Selection.Value

'selecting week to cut
ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut

For clindex = 2 To agentIdCount + 1

'back home then down 1
Range("A1").Select
'expression.Offset(RowOffset, ColumnOffset)
Cells(clindex, 1).Select
''ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste
Cells.Find(What:=agentid, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False).Activate

ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut
'Selection.Copy

Next clindex

your an absolute genius!

been trying allsorts of if and for loops without any luck

its surprising how one small line of code can give so much pleasure and
satisfaction

thanks once again

kevin


"Bob Phillips" wrote in message
...
Try this

myVal = Application.CountIf(Range(Selection,
Selection.End(xlToRight)),
agentid)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"auntie spam" wrote in message
...
hi all
i have a problem in counting a value within a selected range of cells
the value i do not know beforehand but the first instance of this value
is
allways at a certain cell away from the selected cell
i want to count the number of times that this value appears within a
range
and
then use this within a loop. this is the variable that i named agentId


this range is start and finish times each day for a week plus two cells
(16 cells) at the moment i am counting all the cells and then dividing
this
but
this value the agent id value does no appear all the time so this
creates
an
error

i have at the moment

Range(Selection, Selection.End(xlToRight)).Select
numfull = Selection.Count

'want to get rid of this line and count the instances of agentId
agentIdCount = (numfull / 8) / 2

'selecting agentID
ActiveCell.Offset(0, 15).Range("A1").Select

'here i get the value that i am loking for
agentid = Selection.Value

'selecting week to cut
ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut

For clindex = 2 To agentIdCount + 1

'back home then down 1
Range("A1").Select
'expression.Offset(RowOffset, ColumnOffset)
Cells(clindex, 1).Select
''ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste
Cells.Find(What:=agentid, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False).Activate

ActiveCell.Offset(0, 0).Range("A1:P1").Select
Selection.Cut
'Selection.Copy

Next clindex