[RESOLVED] Compare Data Between worksheets

[tome10]

Thanks in adv for help.
I have 2 worksheets. The primary worksheet (Sheet1) only has data in the first 50 Rows.
The 2nd worksheet has a lot of Data down to 200 at least.
The Macro has to look for my Name in (Sheet2) Column F (F3:F200), and then the Project Number in Column B (B3:B200) (I guess I need an Offset command on this.)
It has to compare the Proj Number in Culumn B (Sheet2) B3:B200) (Only if it has my name in Column F) with the projects in Column E (E6:E50) of (Sheet1), and if there is no match before it reaches the end of the Data it places it in the next (empty) Row of Sheet1.
Thanks for the help.

[seenu_1st]

where did u stuck in ur code?

[kaliman79912]

an idea:

Sub a()
 
    Dim endRow As Integer
    Dim newRow As Integer
    Dim i As Integer
    Dim MyName As String
    Dim Rng As Range
        
    MyName = "Luis"
    endRow = Sheets("Sheet2").Range("F1").End(xlDown).Row
    newRow = Sheets("Sheet1").Range("F1").End(xlDown).Row + 1
    For i = 1 To endRow
        If Sheets("Sheet2").Cells(i, 6) = MyName Then
            Sheets("Sheet1").Columns("B:B").Select
            Set Rng = Selection.Find(What:=Sheets("Sheet2").Cells(i, 2).Value)
            If Rng Is Nothing Then
                Sheets("Sheet2").Rows(i).Copy
                Sheets("Sheet1").Cells(newRow, 1).Select
                ActiveSheet.Paste
                newRow = newRow + 1
            End If
        End If
    Next i
 
End Sub

[tome10]

Thanks for the help Kaliman.
for some reason it's erroring on End Row=
I don't know if I have all the Starting and ending rows correct, and if it finds a match it should move to the next row on the Data sheet (sheet10).

        Sub Vldt()
        
            Dim endRow As Integer
            Dim newRow As Integer
            Dim i As Integer
            Dim MyName As String
            Dim Rng As Range
                
            MyName = "Thomas"
            endRow = Sheets("Sheet10").Range("E1").End(xlDown).Row 'Errors Subscript out of Range
            newRow = Sheets("Sheet1").Range("E1").End(xlDown).Row + 1
            For i = 3 To endRow
                If Sheets("Sheet10").Cells(i, 6) = MyName Then
                    Sheets("Sheet10").Columns("B:B").Select
                    Set Rng = Selection.Find(What:=Sheets("Sheet1").Cells(i, 2).Value)
                    If Rng Is Nothing Then
                        Sheets("Sheet10").Rows(i).Copy
                        Sheets("Sheet1").Cells(newRow, 1).Select
                        ActiveSheet.Paste
                        newRow = newRow + 1
                    End If
                End If
                Next
             
            End Sub

[kaliman79912]

Subscript out of range indicates that the Sheet is wrong. make sure your sheet name is "Sheet10". Even better, change the name of the sheet to something more representative and use that.
In that example, endRow is just counting the rows of the second sheet, the one with the many rows (it is expecting column E to be full, if there is a gap or empty cell in E it wont work correctly).
newRow is looking for the next row that will be filled in the first sheet. Again, needs column E to have all consecutive values.

[tome10]

Well, it's doing stuff, but it's not the right stuff. I think it's just taking the Row with my name and pasting it on top of the forst Row of the Main sheet.
I need it to:
1)Check my name in the Row Sheets("Porter").Range("F3:200")
2)Hold the Project Number in a String on same row Sheets("Porter").Range("B3:B200")
3)Loop through the Projects in Sheets("LD").Range("E6") to Used Range
4)If it finds the Project in the LD "E" Column, it should move to the next row in the Porter sheet.
5)If it doesn't find the Project in the LD "E" Column it should place the string in the blank "E" Cell
6)5. will eventually be expanded to move Porter "D" info to LD "F", and Porter "E" info to LD "B"
Problems:
1)I'm seeing with select and paste is that I have a Selectionchange by Val on LD, and it's skipping to that Macro unnecessarily.
2)Why is it selecting the entire "E:E" column?

        Sub Vldt()
            Dim endRow As Integer
            Dim newRow As Integer
            Dim i As Integer
            Dim MyName As String
            Dim Rng As Range
                
            MyName = "Thomas"
            endRow = Sheets("Porter").Range("E1").End(xlDown).Row
            newRow = Sheets("LD").Range("E1").End(xlDown).Row + 1
            For i = 3 To endRow
                If Sheets("Porter").Cells(i, 6) = MyName Then
                    Sheets("LD").Columns("E:E").Select
                    Set Rng = Selection.Find(What:=Sheets("LD").Cells(i, 2).Value)
                    If Rng Is Nothing Then
                        Sheets("Porter").Rows(i).Copy
                        Sheets("LD").Cells(newRow, 1).Select
                        ActiveSheet.Paste
                        newRow = newRow + 1
                    End If
                End If
                Next
                End Sub

