If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Another Macro Question
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! |
#2
|
|||
|
|||
Another Macro Question
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 "igotquestions" wrote: 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! |
#3
|
|||
|
|||
Another Macro Question
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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#4
|
|||
|
|||
Another Macro Question
I own this
http://www.amazon.com/Excel-2003-Pow...29765&sr= 1-2 -- Regards, Peo Sjoblom "Mike H" wrote in message ... 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#5
|
|||
|
|||
Another Macro Question
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" wrote: 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#6
|
|||
|
|||
Another Macro Question
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" wrote: 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" wrote: 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#7
|
|||
|
|||
Another Macro Question
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? "Mike H" wrote: 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" wrote: 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" wrote: 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#8
|
|||
|
|||
Another Macro Question
NM...I had dates in column p. After I created a new column P everything
worked. Thanks again Mike!! Have a great weekend "igotquestions" wrote: 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? "Mike H" wrote: 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" wrote: 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" wrote: 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
#9
|
|||
|
|||
Another Macro Question
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 On Fri, 8 Aug 2008 13:26:02 -0700, igotquestions wrote: 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! |
#10
|
|||
|
|||
Another Macro Question
I'm glad that worked
"igotquestions" wrote: NM...I had dates in column p. After I created a new column P everything worked. Thanks again Mike!! Have a great weekend "igotquestions" wrote: 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? "Mike H" wrote: 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" wrote: 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" wrote: 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 "Mike H" wrote: 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 "igotquestions" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|