syncronizing 2 sheets problem

[next]

[VBA]
Option Explicit
Sub Test()
Application.ScreenUpdating = False
Dim LTD_xls As Worksheet, Open_dif As Worksheet
Dim rLTD As Range, rOPN As Range
Dim ltdLastRow As Integer

Set LTD_xls = Workbooks("LTD Open.xls").Worksheets("open")
Set Open_dif = Workbooks("open.xls").Worksheets("open")
ltdLastRow = LTD_xls.Range("A1").End(xlDown).Row

For Each rLTD In LTD_xls.Range("$A$2:$A$5000")
If IsEmpty(rLTD) Then Exit For

For Each rOPN In Open_dif.Range("$A$2:$A$5000")
If IsEmpty(rOPN) Then Exit For
If (rOPN = rLTD) Then
'syncronize cells in range M through Z in between "LTD open" and "open.dif" based on
'"open.dif"s conents.
rOPN.Range("M" & rOPN.Row & ":Z" & rOPN.Row).Copy rLTD.Range("M" & rLTD.Row & ":Z" & rLTD.Row)
End If
Next rOPN
Next rLTD

Application.ScreenUpdating = True
End Sub
[/VBA]
1) I'm trying to synchronize 2 sheets, but for some reason first row is never updated, why?
2) If "work order" on LTD_xls doesn't exist on Open_dif i would like to remove the whole row, any suggestions on how do i handle that?

P.S. i can't attach more than 1 file, but layout in "LTD Open.xls" is the same, it's only the content that's different. "Open.xls" is attached to this message.

Thanks!

[next]

i guess my problem lies deeper
[vba]
Option Explicit
Sub Test()
Application.ScreenUpdating = False
Dim LTD_xls As Worksheet, Open_dif As Worksheet
Dim rLTD As Range, rOPN As Range
Dim ltdLastRow As Integer

Set LTD_xls = Workbooks("LTD Open.xls").Worksheets("open")
Set Open_dif = Workbooks("open.xls").Worksheets("open")
ltdLastRow = LTD_xls.Range("A1").End(xlDown).Row

For Each rLTD In LTD_xls.Range("A:A")
If IsEmpty(rLTD) Then Exit For

If IsNumeric(rLTD) Then
For Each rOPN In Open_dif.Range("A:A")
If IsEmpty(rOPN) Then Exit For

If IsNumeric(rOPN) Then
Debug.Print rLTD.Address & " - " & rOPN.Address
If (rOPN = rLTD) Then
'syncronize cells in range M through Z in between "LTD open" and "open.dif" based on
'"open.dif"s conents.
rOPN.Range("L" & rOPN.Row & ":Z" & rOPN.Row).Copy rLTD.Range("L" & rLTD.Row & ":Z" & rLTD.Row)
Debug.Print rLTD.Address & " - " & rOPN.Address & "<-------- Match"
Exit For
End If
End If
Next rOPN
End If
Debug.Print rLTD.Address & "is not a number"
Next rLTD

' rOPN.Range("A" & rOPN.Row & ":Z" & rOPN.Row).Copy rLTD.Range("A" & ltdLastRow & ":Z" & ltdLastRow)
' ltdLastRow = LTD_xls.Range("A1").End(xlDown).Row
Application.ScreenUpdating = True
End Sub
[/vba]

