Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 26th 08, 05:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 59
Default 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 -




  #2   Report Post  
Old February 26th 08, 08:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,986
Default Compare columns in two WBks and when match is found...

Revised Code, adds a line to create last row variable and
replaces the row reference in original line nine with the
variable, creating a dynamic range.

Sub CheckData()
Dim c As Range
Dim findC As Variant
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUP).Row
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Windows("Report.xls").Activate '<-- My report
For Each c In ActiveSheet.Range("A2:A" & lastRow)
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

The following snippet will search the source doc for the
value of each cell in column of the target document and
if a match is found, will copy column B value of source
doc to column I of the target doc on the same row as the
item being queried. Assuming Report.xls is the target
workbook and sheet1 is the active sheet in both
workbooks. It the assumtion is incorrect then change
accordingly. Also assumes there will be only one occurence
of any item in either workbook's sheet1.

Dim c As Range
Dim dt As Range
lastRow = Workbooks("Report.xls").Sheets(1) _
.Cells(Rows.Count, 1).End(xlUP).Row
lstRow2 = Workbooks("SourceDoc.xls").Sheets(1) _
.Cells(Rows.Count, 1).End(xlUp).Row
fRng = Workbooks("Report.xls").Sheets(1)
sRng = Workbooks("SourceDoc.xls")
For Each c In fRng.Range("A2:A" & lastRow)
If Not c Is Nothing Then
For Each dt In sRng.Sheets(1).Range("A2:A" & lstRow2)
If Not dt Is Nothing Then
If dt.Value = c.Value Then
dt.Offset(0 1).Copy fRng.Range("I" & c.Row)
Exit For
End If
End If
Next
End If
Next

I did not know where you wanted to insert this, or if you wanted to
insert it, in the other code, so I will let you decide that. I did
not test it so if you get errors post back. Eliminate any duplicate
statements like Dim c and lastRow declarations when you insert.

"DDawson" wrote:

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 -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare cells and copy columns after match Kcope8302 Excel Worksheet Functions 2 August 5th 09 05:37 PM
Need to match 2 columns, if a match found add info from 2nd column Stratton Excel Worksheet Functions 1 October 8th 08 02:55 PM
How to compare two columns of data to make sure they match Len Excel Worksheet Functions 0 June 24th 08 04:07 PM
Compare text in 2 separate spreadsheets, when match found display cocoblue Excel Worksheet Functions 3 May 20th 08 11:57 PM
Compare columns, delete the rows that DO match. ryguy7272 Excel Programming 2 May 20th 07 04:34 PM


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017