[kaliman79912]

to comply with your list do the following:

1) change the line to:
endRow = Sheets("Porter").Range("F1").End(xlDown).Row
this will not be limited to 200 rows as your example suggests, if that were the case then endRow = 200
2) That part is handled by the comparisson made in
If Sheets("Porter").Cells(i, 6) = MyName Then
But here we are considering that the name column is column "F, if it is "E than change the previous line and also here to .Cells(i, 5)
3) This line you changed wrong
Set Rng = Selection.Find(What:=Sheets("Porter").Cells(i, 2).Value)
The number two is expecting the program to be in column "B", if it is in another column adapt the number to it.
4 & 5) Set Rng Is Nothing takes care of that
your problems:
1) I did not understand what you mean.
2) Its selecting entire column E to search for the program, in my original example it was on column B because that is what you said in your original post. If the actual column that holds the programs is "E" you need to change
What:=Sheets("Porter").Cells(i, 5)

[tome10]

It's Pasting the entire row from the Porter Sheet into the 1st Row of the LD sheet
I just need the project number Porter "B" to LD "E" in the next blank Row (if the project number isn't already in the LD "E" Column.
When I cycle through the Macro with the F8 Key, everytime it selects something it triggers the Selectionchange Macro, and it jumps from Macro to Macro. I guess it won't hurt anything, but I'd rather the Macro not select anything directly.
Can you work this into the Loop?
'Sheets("LD").Cells(i, 5) = Sheets("Porter").Cells(i, 2).Value

        Sub Vldt()
        
            Dim endRow As Integer
            Dim newRow As Integer
            Dim i As Integer
            Dim MyName As String
            Dim Rng As Range
                
            MyName = "Thomas"
            endRow = Sheets("Porter").Range("F1").End(xlDown).Row
            newRow = Sheets("LD").Range("E:E").End(xlDown).Row + 1
            For i = 3 To 200
                If Sheets("Porter").Cells(i, 6) = MyName Then
                    Sheets("LD").Columns("E:E").Select
                    Set Rng = Selection.Find(What:=Sheets("Porter").Cells(i, 2).Value)
                    If Rng Is Nothing Then
                        Sheets("Porter").Rows(i).Copy
                        Sheets("LD").Cells(newRow, 1).Select
                        ActiveSheet.Paste
            'Sheets("LD").Cells(i, 5) = Sheets("Porter").Cells(i, 2).Value '(Can you make this work?
                        newRow = newRow + 1
                    End If
                End If
                Next
             
            End Sub

[kaliman79912]

I will try to do it without selecting but I think you should not use a macro with that event, it can put you in more trouble.
So you do not need the whole row, just the project number? it is originally in porter sheet in column B, and you need it in LD sheet in E column?

[kaliman79912]

try it like this:

Sub Vldt()
 
    Dim endRow As Integer
    Dim newRow As Integer
    Dim i As Integer
    Dim MyName As String
    Dim Rng As Range
        
    MyName = "Thomas"
    endRow = Sheets("Porter").Range("F3").End(xlDown).Row
    newRow = Sheets("LD").Range("E1").End(xlDown).Row + 1
    For i = 3 To endRow
        If Sheets("Porter").Cells(i, 6) = MyName Then
            Set Rng = Sheets("LD").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
            If Rng Is Nothing Then
                Sheets("Porter").Cells(i, 2).Copy
                ActiveSheet.Paste Destination:=Worksheets("LD").Cells(newRow, 5)
                newRow = newRow + 1
            End If
        End If
    Next i
 
End Sub

Note that in order for this to work, there must be at least two data in column F in Porter sheet with no black cells, the same for column E in LD sheet,

[tome10]

yes that is correct. but of course only of my project designated by my name in porter column "F".
I was always told to not select and manipulate what you can do with code directly. Good Bad, I don't know, but I know it wouldn't trigger the Existing Selectionchange Macro I have in both sheets.

[kaliman79912]

I think you just missed my last post for less than a minute

[kaliman79912]

I would go with the code in my last post because it is clearer. But this is a compact version of the exact same algorithm.

Sub Vldt()
 
    MyName = "Thomas"
    newRow = Sheets("LD").Range("E1").End(xlDown).Row + 1
    
    For i = 3 To Sheets("Porter").Range("F3").End(xlDown).Row
        If Sheets("Porter").Cells(i, 6) = MyName And Sheets("LD").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value) Is Nothing Then
            Sheets("Porter").Cells(i, 2).Copy
            ActiveSheet.Paste Destination:=Worksheets("LD").Cells(newRow, 5)
            newRow = newRow + 1
        End If
    Next i
 
End Sub

i

[tome10]

It's starting on the LD "E6" and Pasting all my projects in order from the Porter sheet.
It's not Comparing the Porter Sheet project Numbers with the LD Project numbers that are already there, it's just pasting them all in order from Porter. It needs to put the project number from porter and put it in a String and compare as it loops down the LD sheet. I need a loop within a loop, and that's too hard for me.

[tome10]

The short code isn't working.
New Row is always 6, and i loops from 3-6 then starts over. I can't tell it's doing anything to the LD sheet

[kaliman79912]

you do not need a loop. It only may be that we are not understanding each other right.
let me tell you exactly what this is doing so you can see where did I understand it wrong.

* You have two sheets: "LD" and "Porter"
* In the Porter sheet you have some data, where in column "F" there are some names and in column "B" project numbers.
* In the LD sheet you have the project numbers on column "E"
* The code first searches in order column "F" until it finds a match for your name ("Thomas")
* Then it looks in LD sheet, column E to see if the corresponding Program Number is already there, if not it adds it at the bottom of the list.

Is this what you want?

Things that may mess it up:

If Porter sheet, column F has less than 2 data starting from F3
If column F in porter sheet has blank cells
If column E in LD sheet has less than 2 data starting from E1
If column E in LD sheet has blank cells
If there are spaces (not blank) on cells in column E

[tome10]

Your understanding Correctly, and the short code looks like it isn't duplicating the Projects, but it is still Pasting the Data starting with LD Line 6. Somehow it needs a command to where it knows the first open row and pastes there.
I don't like the Paste, because it messes with the Formatting of the LD sheet.
I may not need a Loop, but I'd rather have a Loop, String, Compare, Insert. But that's just me ;-)
Also, the reason it wasn't doing anything earlier, is I suppose because I deleted my name is Row 2 just to make sure it could handle it. I can understand why a Blank on the LD sheet would mess it up, but why does a Blank in the Porter sheet mess it up?

