|
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
Type mismatch using rnge as Range with Type 8 Input Box
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 |
| All times are GMT +1. The time now is 07:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com