Determining if a value exists in a column

[MikeLiberty]

Apologies if this has been addressed elsewhere, I couldn't find it.

I'm trying to determine if a value in a named range in one worksheet exists in column A of another worksheet. If it does, I want to set up a warning message, if not, I need to copy and paste some data. I had most of it working, but when I tried to limit the search to 1 column instead of the entire sheet, I broke something. Its been driving me crazy. Any help is appreciated. The relevant code is below.

Dim LastMonth As Boolean

LastMonth = Workbooks("MOR Workbook.xls").Worksheets("AWP12mo").Range("A:A").Find(What:=Worksheets("DataRetention").Range("RetentionDate").Value, _
After:= Range("A1"), LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


If LastMonth = True Then ..... {Rest of Code}

 

[VoG]

Try

Dim LastMonth As Range

Set LastMonth = Workbooks("MOR Workbook.xls").Worksheets("AWP12mo").Range("A:A").Find(What:=Worksheets("DataRetention").Range("RetentionDate").Value, _
After:=Range("A1"), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)


If Not LastMonth Is Nothing Then

 

[MikeLiberty]

Try

Dim LastMonth As Range
 
Set LastMonth = Workbooks("MOR Workbook.xls").Worksheets("AWP12mo").Range("A:A").Find(What:=Worksheets("DataRetention").Range("RetentionDate").Value, _
After:=Range("A1"), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
 
 
If Not LastMonth Is Nothing Then

Thanks, that got me through the find. However, LastMonth is always registering as Nothing now, so it will always do the copy and paste. Any Idea why?

 

[VoG]

I changed the Lookat and Lookin parameters. Perhaps you need to set them back to what you had.

 

[MikeLiberty]

Perfect, thanks for the help.

Trying to learn each piece of a function or method is brutal.