This is what i get in my debug window:
[vba]
$A$10 - $A$24
$A$10 - $A$25
$A$10 - $A$26
$A$10 - $A$27
$A$10 - $A$27<-------- Match
$A$10is not a number
$A$11 - $A$2
$A$11 - $A$2<-------- Match
$A$11is not a number
$A$12 - $A$2
$A$12 - $A$3
$A$12 - $A$3<-------- Match
$A$12is not a number
$A$13 - $A$2
$A$13 - $A$3
$A$13 - $A$4
$A$13 - $A$4<-------- Match
$A$13is not a number
$A$14 - $A$2
$A$14 - $A$3
$A$14 - $A$4
$A$14 - $A$5
$A$14 - $A$5<-------- Match
$A$14is not a number
$A$15 - $A$2
$A$15 - $A$3
$A$15 - $A$4
$A$15 - $A$5
$A$15 - $A$6
$A$15 - $A$6<-------- Match
$A$15is not a number
$A$16 - $A$2
$A$16 - $A$3
$A$16 - $A$4
$A$16 - $A$5
$A$16 - $A$6
$A$16 - $A$7
$A$16 - $A$7<-------- Match
$A$16is not a number
$A$17 - $A$2
$A$17 - $A$3
$A$17 - $A$4
$A$17 - $A$5
$A$17 - $A$6
$A$17 - $A$7
$A$17 - $A$8
$A$17 - $A$8<-------- Match
$A$17is not a number
$A$18 - $A$2
$A$18 - $A$3
$A$18 - $A$4
$A$18 - $A$5
$A$18 - $A$6
$A$18 - $A$7
$A$18 - $A$8
$A$18 - $A$9
$A$18 - $A$9<-------- Match
$A$18is not a number
$A$19 - $A$2
$A$19 - $A$3
$A$19 - $A$4
$A$19 - $A$5
$A$19 - $A$6
$A$19 - $A$7
$A$19 - $A$8
$A$19 - $A$9
$A$19 - $A$10
$A$19 - $A$10<-------- Match
$A$19is not a number
$A$20 - $A$2
$A$20 - $A$3
$A$20 - $A$4
$A$20 - $A$5
$A$20 - $A$6
$A$20 - $A$7
$A$20 - $A$8
$A$20 - $A$9
$A$20 - $A$10
$A$20 - $A$11
$A$20 - $A$11<-------- Match
$A$20is not a number
$A$21 - $A$2
$A$21 - $A$3
$A$21 - $A$4
$A$21 - $A$5
$A$21 - $A$6
$A$21 - $A$7
$A$21 - $A$8
$A$21 - $A$9
$A$21 - $A$10
$A$21 - $A$11
$A$21 - $A$12
$A$21 - $A$12<-------- Match
$A$21is not a number
$A$22 - $A$2
$A$22 - $A$3
$A$22 - $A$4
$A$22 - $A$5
$A$22 - $A$6
$A$22 - $A$7
$A$22 - $A$8
$A$22 - $A$9
$A$22 - $A$10
$A$22 - $A$11
$A$22 - $A$12
$A$22 - $A$13
$A$22 - $A$13<-------- Match
$A$22is not a number
$A$23 - $A$2
$A$23 - $A$3
$A$23 - $A$4
$A$23 - $A$5
$A$23 - $A$6
$A$23 - $A$7
$A$23 - $A$8
$A$23 - $A$9
$A$23 - $A$10
$A$23 - $A$11
$A$23 - $A$12
$A$23 - $A$13
$A$23 - $A$14
$A$23 - $A$14<-------- Match
$A$23is not a number
$A$24 - $A$2
$A$24 - $A$3
$A$24 - $A$4
$A$24 - $A$5
$A$24 - $A$6
$A$24 - $A$7
$A$24 - $A$8
$A$24 - $A$9
$A$24 - $A$10
$A$24 - $A$11
$A$24 - $A$12
$A$24 - $A$13
$A$24 - $A$14
$A$24 - $A$15
$A$24 - $A$15<-------- Match
$A$24is not a number
$A$25 - $A$2
$A$25 - $A$3
$A$25 - $A$4
$A$25 - $A$5
$A$25 - $A$6
$A$25 - $A$7
$A$25 - $A$8
$A$25 - $A$9
$A$25 - $A$10
$A$25 - $A$11
$A$25 - $A$12
$A$25 - $A$13
$A$25 - $A$14
$A$25 - $A$15
$A$25 - $A$16
$A$25 - $A$17
$A$25 - $A$18
$A$25 - $A$18<-------- Match
$A$25is not a number
$A$26 - $A$2
$A$26 - $A$3
$A$26 - $A$4
$A$26 - $A$5
$A$26 - $A$6
$A$26 - $A$7
$A$26 - $A$8
$A$26 - $A$9
$A$26 - $A$10
$A$26 - $A$11
$A$26 - $A$12
$A$26 - $A$13
$A$26 - $A$14
$A$26 - $A$15
$A$26 - $A$16
$A$26 - $A$17
$A$26 - $A$18
$A$26 - $A$19
$A$26 - $A$19<-------- Match
$A$26is not a number
$A$27 - $A$2
$A$27 - $A$3
$A$27 - $A$4
$A$27 - $A$5
$A$27 - $A$6
$A$27 - $A$7
$A$27 - $A$8
$A$27 - $A$9
$A$27 - $A$10
$A$27 - $A$11
$A$27 - $A$12
$A$27 - $A$13
$A$27 - $A$14
$A$27 - $A$15
$A$27 - $A$16
$A$27 - $A$17
$A$27 - $A$18
$A$27 - $A$19
$A$27 - $A$20
$A$27 - $A$20<-------- Match
$A$27is not a number
[/vba]

For some reason search starts with "A10" instead of "A2", why?
And even when match occures, cell synchronization is not acting as it should, i doesn't update each row.

[Bob Phillips]

Where is the other workbook?

[next]

open.xls, it should be attached to the head.
I wasn't able to upload a second sheet, but they have the same layout, so please just update few values and saveAs "LTD Open.xls".

Thanks.

[Bob Phillips]

I think the immediate window is getting sturated, so it is clearing out the early stuff. It certainly gets written there to start with.

[Bob Phillips]

Here is your problem.

Change

[vba]

rOPN.Range("L" & rOPN.Row & ":Z" & rOPN.Row).Copy rLTD.Range("L" & rLTD.Row)
[/vba]

to

[vba]
rOPN.Offset(0, 11).Resize(1, 15).Copy rLTD.Offset(0, 11)
[/vba]

[next]

Thanks, it's working now! I guess i need to get used to Offset's and Resize's.

Thanks again.

[Bob Phillips]

What you don't need to do is to iterate through the cells getting a cell object, and then pffset further using the row number, it skews the address.