Object Variable Not Set

[Giri]

Hi Everyone,

I have been trying to make some code to find any words that are in the second column of this spreadsheet but not in the first and then copy them into column G.

Here's what I have so far. I haven't been able to really test if this work s because I am getting an error in the line:

"searchCell = x.Value" where I get a run-time error 91 - Object or With variable not set.

What is the significance of using "Set" when declaring something??

Finally, how do you do Shift + Select + Down(Right,Left or Up) in VBA?

PS: If this looks familiar, it was a worksheet posted by another user earlier.

Thank you,

Giri

Option Explicit

Public Sub Giri()
    Dim x As Range
    Dim Total As Integer
    Dim searchRange As Range
    Dim foundCell As Range
    Dim fCellVal As Range
    Dim searchCell As Range
    Worksheets("SEA0611").Range("D:D").Select
    For Each x In Selection.Cells
        searchCell = x.Value
        searchRange = Range("A1:A227")
        Set foundCell = searchRange.Find(searchCell)
        fCellVal = foundCell.Value
        If Not StrComp(searchCell, fCellVal, vbTextCompare) = 0 Then
            Range("G1").Select
            If Not ActiveCell.Value = "" Then
                ActiveCell.Offset(1, 0).Select
                searchCell.Paste
            Else
                ActiveCell.Select
                searchCell.Paste
            End If
        End If
    Next x
End Sub

[mancubus]

hi.

at first glance:

Set searchRange = Range("A1:A227")

wrong data type declaration for fCellValue

Dim fCellVal As Range
fCellVal = foundCell.Value

[mikerickson]

The problem with the line

searchCell = x.Value

is that both searchCell and x are ranges and the .Value of a range is never a range.

If you are trying to set the range searchCell to be the same range as x, this would be the syntax

Set searchCell = x

[mancubus]

i would use stg like this:

Sub copyy()
    Dim Rng As Range, Cll As Range, srcRng As Range
    With Worksheets("SEA0611")
        Set Rng = .Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        Set srcRng = .Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    End With
    For Each Cll In Rng
        If WorksheetFunction.CountIf(srcRng, Cll.Value) > 0 Then
            Cll.Copy Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
        End If
    Next
End Sub

[mikerickson]

Perhaps this non-loop will do what you want

Sub test()
    With Sheet1.Range("B:B")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With .Offset(0, 5)
                .FormulaR1C1 = "=IF(COUNTIF(C1,RC2)=0,RC2, """")"
                .Value = .Value
                On Error Resume Next
                .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
                On Error GoTo 0
            End With
        End With
    End With
End Sub