Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check - Similar Row contents - Different Sheets
I'm looking for a macro which would check ifcontents of Row 2 of Sheet2, Sheet4, Sheet6, and Sheet 9 are the same as Row 2 of sheet1 and a message box appearing would show in which sheets the rows are different. Can anybody help Thxs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check - Similar Row contents - Different Sheets
the entire contents or the content of specific cells? (all 256 cells in
each row?) You say similar in the subject and the same in the body. -- Regards, Tom Ogilvy "al007" wrote in message oups.com... I'm looking for a macro which would check ifcontents of Row 2 of Sheet2, Sheet4, Sheet6, and Sheet 9 are the same as Row 2 of sheet1 and a message box appearing would show in which sheets the rows are different. Can anybody help Thxs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check - Similar Row contents - Different Sheets
Not necessarily all 256 cells - you can take Column A to Z( i WILL
MODIFY ACCORDINGLY) - actual the reason behind that is that I want to check that the data in all sheets are under the same header so that I can sum across sheet to get a total.Thxs a lot for your help - waiting impatiently for your suggestion. I'm sure you'll enrich my excel again... THXS tHXS Tom Ogilvy wrote: the entire contents or the content of specific cells? (all 256 cells in each row?) You say similar in the subject and the same in the body. -- Regards, Tom Ogilvy "al007" wrote in message oups.com... I'm looking for a macro which would check ifcontents of Row 2 of Sheet2, Sheet4, Sheet6, and Sheet 9 are the same as Row 2 of sheet1 and a message box appearing would show in which sheets the rows are different. Can anybody help Thxs |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check - Similar Row contents - Different Sheets
Sub CheckRow2()
Dim v, v1 Dim i As Long, k As Long, bMisMatch As Boolean Dim rng As Range, cell As Range, sStr As String v = Array("Sheet2", "Sheet4", "Sheet6", "Sheet9") v1 = Worksheets("Sheet1").Range("A2:IV2") For i = LBound(v) To UBound(v) Set rng = Worksheets(v(i)).Rows(2).Cells k = 0 bMisMatch = False For Each cell In rng k = k + 1 If cell.Value < v1(1, k) Then bMisMatch = True Exit For End If Next If bMisMatch Then sStr = sStr & v(i) & ", " End If Next If Len(sStr) 3 Then MsgBox "Sheets not matching: " & vbNewLine & _ Left(sStr, Len(sStr) - 2) Else MsgBox "Good to go" End If End Sub -- Regards, Tom Ogilvy "al007" wrote in message oups.com... Not necessarily all 256 cells - you can take Column A to Z( i WILL MODIFY ACCORDINGLY) - actual the reason behind that is that I want to check that the data in all sheets are under the same header so that I can sum across sheet to get a total.Thxs a lot for your help - waiting impatiently for your suggestion. I'm sure you'll enrich my excel again... THXS tHXS Tom Ogilvy wrote: the entire contents or the content of specific cells? (all 256 cells in each row?) You say similar in the subject and the same in the body. -- Regards, Tom Ogilvy "al007" wrote in message oups.com... I'm looking for a macro which would check ifcontents of Row 2 of Sheet2, Sheet4, Sheet6, and Sheet 9 are the same as Row 2 of sheet1 and a message box appearing would show in which sheets the rows are different. Can anybody help Thxs |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check - Similar Row contents - Different Sheets
thxs a lot!!!
(1)How can I amend it to check column B - From row2 to row 20?? - if it's not too much asking. & In the row macro - (2) how to replace the "variables" by a user message box i.e "Sheet1" Range("A2:IV2") "Sheet2", "Sheet4", "Sheet6", "Sheet9" (as I would like to turn it into a standard macro which I can use in other workbooks with different sheetname & row. Many Many Thxs for your help - you're the best Tom Ogilvy wrote: Sub CheckRow2() Dim v, v1 Dim i As Long, k As Long, bMisMatch As Boolean Dim rng As Range, cell As Range, sStr As String v = Array("Sheet2", "Sheet4", "Sheet6", "Sheet9") v1 = Worksheets("Sheet1").Range("A2:IV2") For i = LBound(v) To UBound(v) Set rng = Worksheets(v(i)).Rows(2).Cells k = 0 bMisMatch = False For Each cell In rng k = k + 1 If cell.Value < v1(1, k) Then bMisMatch = True Exit For End If Next If bMisMatch Then sStr = sStr & v(i) & ", " End If Next If Len(sStr) 3 Then MsgBox "Sheets not matching: " & vbNewLine & _ Left(sStr, Len(sStr) - 2) Else MsgBox "Good to go" End If End Sub -- Regards, Tom Ogilvy "al007" wrote in message oups.com... Not necessarily all 256 cells - you can take Column A to Z( i WILL MODIFY ACCORDINGLY) - actual the reason behind that is that I want to check that the data in all sheets are under the same header so that I can sum across sheet to get a total.Thxs a lot for your help - waiting impatiently for your suggestion. I'm sure you'll enrich my excel again... THXS tHXS Tom Ogilvy wrote: the entire contents or the content of specific cells? (all 256 cells in each row?) You say similar in the subject and the same in the body. -- Regards, Tom Ogilvy "al007" wrote in message oups.com... I'm looking for a macro which would check ifcontents of Row 2 of Sheet2, Sheet4, Sheet6, and Sheet 9 are the same as Row 2 of sheet1 and a message box appearing would show in which sheets the rows are different. Can anybody help Thxs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add similar rows in excel sheets | New Users to Excel | |||
Comparing data in two sheets with similar values | Excel Worksheet Functions | |||
read contents of col 1 and extract similar strings | Excel Discussion (Misc queries) | |||
HOW DO I CHECK FOR SIMILAR NUMBERS IN A WORK SHEET | Excel Worksheet Functions | |||
Copying a column from several similar sheets into one sheet | Excel Programming |