Formula using VBA

[Angeliki]

Hello all

I have a problem with one formula..I want to create a formula into one cell.
However i would like to be recalculated when i change the data (last row of
the data is different every time)

Sub rate_click()
Range("J4").Select
With ActiveCell
Set lastrow =
Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,
"A").End(xlUp).Row
.Formula = Sum(Range("J19" & lastrow)) / Sum(Range("C19" & lastrow))
Range("J4").Select
End With

End Sub

it does not recognise the function sum!!

Can you please help me why this macro does not work.

Thanks in advance

Regards
angeliki

 

[Werner Flamme]

Angeliki schrieb am 24.10.2003 11:11:

Hello all

I have a problem with one formula..I want to create a formula into one cell.
However i would like to be recalculated when i change the data (last row of
the data is different every time)

Sub rate_click()
Range("J4").Select
With ActiveCell
Set lastrow =
Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,
"A").End(xlUp).Row
.Formula = Sum(Range("J19" & lastrow)) / Sum(Range("C19" & lastrow))
Range("J4").Select
End With

End Sub

it does not recognise the function sum!!

Can you please help me why this macro does not work.

Thanks in advance

Regards
angeliki

If you want to use a formula from a sheet in VB, you should use
"Application." in front of the formula, e g.
Application.Sum(Range("J19:J26"))

When putting a formula into a cell, you use e. g.
ActiveCell.Formula = "=sum(J19:J26)"
what works fine with me ;-)

 

[Angeliki]

Werner thanks for your immediate reply
I add in front of the formula (application) and

if i specified the range e.g (J19:J36) it works for me too. But i would like
to sum from J19 up to lastrow which i define as

Set lastrow
=Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,"A").End(xlUp)
..Row
Do you recognise any mistake in my code?

Thanks in advance
Angeliki

 

[Harald Staff]

Set lastrow
=Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,"A").End(xlUp).Row
Do you recognise any mistake in my code?

That would return the last cell in A column. Try J:

=Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,"J").End(xlUp).Row

or preferrably the simpler

=Worksheets("Pricing").Cells(65536, "J").End(xlUp).Row

 

[Tom Ogilvy]

Or the more flexible:

Worksheets("Pricing").Cells(Rows.Count,"J").End(xlUp).Row

As long as a worksheet will be active. (all worksheets have the same number
or rows). More flexible because it will work with xl95 and earlier as well
(which may not be a consideration).

or

With Worksheets("Pricing")
lastRow = .Cells(.Rows.count,10).End(xlup).Row
End With

 

[Harald Staff]

Or the more flexible:

Worksheets("Pricing").Cells(Rows.Count,"J").End(xlUp).Row

As long as a worksheet will be active. (all worksheets have the same number
or rows). More flexible because it will work with xl95 and earlier as well
(which may not be a consideration).

Right, of course, sorry. The 60k limit is spoiling me.

Best wishes Harald
Followup to newsgroup only please