[kaliman79912]

OK. If you dont want the .paste lets just fill the data in the cell
don't use the short code. the other one is a bit more reliable.

Sub Vldt()
 
    Dim endRow As Integer
    Dim newRow As Integer
    Dim i As Integer
    Dim MyName As String
    Dim Rng As Range
        
    MyName = "Thomas"
    endRow = Sheets("Porter").Range("F3").End(xlDown).Row
    newRow = Sheets("LD").Range("E1").End(xlDown).Row + 1
    For i = 3 To endRow
        If Sheets("Porter").Cells(i, 6) = MyName Then
            Set Rng = Sheets("LD").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
            If Rng Is Nothing Then
                Sheets("LD").Cells(newRow, 5).Formula = Sheets("Porter").Cells(i, 2).Value
                newRow = newRow + 1
            End If
        End If
    Next i
 
End Sub

The blanks would hurt because the list is looking for consecutive values. If there is a blank in the names column it will stop when it reaches it.

[tome10]

It's still adding the projects in order from LD "E6" on down. I deleted Everything out of LD "E:E" and put AAABBBCCC in Porter "B5". I put the same AAABBBCCC in LD "E12". It detected it in the LD column, because it did not paste the value when it got to "E6", but later on it pasted something on top of "E12" anyways.

[kaliman79912]

