Loop Through Range 2 ranges

realniceguy5000

Board Regular
Joined
Aug 19, 2008
Messages
148
Hi, I need some help. I have 2 worksheets I need to check. i need to loop through sheet1 column a, find same value on sheet2 column a then somehow loop through each column on sheet1 same row and check the row that was found on sheet2 each column to make sure they match if they dont match then I need to mark the cells on both sheets and continue checking the values accross the entire row. Once done checking if they dont match move the rows to sheet3

Here is what I came up with so far but I am stuck.


Thanks for any help

Code:
Sub ColumnChecks()
colcount = 1
    With Sheets(1)
        Lrow1 = .Cells(Rows.Count, 1).End(xlUp).Row
        Lcol1 = .Cells(1, 256).End(xlToLeft).Column
            For x = Lrow1 To 2 Step -1
                For y = 1 To Lcol1
                    '.Cells(x, y).Select
                    Set c = .Cells(x, y)
                        With Sheets(2)
                            .Select
                               Set cfind = .Columns(1).Cells.Find(what:=c.Value, lookat:=xlWhole)
                               
 'not sure what now as I found the row on the 2 sheet but have to loop through values on the first sheet and loop
                                ' through this row on the 2nd sheet
                                
                                                  
                        
                        
                        
                        End With
                Next y
            Next x
    
    
    End With
    
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
you want to check both sheet col A value or all column ( A B C D E ..etc ) like ?
 
Upvote 0
Hi, Thanks for the repy,
I need to check the entire row on both sheets once a match is found. exp: sheet1 A vs if found on sheet2 A then check sheet1 B and sheet2 B of the same row that was found on sheet2 all the way to the end column. My worksheets are layout the same way just the data may be different. so i need to find the differances if any. My data can be over 15000 rows and about 60 columns. Just not sure what the best way to do this is. or even how :(
 
Upvote 0
ok got it one more question if suppose in one row only one cell not match then you want to move that entirerow or only that cell to sheet 3 ?

2 nd question you want to move both sheet data or only one sheet data ?
 
Upvote 0
I will always be moving the non maching rows to sheet 3 ,But Note I still need to check all the cells in that row before moving the row.

So if any cells dont match then the entire row will be moved but only after all the cells have been checked in that row. Make sense?

Thanks again for helping me this is driving me crazy.
 
Upvote 0
still i am confused you want to move both sheet data or only one sheet data pls clarify ?
 
Upvote 0
Sorry, Yes I want to move both rows to the 3rd sheet.

I like to send a test workbook but dont know how to attached here. It would make more sense to you if you saw the data I was looking at.

Once again thank you and sorry for not making much sense.
 
Upvote 0
TRY BELOW CODE IN SAMPLE WORKBOOK
Code:
Sub Test()
Dim lr As Integer
Dim i As Integer
Dim J As Integer
Application.ScreenUpdating = False
Sheets("Sheet1").Select
For J = 1 To 80 ' it will check till 80 column you can change as your data
    lr = Cells(Rows.Count, J).End(xlUp).Row
    For i = 1 To lr
        If Cells(i, J) <> Sheets("Sheet2").Cells(i, J) Then
            Cells(i, J).Interior.ColorIndex = 6
            Sheets("Sheet2").Cells(i, J).Interior.ColorIndex = 6
        End If
    Next i
Next J
Sheets("Sheet1").Select
K = 1
Cells(K, 1).Select
lr = Range("A" & Rows.Count).End(xlUp).Row
For l = 1 To lr
    Do Until ActiveCell.Column = 80
    If ActiveCell.Interior.ColorIndex = 6 Then
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Exit Do
    Else
    ActiveCell.Offset(0, 1).Select
    End If
    Loop
K = K + 1
Cells(K, 1).Select
Next l
Range("A1").Select
Sheets("Sheet2").Select
m = 1
Cells(m, 1).Select
lr = Range("A" & Rows.Count).End(xlUp).Row
For n = 1 To lr
    Do Until ActiveCell.Column = 80
    If ActiveCell.Interior.ColorIndex = 6 Then
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Exit Do
    Else
    ActiveCell.Offset(0, 1).Select
    End If
    Loop
m = m + 1
Cells(m, 1).Select
Next n
Range("A1").Select
Sheets("Sheet3").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks for your help, seems like it is working just great, I'm going to try and clean it up some to avoid the selects since I have 15000 rows and 60 columns to check it could take some time to complete. But anyway thanks again, not sure how you knew what I wanted from all the statments above. But your script is GREAT!!! Got me going again...

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top