[RESOLVED] Compare Data Between worksheets

[kaliman79912]

before you compare check for nothingness. As the Find method may not find what you are looking for and thus be "nothing", in that case you should not compare it to anything (it appears that you took out that line, but you need it so it will only copy what you want). Before you do that, you must make sure Rng has something, meaning it found a value in the range.

If Rng Is Nothing Then

[tome10]

I put the code in a Module by itself so I can see what it's doing.. The Rng is coming back Null I think.
It should be cycling through the LD Col "E" and finding the matching numbers in Porter Col "B". So, what's wrong?

        Sub Dates()
        
        Dim i As Integer
        Dim endRow As Integer
        Dim Rng As Range
                 
          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

        End Sub

[kaliman79912]

In your recent code you use Rng to set the range of the result of the find. But on the next line you simply assign the value of a cell in "Porter" to another cell in "LD" without taking into consideration the value of Rng. In fact, you never use that value making the line pointless.

In earlier versions of your program you were checking to see if the searched value was found and then do the copy. The problem there is that when it did not find the searched item it was assigned "nothing" thus making the comparisson fail.

What you want to do is only copy when it finds it, so it would be something like:

For i = 6 To endRow
    Set Rng = Sheets("Porter").Columns("B:B").Find(What:=Sheets("LD").Cells(i, 5).Value)
    If Not(Rng is nothing) then     
        Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(i, 5).Value
    End If
Next i

[tome10]

It isn't doing anything. When I cycle through with f8, it jumps from For i to End Sub. ideas?

        Sub Dates()
        
        Dim i As Integer
        Dim endRow As Integer
        Dim Rng As Range
                 
        For i = 6 To endRow
            Set Rng = Sheets("Porter").Columns("B:B").Find(What:=Sheets("LD").Cells(i, 5).Value)
            If Not (Rng Is Nothing) Then
                Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(i, 5).Value
            End If
        Next i

        End Sub

[tome10]

I couldn't figure out what was wrong with the other code, so I started working with a piece of code I have from another project and finally got it to working.. Almost.. Only thing this is doing wrong is that it's out of sync.. LD sheet starts at Row 6, and Porter sheet starts on Row 3. So it's 3 behind. I think i'm supposed to dim another letter "c", and say c=3, but I can't figure out how I'm supposed to make it count. it's just stuck filling in all the LD cells with the first porter Row Date..

         Sub Dates()
            Dim i As Integer, c As Integer
            'Dim endRow As Integer
            'Dim Rng As Range
            
            Dim ws As Worksheet, aCell As Range
            Set ws = Sheets("Porter")
            
            c = 3
            For i = 6 To 200 'endRow
            
            If Sheets("LD").Cells(i, 5).Value <> "" Then
                Set aCell = ws.Range("B3:B200").Find(What:=Sheets("LD").Cells(i, 5).Value, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Not aCell Is Nothing Then
                Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(c, 5).Value
            End If
            End If
            
            Next
        
        End Sub

[kaliman79912]

Yes, in this little example you left out the calculation of endRow, so the loop can't go from 6 to 0 the way its setup. the calculation of endRow is important so you do not need to set the loop to 200.

you do not need another variable, just when referencing the Porter Sheet deduct 3 from i

Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(i-3, 5).Value

[tome10]

1) I can't make the Endrow work at all, it just refuses to work. 2) it's not finding the Project number and bringing over the corresponding date. It seems to be just going down the line and filling in the dates in order from the porter sheet. So I'm missing something.

How do I make this work?
How do I make it bring back the row it found the project number in? i.row being the Row in Porter where it found the project number. It's not activerow, because that always brings back the endrow.

MsgBox Sheets("LD").Cells(i, 5).Value & " Found. " & i.Row

         Sub Dates()
            Dim i As Integer
            'Dim endRow As Integer
            'Dim Rng As Range
            
            Dim ws As Worksheet, aCell As Range
            Set ws = Sheets("Porter")
            
            For i = 6 To 200 'endRow
            
            If Sheets("LD").Cells(i, 5).Value <> "" Then
                Set aCell = ws.Range("B3:B200").Find(What:=Sheets("LD").Cells(i, 5).Value, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Not aCell Is Nothing Then
                Sheets("LD").Cells(i, 2).Formula = Sheets("Porter").Cells(i - 3, 5).Value
                MsgBox Sheets("LD").Cells(i, 5).Value & " Found. " & i.Row 
            End If
            End If
            
            Next
        
        End Sub

[kaliman79912]

OK, why dont you post as attachment the project as it is now so I can take a look at it with latest additions. I have been trying to direct you in the direction I think is best. Let me now try to solve the issue as for some reason it is eluding us.

you are assigning the result of the .Find to aCell, so the found row must be aCell.Row, i.Row makes no sense as i is just an integer counter.

[tome10]

Once you told me that it was the aCell I should be looking for, I got it to work. with aCell.offset(0,3).value

So the only thing not working now is the End Row Loop. Why did it stop working? Shouldn't it have an xlUp command or something?

         Sub Dates()
            Dim i As Integer, c As Integer
            'Dim endRow As Integer
            'Dim Rng As Range
            
            Dim ws As Worksheet, aCell As Range
            Set ws = Sheets("Porter")
            
            For i = 6 To 200 'endRow
            
            If Sheets("LD").Cells(i, 5).Value <> "" Then
                Set aCell = ws.Range("B3:B200").Find(What:=Sheets("LD").Cells(i, 5).Value, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Not aCell Is Nothing Then
                Sheets("LD").Cells(i, 2).Formula = aCell.Offset(0, 3).Value
                'MsgBox Sheets("LD").Cells(i, 5).Value & " Found. " & aCell.Row & aCell.Offset(0, 3).Value
            End If
            End If
            
            Next
        
        End Sub

[kaliman79912]

The problem is that you took out the calculation of endRow and in your code endRow is always zero. before the start of the loop you must determine the endRow (which is the last row that has data)

endRow = Sheets("Porter").Range("E1").End(xlDown).Row

[kaliman79912]

your code would currently be something like

Sub Dates()
    Dim i As Integer, c As Integer
    Dim endRow As Integer
    
    Dim ws As Worksheet, aCell As Range
    Set ws = Sheets("Porter")
    
    endRow = ws.Range("E10000").End(xlUp).Row
    
    For i = 6 To endRow
    
        If Sheets("LD").Cells(i, 5).Value <> "" Then
            Set aCell = ws.Range("B3:B200").Find(What:=Sheets("LD").Cells(i, 5).Value, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not aCell Is Nothing Then
                Sheets("LD").Cells(i, 2).Formula = aCell.Offset(0, 3).Value
            End If
        End If
    
    Next
 
End Sub

I still think you should send me the workbook so I can do a thorough annalysis of it.