asked on

Loop Synchronization

I have a workbook with 2 worksheets in it.
"MC Tracker Data" & "Tracking"

The control worksheet is "MC Tracker Data"

The "Tracking" worksheet gets deleted and replaced daily.

The key field for Sync is Column C in both worksheets. The columns stay static, but rows may be deleted or added.

"MC Tracker Data" row 3 has the corresponding column numbers from "Tracking" where the data is to be updated from.

"Tracking" columns "Y" to "AG" is the tough one. The #/# must be split to the corresponding columns in "MC Tracker Data" The snippets of code to break them is in Module 1.

I need to look up the key field in "MC Tracker Data"
Find the corresponding key field (Row) in "Tracking"
Update all columns associated by the mapping
Split the text in cells "Y to AG" and populate the columns in "MC Tracker Data"

I am terrible with loops, so help would be appreciated.....
Example-Code.xlsm

Kimputer

Does it have to be VBA though?
If on the MC Tracker Data sheet, cell G6 has this formula

=VLOOKUP(C6,Tracking!C:AG,23)

Open in new window

That's already the info you need? cell G7, has the same formula but ends with 24, etc etc
After you have the row then, copy formula down

If you have a really good reason to use VBA though, I will take a look at if after you respond.

DougDodge

ASKER

The worksheet "Tracking" gets deleted and replaced all the time, the quantity of rows changes all the time. Formulas do this...... REF! .......all the time......

Kimputer

Ref! will not happen if you change the formula to:

=IF(ISNA(VLOOKUP($C6,Tracking!$C:$AG,23)),"",VLOOKUP($C6,Tracking!$C:$AG,23))

(of course, again, make sure the next cell is 24. 25 etc, and then copy down.

DougDodge

ASKER

There is way more to the issue than just this update. VBA is the only way to handle it all.

Kimputer

Okay try this code:

Option Explicit

Sub TrackingToTracker()

Application.ScreenUpdating = False

Dim c
Dim i, j As Integer
Dim LastRow As Integer

LastRow = Sheets("MC Tracker Data").Cells(Rows.Count, 1).End(xlUp).row

For i = LastRow To 6 Step -1

    With Sheets("Tracking").Range("A1:AO9999")
        Set c = .Find(Sheets("MC Tracker Data").Cells(i, 3).Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            'MsgBox c.row
            For j = 0 To 8 Step 1
                'left
                Sheets("MC Tracker Data").Cells(i, 35 + (j * 3)).Value = Split(Sheets("Tracking").Cells(c.row, 25 + j).Value, "/")(0)
                'right
                Sheets("MC Tracker Data").Cells(i, 35 + (j * 3) + 2).Value = Split(Sheets("Tracking").Cells(c.row, 25 + j).Value, "/")(1)
            Next
        End If
    End With

Next

Application.ScreenUpdating = True

End Sub

Open in new window

DougDodge

ASKER

That part seems to work, but all the additional fields are missed.
"MC Tracker Data" Row 3 shows the mapping of the columns from "Tracking" that needs to be completed as well.

ASKER CERTIFIED SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DougDodge

ASKER

Interesting......

Using the code you provided it does nothing at all......

Kimputer

Did you delete all code and copy & pasted it again?
I compared previous code with current code (to make sure no typing errors were introduced), and it's basically the same, with extra copy code.
I tried it again with the file and latest code. All columns are filled with supposed data.
Please note it won't display anything directly, have some patience.

DougDodge

ASKER

I fixed it......

I had to change line:

LastRow = Sheets("MC Tracker Data").Cells(Rows.Count, 1).End(xlUp).row
to
LastRow = Sheets("MC Tracker Data").Cells(Rows.Count, 3).End(xlUp).row

Thanks

DougDodge

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for DougDodge's comment #a40564398

for the following reason:

Works like a charm now.....
Thank you....

Kimputer

Okay, but usually when you're satisfied with the answer, you award the expert points :(

Kimputer

Okay, but usually when you're satisfied with the answer, you award the expert points :(

DougDodge

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for DougDodge's comment #a40564431

for the following reason:

Sorry, the system did not pick up the grading when I closed it.....

DougDodge

ASKER

I have graded this "A" twice now.....