Type mismatch using rnge as Range with Type 8 Input Box

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" <[email protected]> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > 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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >> > 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" <[email protected]> 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 here:
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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> >> >> >> news:%[email protected]...
> >> >> >> > 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" <[email protected]> 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