Another Macro Question

[igotquestions]

First of all, what would be a good book to get to help a new user with
learning how to create macros for Excel? The Dummies books don't seem to help
with the more advanced functions.

Next I want to apply a formula to a range of cells in a column. I have
successfully done this but my range of cells is o4330. The problem is that
it puts 0 in all the cells below the cell with the last value in it.

The scenario I am dealing with is this:
I need to apply the formula =sum(cell*.75) to only the cells with values in
them in column o starting with o4 (o4 is the starting cell for all of my
values)

Special Thanks again to Don and Mike for their help with my previous question!

 

[Mike H]

Maybe,

Right click the sheet tab, view code and paste this in

Sub marine()
lr = Cells(Rows.Count, "O").End(xlUp).Row
Set myrange = Range("O4:O" & lr)
For Each r In myrange
If Not IsEmpty(r) And IsNumeric(r) Then
r.Offset(, 1).Formula = "=" & r.Address & "*.75"
End If
Next
End Sub

Mike

 

[Mike H]

With regard to the good book then my vote is for John Walkenbach the Excell
2003 bible but you could do worse than monitor these groups. Some of the
stuff that get posted here is superb.

Mike

 

[Peo Sjoblom]

I own this

http://www.amazon.com/Excel-2003-Po...bs_sr_2?ie=UTF8&s=books&qid=1218229765&sr=1-2

--


Regards,


Peo Sjoblom

 

[igotquestions]

I will check on that book and yes there is alot of information here as well.

I copied that code like you said but it didn't work when I ran it. It didn't
appear to do anything.

 

[Mike H]

Hi,

The code looks at the used range of Colmn O from O4 down and if there is a
numeric value in the cell it puts the form =$O$n*.75 in column P wher n is
the row number. If it isn't doing that then you have the code in the wrong
place, it's worksheet code or the values in Column O aren't numeric.

Mike

 

[igotquestions]

From o4 down is all dollar amounts. There are a few empty cells in column o
where the report separates my parts by product groups. The sheet is named
rptPartstoPG and is titled that way from the access report I run to give me
that page. Would any of this be the reason it isn't working?

 

[igotquestions]

NM...I had dates in column p. After I created a new column P everything
worked. Thanks again Mike!! Have a great weekend

 

[Gord Dibben]

Do you need the formula =cell * .75....no SUM necessary BTW?

What is address(es) of "cell"?

How about just do the math on filled cells in column O without a formula?

Sub test()
Dim rng As Range
Dim rng2 As Range
Set rng2 = Range(Range("O4"), Cells(Rows.Count, "O").End(xlUp))
For Each rng In rng2
If rng <> "" Then
With rng
.Value = .Value * 0.75
End With
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

 

[Mike H]

I'm glad that worked

NM...I had dates in column p. After I created a new column P everything
worked. Thanks again Mike!! Have a great weekend