How about...
Dim myStr As String
myStr = "CountA('Sheet1:Sheet" & Sheets.Count - 2 & "'!A13:A100)"
range("I18").value = Evaluate(myStr)
I don't think you can use a 3D reference like this in VBA. (Well, I couldn't!)
(You could loop throught the worksheets to achieve the same effect, though.)
Mike Fogleman wrote:
I am trying to put a calculated value in a cell without putting the formula
in the cell. VB will calculate and put result.
The syntax is killing me. What is it for this:
Range("I18").Value =
Application.WorksheetFunction.CountA('Sheet1:Sheet (Sheets.Count-2)'!A13:A100)
Mike F
--
Dave Peterson