Compile error: Object required

[saupton]

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!

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:

'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!

 

[dmt32]

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

try changing this line:

Set fnd = CStr(Selection.Copy.Value)

to this:

fnd = CStr(Selection.Copy.Value)

and see if solves your problem

Dave

 

[saupton]

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.

 

[dmt32]

sorry, was meant to look like this:

fnd = CStr(Selection)

 

[saupton]

Still no luck unfortunately.

 

[Rick Rothstein]

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.

 

[saupton]

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