Compare columns in two WBks and when match is found...
Eliano and JLG Whiz both kindly contributed to the enclosed macro which
checks the value of each cell in column A of Targetdoc against the values in
Sourcedoc xlValues and when a match is found, it enters a textstring into the
corresponding cell in Column "I".
I originally used today's date as the text string, but since Sourcedoc has a
"Date Created" column, it makes sense to insert this value rather than a
textstring into the Targetdoc when the match is found.
How do check the value of each cell in Column A of Targetdoc against the
values in column A in Sourcedoc...
And when a match is found...
Enter the value of column B (of the found row), of Sourcedoc into...
Column I (of the checkvalue row), of Targetdoc?
Also, how do I amend the ninth line of the Macro so that ActiveSheet.Range
expands as more data is added?
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
"eliano" wrote:
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
On 19 Feb, 19:55, JLGWhiz wrote:
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
"DDawson" wrote:
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?- Nascondi testo tra virgolette -
- Mostra testo tra virgolette -
|