macro required..
Hi All,
I have excel file named as A.xls & B.xls I get A.xls in morning and B.xls in Evening.( day 1 ) Once i get B.xls I need to compare this workbook with A.xls ie., Col A has some codes in all workbooks that will repeated some times. So, If i know which one is repeated i will prioritize the work. Here is an example, A.xls Col A ABC45 IND45 AUD45 CZK47 B.xls Col A ABC47 IND45 AUD48 AUD46 CZK47 IN45 I have created tiny macro .. pls see below please create two workbook named as a.xls & b.xls Col A put some letter or numbers in both ( a.xls & b.xls - Sheet 2 ) which starts from A2 and end at A10 ( this is an example ) in b.xls put this macro and run it from b.xls ..... Sub matcher() Dim str As String Range("B2").Select a = ActiveWorkbook.Name Application.Dialogs(xlDialogOpen).Show (str) b = ActiveWorkbook.Name Workbooks(a).Activate Do Until ActiveCell.Offset(0, -1).Value = "" ActiveCell.FormulaR1C1 = _ "=IF(RC[-1]=[a.xls]Sheet2!R2C1:R10C1,""repeated"","""")" ActiveCell.Offset(1, 0).Select Loop ActiveCell.Offset(1, 0).Select Range("B2:B10").Select Selection.Copy Range("b2").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A1").Select End Sub this is running fine only when i know the file name and sheet name ... this file name and sheet name changes from day to day. As I inserted file name and sheet name in formula. Tomorrow I will receive C.xls then I need to compare with B.xls if i received D.xls then I will compare with C.xls...... goes on ,,,,,, |
macro required..
Try this
Sub matcher() Dim str As String Set BkASht = ThisWorkbook.ActiveSheet Application.Dialogs(xlDialogOpen).Show (str) Set BKB = ActiveWorkbook Set BkBSht = BKB.ActiveSheet With BkASht RowCount = 2 Do While .Range("A" & RowCount) = "" Data = .Range("A" & RowCount) Set c = BkBSht.Columns("A").Find(what:=Data, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then .Range("B" & RowCount) = "repeated" End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Hi All, I have excel file named as A.xls & B.xls I get A.xls in morning and B.xls in Evening.( day 1 ) Once i get B.xls I need to compare this workbook with A.xls ie., Col A has some codes in all workbooks that will repeated some times. So, If i know which one is repeated i will prioritize the work. Here is an example, A.xls Col A ABC45 IND45 AUD45 CZK47 B.xls Col A ABC47 IND45 AUD48 AUD46 CZK47 IN45 I have created tiny macro .. pls see below please create two workbook named as a.xls & b.xls Col A put some letter or numbers in both ( a.xls & b.xls - Sheet 2 ) which starts from A2 and end at A10 ( this is an example ) in b.xls put this macro and run it from b.xls ..... Sub matcher() Dim str As String Range("B2").Select a = ActiveWorkbook.Name Application.Dialogs(xlDialogOpen).Show (str) b = ActiveWorkbook.Name Workbooks(a).Activate Do Until ActiveCell.Offset(0, -1).Value = "" ActiveCell.FormulaR1C1 = _ "=IF(RC[-1]=[a.xls]Sheet2!R2C1:R10C1,""repeated"","""")" ActiveCell.Offset(1, 0).Select Loop ActiveCell.Offset(1, 0).Select Range("B2:B10").Select Selection.Copy Range("b2").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A1").Select End Sub this is running fine only when i know the file name and sheet name ... this file name and sheet name changes from day to day. As I inserted file name and sheet name in formula. Tomorrow I will receive C.xls then I need to compare with B.xls if i received D.xls then I will compare with C.xls...... goes on ,,,,,, |
All times are GMT +1. The time now is 04:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com