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