Compile error: Object required

saupton

New Member
Joined
Aug 13, 2014
Messages
4
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! :biggrin:
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
Thank you so much for such a quick response! I really appreciate it! :biggrin: 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.
 
Upvote 0
sorry, was meant to look like this:

Code:
fnd = CStr(Selection)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top