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
"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 <JLGW...@discussions.microsoft.com> 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 -
>
>
|
Similar Threads
