Compare the contents of a cell with a column in another workbook

[DDawson]

I want to compare the contents of a cells A2:A3000 with a column (or defined
name) in another workbook. And, if a match is found, I want to enter a
comment in a cell on the same row, column G of the first workbook?

 

[JLGWhiz]

This is untested:

Sub IsItThere()
For Each c In ActiveSheet.Range("A2:A3000")
If Not c Is Nothing Then
Set findC = Worksheets(2).Cells _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
ActiveSheet.Range("G" & c.Row).AddComment _
.Visible = False _
.Text = "Found it"
End If
End If
Next
End Sub

 

[eliano]

Hi JLG.

Tested, but my xl2003 require a With....End With to work properly.
However many thanks.

Sub IsItThere() 'by JLGWitz
Dim c As Range
Dim findC As Variant

For Each c In ActiveSheet.Range("A2:A3000")
If Not c Is Nothing Then
Set findC = Worksheets(2).Cells _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
'---
With ActiveSheet.Range("G" & c.Row).AddComment '<-----
.Visible = False
.Text "Found it" '<-----
End With
'---
End If
End If
Next
End Sub

Regards
Eliano

 

[DDawson]

Thank you JLG and Eliano,

Here is the amended macro I used to enter a "comment" (it enters a value to
the cell rather than in a comment box adjacent) as text in each cell of my
Report where each cell in column A contains a value that appeared in my
SourceDoc(Sheet1). I Hope this makes sense.

Ideally it checks column B of the SourceDoc against column A of the Report
and if a matching entry is found, it enters the date in Column A of the
report, on the same row as the check value.

Sub CheckData()
Dim c As Range
Dim findC As Variant

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Windows("Report.xls").Activate '<-- My report
For Each c In ActiveSheet.Range("A2:A90")
If Not c Is Nothing Then
Windows("SourceDoc.xls").Activate '<-- My document to be checked
Set findC = Worksheets("Sheet1").Cells _
.Find(c.Value, LookIn:=xlValues)

If Not findC Is Nothing Then

Windows("Report.xls").Activate
ActiveSheet.Range("I" & c.Row).Cells.Value = "20/02/2008"
End If
End If
Next
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Kind Regards
Dylan Dawson