Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Compile error: Object required

  1. #1
    New Member
    Join Date
    Aug 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Compile error: Object required

    Hello everyone! I am a new VBA coder and I'm trying to figure out this error. Basically, I am trying to create a macro that takes the value of one entry in the column, switches sheets and searches for that value, then copies the data in the columns next to it and pastes them back on the first sheet next to the original values. And then have it continue to the end of all the entries in the column. This is the code that I have right now. For some reason, I keep getting this error and I really don't know why! Any help would be appreciated, thank you!

    Code:
    Sub LookupAndPaste()
          Dim x As Integer
          Dim fnd As String
          NumRows = Range("C2", Range("C2").End(xlDown)).Rows.Count
          Range("C2").Select
          For x = 1 To NumRows
            ActiveCell.Offset(1, 0).Select
            Set fnd = CStr(Selection.Copy.Value)
            ActiveWorkbook.Sheets(2).Activate
    
    
            Do Until cell Is Nothing
                Columns("F:F").Select
                Set cell = Selection.Find(what:=fnd, after:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)
    
    
                If cell Is Nothing Then
                    'do something
                Else
                    cell.Select
    
    
                End If
            Loop
            Next
    End Sub


    And here, I've commented out some of the other things that I have tried:

    Code:
    'What value do you want to find (must be in string form)?
    '  fnd = CStr(Selection.Value)
    '
    'Set myRange = ActiveSheet.UsedRange
    'Set LastCell = myRange.Cells(myRange.Cells.Count)
    'Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
    '
    ''Test to see if anything was found
    '  If Not FoundCell Is Nothing Then
    '    FirstFound = FoundCell.Address
    '  Else
    '    GoTo NothingFound
    '  End If
    '
    'Set rng = FoundCell
    '
    ''Loop until cycled through all unique finds
    '  Do Until FoundCell Is Nothing
    '    'Find next cell with fnd value
    '      Set FoundCell = myRange.FindNext(after:=FoundCell)
    '
    '    'Add found cell to rng range variable
    '      Set rng = Union(rng, FoundCell)
    '
    '    'Test to see if cycled through to first found cell
    '      If FoundCell.Address = FirstFound Then Exit Do
    '
    '  Loop
    '
    ''Select Cells Containing Find Value
    '  rng.Select
    '
    'Exit Sub
    '
    ''Error Handler
    'NothingFound:
    '  MsgBox "No values were found in this worksheet"
    '        ' Application.Dialogs(xlDialogFormulaFind).Show
    '        ' SelectRange.Paste
    '
    '        ' Dim Sh As Worksheet
    '        ' Dim Loc As Range
    '
    '
    '        ' With Sh.UsedRange
    '        ' For Each Sh In ActiveWorkbook.Sheets(2)
    '            ' Set Loc = Cells.Find(What:=Selection)
    '                ' If Not Loc Is Nothing Then
    '                    ' Do Until Loc Is Nothing
    '                        ' Loc.Value = "Working?"
    '                        ' Set Loc = Cells.FindNext(Loc)
    '                    ' Loop
    '                ' End If
    '          ' End With
    '    ' Set Loc = Nothing
    '        ' Next

    Thank you all so much for the help!

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,404
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compile error: Object required

    Hi,
    you are trying to assign an object reference to a variable you have declared as a string.

    try changing this line:

    Code:
    Set fnd = CStr(Selection.Copy.Value)
    to this:

    Code:
    fnd = CStr(Selection.Copy.Value)

    and see if solves your problem

    Dave

  3. #3
    New Member
    Join Date
    Aug 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile error: Object required

    Thank you so much for such a quick response! I really appreciate it! Unfortunately, this is still giving me the same error, although it might be a different instance of that error! This one doesn't look like it's a compiler error.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,404
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compile error: Object required

    sorry, was meant to look like this:

    Code:
    fnd = CStr(Selection)

  5. #5
    New Member
    Join Date
    Aug 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile error: Object required

    Still no luck unfortunately.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,225
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    22 Thread(s)

    Default Re: Compile error: Object required

    Some comments (in addition to dtm32's suggestion from Message #4)....

    1) I would change the declaration for the 'x' variable from Integer to Long (in case there ever are more than 32767 rows of data).

    2) I would add a Dim statement for the NumRows variable... make it a Long as well.

    3) You should add a Dim statement for the Cell variable... make it a Range.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    New Member
    Join Date
    Aug 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile error: Object required

    Perfect! Thank you so much! That fixed the error.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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