I declared both a workbook and range and couldnt get my
formula to calculate non text for both the workbook and
range. The reason i declared the workbook is because i
wanted the formula to appear in another book. Thanks in
advance



''''''''''''''''''''''''''''''''''Macro''''''''''' '''''''
Dim IDS As Range
Dim Fname As String


Fname = ActiveWorkbook.Name

ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlDown)).Name = "IDS"

ActiveWindow.ActivateNext


ActiveCell.FormulaR1C1 = "=COUNT(FNAME&IDS)"

'''This comes out as described above im stumped

'''is it an indirect?

you could try:

ActiveCell.FormulaR1C1 = "=COUNT(" & "'" & FNAME & "'!IDS)"

I think I'd declare a range and use that directly. I don't like using
activecell--it moves around too much.

dim IDSrng as range
with activesheet
set IDSrng = .range("c9",.range("c9").end(xldown))
'or whatever you need???
end with

ActiveCell.FormulaR1C1 = "=COUNT(" & IDSrng.address(external:=true) & ")"




Jaime wrote:

I declared both a workbook and range and couldnt get my
formula to calculate non text for both the workbook and
range. The reason i declared the workbook is because i
wanted the formula to appear in another book. Thanks in
advance

''''''''''''''''''''''''''''''''''Macro''''''''''' '''''''
Dim IDS As Range
Dim Fname As String

Fname = ActiveWorkbook.Name

ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlDown)).Name = "IDS"

ActiveWindow.ActivateNext

ActiveCell.FormulaR1C1 = "=COUNT(FNAME&IDS)"

'''This comes out as described above im stumped

'''is it an indirect?

--

Dave Peterson

Change that last .formular1c1 to .formula:

ActiveCell.Formula = "=COUNT(" & IDSrng.Address(external:=True) & ")"

oopsie.

Dave Peterson wrote:

you could try:

ActiveCell.FormulaR1C1 = "=COUNT(" & "'" & FNAME & "'!IDS)"

I think I'd declare a range and use that directly. I don't like using
activecell--it moves around too much.

dim IDSrng as range
with activesheet
set IDSrng = .range("c9",.range("c9").end(xldown))
'or whatever you need???
end with

ActiveCell.FormulaR1C1 = "=COUNT(" & IDSrng.address(external:=true) & ")"

Jaime wrote:

I declared both a workbook and range and couldnt get my
formula to calculate non text for both the workbook and
range. The reason i declared the workbook is because i
wanted the formula to appear in another book. Thanks in
advance

''''''''''''''''''''''''''''''''''Macro''''''''''' '''''''
Dim IDS As Range
Dim Fname As String

Fname = ActiveWorkbook.Name

ActiveCell.Offset(0, 7).Select
Range(Selection, Selection.End(xlDown)).Name = "IDS"

ActiveWindow.ActivateNext

ActiveCell.FormulaR1C1 = "=COUNT(FNAME&IDS)"

'''This comes out as described above im stumped

'''is it an indirect?

--

Dave Peterson


--

Dave Peterson