Hey Norman,

It was an error on my part. Thanks for you help!

"Norman Jones" wrote:

Hi Mike,

It dosn't seem to be working though. I am not getting a returned value,
however if I do a countif on the actual sheet then i do get a value
returned... any thoughts?

It works in my test book. Did you perhaps drop the period (full stop) that
prepends Columns(2) in the following line:

i = Application.CountIf(.Columns(2), nStuff).


---
Regards,
Norman



"Michael A" wrote in message
...
Thanks Norman,

It dosn't seem to be working though. I am not getting a returned value,
however if I do a countif on the actual sheet then i do get a value
returned... any thoughts?



"Norman Jones" wrote:

Hi Michael,

There is no need to insert a formula and subsequently delete it. It is
also
rarely necessary to activate sheets.

Try, instead:

'<<=============
Sub MAM()
Dim nStuff As String
Dim ThisSheet As String
Dim i As Long

ThisSheet = ActiveSheet.Name

nStuff = InputBox _
("What date is this for? I.E 1/1/2006,1/5/2006 etc.")

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\server2\mam.xls"

With Worksheets("Jan 06")
'Now working in the MAM Sheet
i = Application.CountIf(.Columns(2), nStuff)
End With

Workbooks("January Stats.xls"). _
Worksheets(ThisSheet).Range("B44").Value = i

ActiveWorkbook.Close False
Application.ScreenUpdating = True

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


---
Regards,
Norman