Formula using VBA

  • Thread starter Thread starter Angeliki
  • Start date Start date
A

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
 
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
Click to expand...

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 ;-)
 
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
 
Angeliki said:
Set lastrow
=Worksheets("Pricing").Cells(Worksheets("Pricing").Rows.Count,"A").End(xlUp).Row
Do you recognise any mistake in my code?
Click to expand...

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
 
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
 
Tom Ogilvy said:
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).
Click to expand...

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

Best wishes Harald
Followup to newsgroup only please
 
Back
Top