Type mismatch using rnge as Range with Type 8 Input Box

  • Thread starter Thread starter STEVE BELL
  • Start date Start date
S

STEVE BELL

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...
 
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"
 
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
 
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.
 
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)?
 
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...
 
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?
 
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
 
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 said:
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
Click to expand...
 
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...
 
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 said:
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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Input box Type:= 8 not working 6
Sub that has an input of an array of ranges... what's wrong? 3
Array coding type mismatch 6
Error 13 Type Mismatch 4
Type Mismatch 0
Why type mismatch? 3
Type Mismatch? 3
Type mismatch error 1

Back
Top