That sounds to me like either there is nothing on some cells above E6. Is it possible that you post your sheet as an attachment?

[tome10]

E1:E5 is my header field, I have buttons to run macros, and the Column Titles.

[kaliman79912]

Then make a this change:

newRow = Sheets("LD").Range("E6").End(xlDown).Row + 1

Now, data must be on at least 2 cells of "LD" sheet (E6 & E7), if there is not then we would need to add a few lines to the code. Also in "Porter" sheet, cells F3 and F4

If you are shure that E5 contains the header then you could put .Range("E5") in the above line and would only need one extra data on E6.

I must insist. Put a copy of your sheet, change some data and delete other code if you are concerned about your intelectual property or confidential data. But it would benefit me (us) to have a broad feeling of the actual layout.

[tome10]

Uploaded.

[kaliman79912]

It appears to work OK if you follow the steps in my last post. set the newRow according to "E5" and make sure there's at least one data in "E6"

[kaliman79912]

Just figured out a simple way of coding it without the need of any data in LD column E, or Porter column F

change rows 10 and 11 to

endRow = Sheets("Porter").Range("F10000").End(xlUp).Row
    newRow = Sheets("LD").Range("E10000").End(xlUp).Row + 1

[tome10]

Alright, this works. Now I need something else.. I need it to disregard my name, and look at the project numbers on both sheets, to take the Due Date from Porter "E" and update LD "B". That should help out a bunch.

[kaliman79912]

Alright, this works. Now I need something else.. I need it to disregard my name, and look at the project numbers on both sheets, to take the Due Date from Porter "E" and update LD "B". That should help out a bunch.

lets see if I understand. after you do what you did with the previous code you need to search for the project numbers that you have in LD. and for each one update column B according on what that same project number has on column E in Porter Sheet.

Right?

[tome10]

That is correct. Thanks for the help. ;-)

[tome10]

I also have a problem with the original code. I have a History sheet with completed projects. It's the exact same layout as LD, but the Name is "Hist". Problem is the "Porter" Sheet seems to not get updated with Completions in a timely manner, and the code is just importing completed projects back into "LD".
So, I need it to look in "Hist" used range first, and then look in "LD" and if it can't find the Project, then import the data into "LD".

[tome10]

I put this together, I used <> for if <is not> "this" I thought that was Legit, but it comes back with an error.
This is trying to cycle through the History sheet first and make sure the projects aren't there and then cycle through the LD sheet and if the project is not there then to transfer it to LD.

    Sub PrLead()
        Dim endRow As Integer
        Dim newRow As Integer
        Dim i As Integer
        Dim MyName As String
        Dim Rng As Range

        Sheets("LD").ToggleButton1.Value = "True"
        MyName = "Thomas"
        endRow = Sheets("Porter").Range("F3").End(xlDown).Row
        newRow = Sheets("LD").Range("E6").End(xlDown).Row + 1
        For i = 3 To endRow
             If Sheets("Porter").Cells(i, 6) = MyName Then
                Set Rng = Sheets("Hist").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
                 If Rng <> Sheets("Porter").Cells(i, 2).Value Then  ' it hangs up here Object Variable with Block Variable not set 
                Set Rng = Sheets("LD").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
                 If Rng <> Sheets("Porter").Cells(i, 2).Value Then
                    Sheets("LD").Cells(newRow, 5).Formula = Sheets("Porter").Cells(i, 2).Value
                    Sheets("LD").Cells(newRow, 2).Formula = Sheets("Porter").Cells(i, 5).Value
                    Sheets("LD").Cells(newRow, 6).Formula = Sheets("Porter").Cells(i, 4).Value
                    newRow = newRow + 1
                End If
            End If
            End If
        Next i
        Sheets("LD").ToggleButton1.Value = "False"

            End Sub

[kaliman79912]

You are in the right track, except that in the line in question you are trying to assing the value of a cell to a range, just use a variable of the type of the contents of the cells instead of Rng which is declared as a Range

[tome10]

I'm not really sure what your saying, so, can you help me out? ;-)

[kaliman79912]

I'm not really sure what your saying, so, can you help me out? ;-)

When you declare Rng it is defined as range

        Dim Rng As Range

And you assign it the range resulting of a find

Set Rng = Sheets("Hist").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)

But then you compare it to a cell's value:

If Rng <> Sheets("Porter").Cells(i, 2).Value Then

Rng holds a range, like A1:C200 or the like, whilest Sheets("Porter").Cells(i, 2).Value holds a value, like 10 or 134.4 or "John". These are two different types of object and can't compare them. What you need is a way to extract the value of the first cell of Rng, a range can be any size, in this case it is just one cell but VBA has no way of knowing this until it executes. And even then, you are not asking for its value but the range. Is this somewhat clear?

[tome10]

Alright, so this is doing 2 things. Setting the range to look in and finding the project number.

Set Rng = Sheets("Hist").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)

So I need something to say; if it doesn't find the project number to change the range to the LD sheet and look there.
How do I do that?

[kaliman79912]

Alright, so this is doing 2 things. Setting the range to look in and finding the project number.


So I need something to say; if it doesn't find the project number to change the range to the LD sheet and look there.
How do I do that?

Yes, this part is ok because you are assigning a range to a variable of type range, but then you compare the range to a value.

What you need to do in the other statement is to look for the value of a cell inside the range and compare it to the other value. You cannot compare the characteristics of a basket to the contents of another one, makes no sense.
what you are saying with If Rng <> Sheets("Porter").Cells(i, 2).Value Then is like saying If a big basket is the same as 12 oranges Then, when what you should be saying is If the contents of the basket is the same as 12 oranges Then, am I making my self clear? sorry for all the confussion but I don't know how to explain it. Anyway you should code that like somethink like this:

If Rng(1.1).Value <> Sheets("Porter").Cells(i, 2).Value Then

[tome10]

I have to ask.. What does the (1.1) represent.

[kaliman79912]

Sorry, it should be (1,1). it is the first cell in the range (row 1, cell 1), in this case its the only one but still, you need to reference the value of a cell in a range to compare it to a value.

It does not represent "A1" since it is confined to Rng

[tome10]

Alright, there's something wrong.. It errors saying Rng=Nothing.

If Rng(1, 1).Value <> Sheets("Porter").Cells(i, 2).Value Then 'Errors here

    Sub PrLead()
        Dim endRow As Integer
        Dim newRow As Integer
        Dim i As Integer
        Dim MyName As String
        Dim Rng As Range

        Sheets("LD").ToggleButton1.Value = "True"
        MyName = "Thomas"
        endRow = Sheets("Porter").Range("F3").End(xlDown).Row
        newRow = Sheets("LD").Range("E6").End(xlDown).Row + 1
        For i = 3 To endRow
             If Sheets("Porter").Cells(i, 6) = MyName Then
                Set Rng = Sheets("Hist").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
                 If Rng(1, 1).Value <> Sheets("Porter").Cells(i, 2).Value Then  'Errors here Says Range = Nothing
                Set Rng = Sheets("LD").Columns("E:E").Find(What:=Sheets("Porter").Cells(i, 2).Value)
                 If Rng(1, 1).Value <> Sheets("Porter").Cells(i, 2).Value Then
                    Sheets("LD").Cells(newRow, 5).Formula = Sheets("Porter").Cells(i, 2).Value
                    Sheets("LD").Cells(newRow, 2).Formula = Sheets("Porter").Cells(i, 5).Value
                    Sheets("LD").Cells(newRow, 6).Formula = Sheets("Porter").Cells(i, 4).Value
                    newRow = newRow + 1
                End If
            End If
            End If
        Next i
        Sheets("LD").ToggleButton1.Value = "False"

            End Sub

[kaliman79912]

that would happen if the previous statement, which is a "Find" does not find anything. Then the range would be nothing. Going back to the basket of oranges, Rng would have no basket, so you can't look inside the basket if there is none.

[tome10]

I have the first/main part working, thanks for the help..

This part is the one that has to look up the proj numbers from LD Col "E" and find them in the porter sheet Col "B" and bring the Due Dates Porter Col "E" over to LD Col "B".
It's kinda working. Seems Random at best, but it is bringing every date over from Porter which is a start, but it is not comparing the Project numbers and it needs something to say ignore blank cells,and only go after the dates for project numbers existing in the LD sheet.

                For i = 6 To endRow
                Set Rng = Sheets("Porter").Columns("B:B").Find(What:=Sheets("LD").Cells(i, 5).Value)
                    Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(i, 5).Value

              Next i