Input box Type:= 8 not working
One problem was this:
Range(rnge).Select
Rnge is already a range. So you might have been able to use:
rnge.Select
But selecting ranges can cause other problems. You can only select a range on
the activesheet--if the use selected a range from a different sheet (or
different sheet in a different workbook), then that line would blow up real good
<vbg.
And it's usually a good idea to check to see if the user hit the cancel
button--if they did, then your rnge variable would be nothing (and then you'd
have different trouble).
And I used this:
Rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas
I wanted to pick the first cell of the range the user selected. And then excel
could resize that paste to match the selected range.
And one more thing, say your user had A1:B2 selected. Ran the macro and
selected IV1 as that range to paste. One more error (not enough room on the
worksheet to actually paste the selection's formulas).
You might want this little addition:
Option Explicit
Sub testme01()
Dim Rnge As Range
Application.ScreenUpdating = True
Set Rnge = Nothing
On Error Resume Next
Set Rnge = Application.InputBox("Select Cell", Type:=8)
On Error GoTo 0
Application.ScreenUpdating = False
If Rnge Is Nothing Then
'user hit cancel
Exit Sub '???
End If
Selection.Copy
On Error Resume Next
Rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas
If Err.Number < 0 Then
MsgBox "Error when pasting formulas"
Err.Clear
End If
With Application
.ScreenUpdating = True
.CutCopyMode = False
End With
End Sub
This would show the msgbox when the pastespecial fails for other reasons,
too--the worksheet being protected.
STEVE BELL wrote:
Dave,
Thank you very much! It now works like I had hoped for - it will now
copy/paster a multirow ranged.
I'm not really sure why my code didn't work.
Added su = t
rnge = nothing
to my code and it still failed.
--
steveB
Remove "AYN" from email to respond
"Dave Peterson" wrote in message
...
Do you turn off screenupdating before you try to get that range? Turn it
back
on (just temporarily)--and turn it off after you get the range:
Option Explicit
Sub testme01()
Dim Rnge As Range
Application.ScreenUpdating = True
Set Rnge = Nothing
On Error Resume Next
Set Rnge = Application.InputBox("Select Cell", Type:=8)
On Error GoTo 0
Application.ScreenUpdating = False
If Rnge Is Nothing Then
'user hit cancel
Exit Sub '???
End If
Selection.Copy
Rnge.Cells(1).PasteSpecial Paste:=xlPasteFormulas
Application.ScreenUpdating = True
End Sub
STEVE BELL wrote:
Thanks guys,
here's the code
Set rnge = Application.InputBox("Select Cell", Type:=8)
Selection.Copy
Range(rnge).Select
Selection.PasteSpecial Paste:=xlFormulas
I am just trying to select a small range on a row and have it pasted to
another range. The input box helps select the start cell for the paste.
But now I can't even select a cell when the input box comes up...
(it worked before)
Am running Excel 2k, and show R1C1 instead of A1.
--
steveB
Remove "AYN" from email to respond
"Tom Ogilvy" wrote in message
...
get your ranges, then copy and paste in one command.
--
Regards,
Tom Ogilvy
"STEVE BELL" wrote in message
news:zny3f.45133$HM1.37868@trnddc04...
Thanks in advance for any help on this.
I had built code to define a range
Set rnge = Application.InputBox("Select Cell", Type:=8)
and either one of 2 things would occur
1. Unable to select cell on worksheet
2. rnge not defined ("empty")
This was working in Excel 2000 when I copied a single (row) range and
went
to select a cell to paste into. Than it started acting up when I
copied
more than 2 rows, and selected a single (from the Input Box) cell to
paste
to.
Has my Excel been corrupted or am I missing something...
Thanks...
--
steveB
Remove "AYN" from email to respond
--
Dave Peterson
--
Dave Peterson
|