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!

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!

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!

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!

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!

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!

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!

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!