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

[VBA]
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").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

[/VBA]

[mancubus]

hi.

at first glance:
[vba]
Set searchRange = Range("A1:A227")
[/vba]

wrong data type declaration for fCellValue
[vba]
Dim fCellVal As Range

fCellVal = foundCell.Value
[/vba]

[mikerickson]

The problem with the line [VBA]searchCell = x.Value[/VBA] 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:

[vba]
Sub ccopyy()

Dim Rng As Range, Cll As Range, srcRng As Range

With Worksheets("SEA0611")
Set Rng = .Range("D1" & 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
[/vba]

[mikerickson]

Perhaps this non-loop will do what you want
[VBA]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[/VBA]