Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range to
another.
The range is highlighted and than the code shows an input box for a cell
selection.

Most of the time it works beautifully. Other times it gives a type mismatch
on rnge and fails.

I don't understand why one selection works and another doesn't. With or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell - just
different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub
<<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond

First thing I'd do is:

Dim Rnge as Range

But I'm not sure how you're getting errors in the middle of
"on error resume next"



STEVE BELL wrote:

Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range to
another.
The range is highlighted and than the code shows an input box for a cell
selection.

Most of the time it works beautifully. Other times it gives a type mismatch
on rnge and fails.

I don't understand why one selection works and another doesn't. With or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell - just
different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub
<<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond

--

Dave Peterson

Your On Error should suppress the error. In the VBA Editor, go to
the Tools menu, choose Options then the General tab. There, in
the "Error Trapping" frame, ensure that "Break on all errors" is
NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one
range to another.
The range is highlighted and than the code shows an input box
for a cell selection.

Most of the time it works beautifully. Other times it gives a
type mismatch on rnge and fails.

I don't understand why one selection works and another doesn't.
With or without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook
from Personal.xls). All selections are similar in size and
starting cell - just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8)
<<< errors here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On Error goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to the
Tools menu, choose Options then the General tab. There, in the "Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range to
another.
The range is highlighted and than the code shows an input box for a cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't. With or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond

You'll get an error if you hit the cancel button on that dialog. Are you sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On Error goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to the
Tools menu, choose Options then the General tab. There, in the "Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range to
another.
The range is highlighted and than the code shows an input box for a cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't. With or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do - end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog. Are you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to the
Tools menu, choose Options then the General tab. There, in the "Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range
to
another.
The range is highlighted and than the code shows an input box for a
cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't. With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

Steve,

No guarantees on this one, but I seem to remember something similar.
The solution (maybe) was to either use all named arguments or
use all of the position commas (not a mix of both) so...
Application.InputBox("Select Cell", , , , , , , 8)

Regards,
Jim Cone
San Francisco, USA

And it still blows up if you "dim rnge as range"???

I couldn't break it.

After the inputbox is showing, can you be more specific about what the user
clicks on (or types???) to make it blow up?



STEVE BELL wrote:

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do - end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog. Are you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to the
Tools menu, choose Options then the General tab. There, in the "Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one range
to
another.
The range is highlighted and than the code shows an input box for a
cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't. With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<< errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

--

Dave Peterson

Jim and Dave - thanks for hanging in there with me on this supposedly simple
macro...

The problem might be related to the workbook and how it fits together. But
I have stepped through the code and can't detect anything else happening.
The code either recognizes the second cell as a range or it doesn't...

Jim - tried using your version of the code line and behavior is the same.

Dave:
User selects range with mouse
User clicks special toolbar button
Input box appears
User clicks on destination cell
range.address appears on Input box
either R5C6 or $F$5 (tried R1C1 and A1)
User clicks "OK" in Input box dialog box.

Either the paste happens or it doesn't.

This should be the same as
Select Range
Edit Copy
Select a single cell
Edit PasteSpecial Formulas

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
And it still blows up if you "dim rnge as range"???

I couldn't break it.

After the inputbox is showing, can you be more specific about what the
user
clicks on (or types???) to make it blow up?



STEVE BELL wrote:

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the
range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single
row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the
input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do -
end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog. Are
you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On
Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to
the
Tools menu, choose Options then the General tab. There, in the
"Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one
range
to
another.
The range is highlighted and than the code shows an input box for a
cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't.
With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting
cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<<
errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

--

Dave Peterson

I don't have another guess. I was hoping that something bad was happening when
the user was selecting the range.

If you copy just that little portion to another workbook (and declare all your
variables <bg), does it ever fail?

I just don't see the problem.

STEVE BELL wrote:

Jim and Dave - thanks for hanging in there with me on this supposedly simple
macro...

The problem might be related to the workbook and how it fits together. But
I have stepped through the code and can't detect anything else happening.
The code either recognizes the second cell as a range or it doesn't...

