Formula using VBA
 

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

Formula using VBA
 

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 ;-)

Formula using VBA
 

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

"Werner Flamme" wrote in message
...
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 ;-)

Formula using VBA
 

"Angeliki" wrote in message
...
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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

Formula using VBA
 

Or the more flexible:

Worksheets("Pricing").Cells(Rows.Count,"J").End(xl Up).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

--
Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
"Angeliki" wrote in message
...
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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

Formula using VBA
 

"Tom Ogilvy" skrev i melding
...
Or the more flexible:

Worksheets("Pricing").Cells(Rows.Count,"J").End(xl Up).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