Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare cells and copy columns after match | Excel Worksheet Functions | |||
Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
How to compare two columns of data to make sure they match | Excel Worksheet Functions | |||
Compare text in 2 separate spreadsheets, when match found display | Excel Worksheet Functions | |||
Compare columns, delete the rows that DO match. | Excel Programming |