Jim - tried using your version of the code line and behavior is the same.

Dave:
User selects range with mouse
User clicks special toolbar button
Input box appears
User clicks on destination cell
range.address appears on Input box
either R5C6 or $F$5 (tried R1C1 and A1)
User clicks "OK" in Input box dialog box.

Either the paste happens or it doesn't.

This should be the same as
Select Range
Edit Copy
Select a single cell
Edit PasteSpecial Formulas

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
And it still blows up if you "dim rnge as range"???

I couldn't break it.

After the inputbox is showing, can you be more specific about what the
user
clicks on (or types???) to make it blow up?



STEVE BELL wrote:

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the
range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single
row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the
input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do -
end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog. Are
you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On
Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to
the
Tools menu, choose Options then the General tab. There, in the
"Error
Trapping" frame, ensure that "Break on all errors" is NOT selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one
range
to
another.
The range is highlighted and than the code shows an input box for a
cell
selection.

Most of the time it works beautifully. Other times it gives a type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't.
With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook from
Personal.xls). All selections are similar in size and starting
cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<<
errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave,

Your suspecion is probably right.
The code is in Personal.xls and is available for any workbook.

It only seems to have problems on one special workbook. But not on every
selection.

I have been looking for anything special about the offending selections but
have been unable to see anything.

The only problem is that when I step through the code there is no branching
to another code or anything like that. So I have no idea of what is
happening.

I just tried it and disabled macros in the offending workbook and still got
the problem.

It's starting to look light Twilight Zone time...

What makes it crazier is that it is a simple (silly) workbook that I use for
my own pleasure (weekly) and am ashamed to share it with anyone.

Thanks for trying...

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
I don't have another guess. I was hoping that something bad was happening
when
the user was selecting the range.

If you copy just that little portion to another workbook (and declare all
your
variables <bg), does it ever fail?

I just don't see the problem.

STEVE BELL wrote:

Jim and Dave - thanks for hanging in there with me on this supposedly
simple
macro...

The problem might be related to the workbook and how it fits together.
But
I have stepped through the code and can't detect anything else happening.
The code either recognizes the second cell as a range or it doesn't...

Jim - tried using your version of the code line and behavior is the same.

Dave:
User selects range with mouse
User clicks special toolbar button
Input box appears
User clicks on destination cell
range.address appears on Input box
either R5C6 or $F$5 (tried R1C1 and A1)
User clicks "OK" in Input box dialog box.

Either the paste happens or it doesn't.

This should be the same as
Select Range
Edit Copy
Select a single cell
Edit PasteSpecial Formulas

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
And it still blows up if you "dim rnge as range"???

I couldn't break it.

After the inputbox is showing, can you be more specific about what the
user
clicks on (or types???) to make it blow up?



STEVE BELL wrote:

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the
range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single
row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste
special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the
input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do -
end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything
to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog.
Are
you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On
Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to
the
Tools menu, choose Options then the General tab. There, in the
"Error
Trapping" frame, ensure that "Break on all errors" is NOT
selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one
range
to
another.
The range is highlighted and than the code shows an input box
for a
cell
selection.

Most of the time it works beautifully. Other times it gives a
type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't.
With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook
from
Personal.xls). All selections are similar in size and starting
cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<<
errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Silly guess #1. Maybe rebuilding that worksheet/workbook would help.

Silly guess #2. Maybe running Rob Bovey's code cleaner would help.

You can find it he
http://www.appspro.com/

STEVE BELL wrote:

Dave,

Your suspecion is probably right.
The code is in Personal.xls and is available for any workbook.

It only seems to have problems on one special workbook. But not on every
selection.

I have been looking for anything special about the offending selections but
have been unable to see anything.

The only problem is that when I step through the code there is no branching
to another code or anything like that. So I have no idea of what is
happening.

I just tried it and disabled macros in the offending workbook and still got
the problem.

It's starting to look light Twilight Zone time...

What makes it crazier is that it is a simple (silly) workbook that I use for
my own pleasure (weekly) and am ashamed to share it with anyone.

