Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Paste Code (Excel2000)

    When using the mouse to copy and paste between two sheets--OK-- works the way it should. However, when I tried to write a Macro --Error Message 424 Object Required---partial code below

    Dim SelectCell As Variant
    Dim Range1 As Range
    Sheets("FinalSort").Select
    SelectCell=InputBox("Enter Starting Cell")
    Debug.Print SelectCell
    Set Range1=Range(SelectCell,SelectCell.Offset(7,4)
    Debug.Print Range1

    When I put the cursor over Set Range 1 I get Range1 = nothing
    When I put the cursor over SelectCell I get ="SelectCell =B7385.The second SelectCell doesn't do anything
    I thought when I Set Range1 an object was created?

    Paul B

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste Code (Excel2000)

    The result of InputBox is a string, not a range. One workaround is as follows:

    Dim SelectCell As String
    Dim Range1 As Range
    Sheets("Blad6").Select
    SelectCell = InputBox("Enter Starting Cell")
    Debug.Print SelectCell
    Set Range1 = Range(Range(SelectCell), Range(SelectCell).Offset(7, 4))
    Debug.Print Range1.Address

    Another workaround is to use the Application.InputBox function. This has an extra argument Type, if you use 8 you can enter/select a range.

    Dim SelectCell As Range
    Dim Range1 As Range
    Sheets("Blad6").Select
    Set SelectCell = Application.InputBox(Prompt:="Enter Starting Cell", Type:=8)
    Debug.Print SelectCell.Address
    Set Range1 = Range(SelectCell, SelectCell.Offset(7, 4))
    Debug.Print Range1.Address

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Code (Excel2000)

    Hans, The Application Input Box function did the trick.

    Thanks again,

    Paul B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •