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

Post your code.

Best wishes Harald

"STEVE BELL" skrev i melding
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

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

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

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,

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

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