Thanks for trying...

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
I don't have another guess. I was hoping that something bad was happening
when
the user was selecting the range.

If you copy just that little portion to another workbook (and declare all
your
variables <bg), does it ever fail?

I just don't see the problem.

STEVE BELL wrote:

Jim and Dave - thanks for hanging in there with me on this supposedly
simple
macro...

The problem might be related to the workbook and how it fits together.
But
I have stepped through the code and can't detect anything else happening.
The code either recognizes the second cell as a range or it doesn't...

Jim - tried using your version of the code line and behavior is the same.

Dave:
User selects range with mouse
User clicks special toolbar button
Input box appears
User clicks on destination cell
range.address appears on Input box
either R5C6 or $F$5 (tried R1C1 and A1)
User clicks "OK" in Input box dialog box.

Either the paste happens or it doesn't.

This should be the same as
Select Range
Edit Copy
Select a single cell
Edit PasteSpecial Formulas

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
And it still blows up if you "dim rnge as range"???

I couldn't break it.

After the inputbox is showing, can you be more specific about what the
user
clicks on (or types???) to make it blow up?



STEVE BELL wrote:

Dave,

Thanks for taking the time.

The code was derived with recording and modification.
And is several years old. And it is very basic code.
Set range = selection
Paste special to a single cell choosen with an Input box.
Should be simple stuff...

I also borrowed from previous code found on this site - primarily the
range
test for nothing.

The way the code is supposed to work is:
User selects a range of cells (generally several cells on a single
row.
User clicks on toolbar button to activate the code.
The input box appears.
The user selects a single cell for the destination.
The code tests to see that the user has selected a range
If the code sees a range - the first selected range is paste
special
formulas to the new range.

Works most of the time. But with some rows it doesn't recognize the
input
box as a range or as an object (depending on my dim statement).

Originally put in the On Error stuff to do what it is supposed to do -
end
the code on error.

It is supposed to work just like:
Highlight a range
Edit Copy
Select a cell
Edit Paste Special = formulas

Beats the heck out of me...

And I look at formatting and sheet events and couldn't find anything
to
interfer.
Works great doing it manually. But not with the code.

Again - Thanks...
--
steveB

Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
You'll get an error if you hit the cancel button on that dialog.
Are
you
sure
you're selecting a range and hitting ok?

If you are, how about posting exactly what you did (step by step)?

STEVE BELL wrote:

Thanks for the prompt replies...

Forgot to mention that I only saw the errors when I got rid of On
Error
goto
0

Also - Dim rnge
Dim rnge as Range
without - got type error.
with - got Object required error.

Yet when I select different rows it might work.

--
steveB

Remove "AYN" from email to respond
"Chip Pearson" wrote in message
...
Your On Error should suppress the error. In the VBA Editor, go to
the
Tools menu, choose Options then the General tab. There, in the
"Error
Trapping" frame, ensure that "Break on all errors" is NOT
selected.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"STEVE BELL" wrote in message
news:gvLjf.21778$KZ2.74@trnddc05...
Thanks in advance for any and all help.

I have been using the following code to paste formulas from one
range
to
another.
The range is highlighted and than the code shows an input box
for a
cell
selection.

Most of the time it works beautifully. Other times it gives a
type
mismatch on rnge and fails.

I don't understand why one selection works and another doesn't.
With
or
without Events enabled.
A1 or R1C1 reference. (I primarily use this in one workbook
from
Personal.xls). All selections are similar in size and starting
cell -
just different rows.

Using in XL 2k & XL 2003

Dim rnge
Set rnge = Nothing
Application.ScreenUpdating = True
On Error Resume Next
Set rnge = Application.InputBox("Select Cell", Type:=8) <<<
errors
here
On Error GoTo 0
Application.ScreenUpdating = False
If rnge Is Nothing Then
Exit Sub <<< Exits here on error
End If

Selection.Copy
rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas

Again - Thanks...
--
steveB

Remove "AYN" from email to respond




--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson