good morning,

i've this problem.

i need a method to count the number og elemnts i get in a column, let
say column A and use this number to set the range for my formulas and
loop.

i tried this:

dim h as integer

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-12]=0,0,1)"
Selection.AutoFill destination:=Range("M2:M15000")
Range("M15001").Select
h = ActiveCell.FormulaR1C1 = "=SUM(R[-14999]C:R[-1]C)"

(whit this formula, in column M, i set 1 if there is an value in A else
0. then h is the sum on all the values in Column M.)

and then

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-3]"
Range("G2").Select
Selection.AutoFill destination:=Range(.Cells(7, 2), .Cells(7, h))

or also

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-3]"
Range("G2").Select
Selection.AutoFill destination:=Range("g2:gh")

but it does not work.

what i'm wrong?


thank's in advance



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

If you just want the static count:

Sub GetCount()
Dim rng As Range, vVal As Variant
Dim icnt As Long
Set rng = Range("A1").Resize(15000, 1)
vVal = rng.Value
icnt = 0
For i = 1 To 15000
If IsNumeric(vVal(i, 1)) Then
If vVal(i, 1) < 0 And Not IsEmpty(vVal(i, 1)) Then
icnt = icnt + 1
End If
End If
Next
Range("H1").Value = icnt

End Sub


Not sure what the G2 stuff is you are doing.

--
Regards,
Tom Ogilvy



"ferrdav" wrote in message
...
good morning,

i've this problem.

i need a method to count the number og elemnts i get in a column, let
say column A and use this number to set the range for my formulas and
loop.

i tried this:

dim h as integer

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-12]=0,0,1)"
Selection.AutoFill destination:=Range("M2:M15000")
Range("M15001").Select
h = ActiveCell.FormulaR1C1 = "=SUM(R[-14999]C:R[-1]C)"

(whit this formula, in column M, i set 1 if there is an value in A else
0. then h is the sum on all the values in Column M.)

and then

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-3]"
Range("G2").Select
Selection.AutoFill destination:=Range(.Cells(7, 2), .Cells(7, h))

or also

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*RC[-3]"
Range("G2").Select
Selection.AutoFill destination:=Range("g2:gh")

but it does not work.

what i'm wrong?


thank's in advance



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

thank's tom for your help.

but my problem is a bit different: once ai get icnt (h in my
formulation), how can i use in Range object?

for example: if i write range("B1:Bh") this does not work.

what i would like is to set the range in order to let him fit the
number of rows are in my worksheet (maybe there is anathor method).



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))

now rng has a reference to the used range of Column A (ignoring interior
cells which may be blank).

If i wanted to work with the same range in column G for instance

set rngG = rng.offset(0,7)
set rngB = rng.offset(0,1)

or

just to get the last row

lastrow = Cells(rows.count,1).End(xlup)
Range("B1:B" & lastRow)
or
Range("B1").Resize(lastrow,1) '< only good if starting from B1

--
Regards,
Tom Ogilvy




"ferrdav" wrote in message
...
thank's tom for your help.

but my problem is a bit different: once ai get icnt (h in my
formulation), how can i use in Range object?

for example: if i write range("B1:Bh") this does not work.

what i would like is to set the range in order to let him fit the
number of rows are in my worksheet (maybe there is anathor method).



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Not sure what you are trying to do, but
instead of: Selection.AutoFill Destination:=Range("g2:gh")
try: Selection.AutoFill Destination:=Range("g2:g" & h)

HTH,
Merjet

thank's a lot. problem solved!!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/