Comparing cells from two different worksheets

zoog25

Active Member
Joined
Nov 21, 2011
Messages
418
Hello all,

Here is my situation, I have Sheet 1 that is titled "Tract Parcels" and Sheet 2 that is titled "NOC". Currently i started a short program that would look at entries made from sheet 1 and if it exists in sheet 2 then a message would appear that says that it already exists, now if it didn't then it would copy the info over.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


'Initiate Work
Dim lrow As Long
Dim NOCws As Worksheet
Dim TPws As Worksheet


Set NOCws = ThisWorkbook.Worksheets("NOC")
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


'Find last row in NOC Log
lrow = NOCws.Cells(Rows.Count, 5).End(xlUp).Row


Stop


'NOC Auto Entry After Map goes to Council
If Not Intersect(Target, Range("E:F")) Is Nothing Then
    If WorksheetFunction.CountA(Cells(Target.Row, "E").Resize(, 2)) = 2 Then
        With NOCws.Range(Cells(4, "F"), Cells(lrow, "F"))
            Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
            If Not NOCTP Is Nothing Then
                
                Do
                Loop While NOCTP.Address <> firstAddress
            End If
        End With
    End If
End If


End Sub

So the above is from code that I found and have manipulated. My problem is that in this situation, it first Take the value in Column G of Sheet 1 and looks for it in Column F of Sheet 2. How my problem is this, if it find a match between the two, i want to add another comparison. Next if say Column H of Sheet 1 Matches Column G of sheet 2 then a message would appear saying that "NOC Entry already made", otherwise the values from columns (A, B, E, D, G, H, & I) from sheet 1 would then be copied over to the first available row in Sheet 2 into columns (B,C,D,E,F,G,I) Respectively.

Now i know the above loops through each instance that the information being search for appears. Ultimately if all instances don't match up then it transfers info, otherwise the message appears saying that it has already been entered.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
maybe this

Code:
With NOCws.Range(Cells(4, "F"), Cells(lrow, "F"))
    Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
        If Not NOCTP Is Nothing Then
            If Cells(Target.Row, "H").Value = .Cells(NOCTP.Row, "G").Value Then
                MsgBox "NOC entry already made.", vbInformation, "ENTRY MADE"
            Else
                .Cells(NOCTP.Row, "B") = Cells(Target.Row, "A").Value
                .Cells(NOCTP.Row, "C") = Cells(Target.Row, "B").Value
                .Cells(NOCTP.Row, "D") = Cells(Target.Row, "E").Value
                .Cells(NOCTP.Row, "E") = Cells(Target.Row, "D").Value
                .Cells(NOCTP.Row, "F") = Cells(Target.Row, "G").Value
                .Cells(NOCTP.Row, "G") = Cells(Target.Row, "H").Value
                .Cells(NOCTP.Row, "I") = Cells(Target.Row, "I").Value
            End If
        End If
End With
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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