|
Fill Down Macro
I am working to create a Macro that will look at a specific column and fill
down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
I'm sorry - can you put the specific formula in one section. This one is not
working. Where is the beginning and the end? I'm confused... "Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
It keeps saying either expected End With or Expected End Sub
"Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
You did NOT give me a complete macro. All I did was take what you sent and
modify it. Surely, you can figure out how to substitute. You should always post ALL of your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... It keeps saying either expected End With or Expected End Sub "Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
Actually, Mr. Guillett, I do not know how to substitute. I was not aware
that I needed to be an Excel expert to post a question on this blog. I am just learning about macros on my own and this site has been very helpful when I have had questions. I feel the tone of your response was not appropriate or helpful. I didn't think I need to post my entire macro when I was only have trouble with this part, but I will be sure to do this next time. Please refrain from such rude responses in the future as yours contained a tone that was not appreciated. Thank you. "Don Guillett" wrote: You did NOT give me a complete macro. All I did was take what you sent and modify it. Surely, you can figure out how to substitute. You should always post ALL of your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... It keeps saying either expected End With or Expected End Sub "Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
Umm, this is a public newsgroup. It's not a blog, a forum, or any other
private pay for media. Any and all replies are the sole opinion of the poster and should be taken as that. If you are that sensitive to Don's statements, then you may wish to undertake to acquire further training from a local source. Lost in Excel wrote: Actually, Mr. Guillett, I do not know how to substitute. I was not aware that I needed to be an Excel expert to post a question on this blog. I am just learning about macros on my own and this site has been very helpful when I have had questions. I feel the tone of your response was not appropriate or helpful. I didn't think I need to post my entire macro when I was only have trouble with this part, but I will be sure to do this next time. Please refrain from such rude responses in the future as yours contained a tone that was not appreciated. Thank you. "Don Guillett" wrote: You did NOT give me a complete macro. All I did was take what you sent and modify it. Surely, you can figure out how to substitute. You should always post ALL of your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... It keeps saying either expected End With or Expected End Sub "Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
Fill Down Macro
All Subs must have an End Sub at the end..........your original Sub should
have had one. Don used a With statement in his revision of your piece of posted code. Every With needs an End With What we don't know is whether or not you will be operating any further on the Range("A9") or should you place the End With after you have substituted Don's revised code. Seeing all your original code would assist in that. Gord Dibben MS Excel MVP On Mon, 8 Dec 2008 12:43:01 -0800, Lost in Excel wrote: Actually, Mr. Guillett, I do not know how to substitute. I was not aware that I needed to be an Excel expert to post a question on this blog. I am just learning about macros on my own and this site has been very helpful when I have had questions. I feel the tone of your response was not appropriate or helpful. I didn't think I need to post my entire macro when I was only have trouble with this part, but I will be sure to do this next time. Please refrain from such rude responses in the future as yours contained a tone that was not appreciated. Thank you. "Don Guillett" wrote: You did NOT give me a complete macro. All I did was take what you sent and modify it. Surely, you can figure out how to substitute. You should always post ALL of your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... It keeps saying either expected End With or Expected End Sub "Don Guillett" wrote: Bottom = [D9].End(xlDown).Row with Range("a9", Range("a9").SpecialCells(xlLastCell)) .Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .NumberFormat = "General" end with Range("D9").AutoFill Destination:=Range("D9:D & Bottom") 'why copy and then change?? Range("D9:D" & Bottom).FormulaR1C1 = "=RC[3]-RC[1]" -- Don Guillett Microsoft MVP Excel SalesAid Software "Lost in Excel" wrote in message ... I am working to create a Macro that will look at a specific column and fill down a formula. The Macro is working properly to identify the column, but it is not working properly at the end due to an error. My formula is Bottom = [D9].End(xlDown).Row Range("A9").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.NumberFormat = "General" Range("D9").Select Selection.AutoFill Destination:=Range("D9:D & Bottom").Select (ERROR HERE) Range("D9:D" & Bottom).Select ActiveCell.FormulaR1C1 = "=RC[3]-RC[1]" The error is indicated above. Does anyone know why this formula won't fill down the column? I only need it is fill in where there is data. Thanks! |
| All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com