Solved

Loop Synchronization

Posted on 2015-01-21
16
85 Views
Last Modified: 2015-01-24
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
0
Comment
Question by:DougDodge
  • 8
  • 7
16 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40563835
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.
0
 

Author Comment

by:DougDodge
ID: 40563950
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......
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40563978
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.
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:DougDodge
ID: 40564073
There is way more to the issue than just this update. VBA is the only way to handle it all.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40564106
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

0
 

Author Comment

by:DougDodge
ID: 40564174
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.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40564184
Updated:

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
            For j = 1 To 5 Step 1
                Sheets("MC Tracker Data").Cells(i, j).Value = Sheets("Tracking").Cells(c.row, j).Value
            Next
            Sheets("MC Tracker Data").Range("P" & i).Value = Sheets("Tracking").Range("F" & c.row).Value
            Sheets("MC Tracker Data").Range("Q" & i).Value = Sheets("Tracking").Range("G" & c.row).Value
            Sheets("MC Tracker Data").Range("Y" & i).Value = Sheets("Tracking").Range("O" & c.row).Value
            Sheets("MC Tracker Data").Range("Z" & i).Value = Sheets("Tracking").Range("P" & c.row).Value
            Sheets("MC Tracker Data").Range("AA" & i).Value = Sheets("Tracking").Range("Q" & c.row).Value
            Sheets("MC Tracker Data").Range("AB" & i).Value = Sheets("Tracking").Range("R" & c.row).Value
            Sheets("MC Tracker Data").Range("AC" & i).Value = Sheets("Tracking").Range("S" & c.row).Value
            Sheets("MC Tracker Data").Range("AD" & i).Value = Sheets("Tracking").Range("T" & c.row).Value
            Sheets("MC Tracker Data").Range("AE" & i).Value = Sheets("Tracking").Range("U" & c.row).Value
            Sheets("MC Tracker Data").Range("AF" & i).Value = Sheets("Tracking").Range("V" & c.row).Value
            Sheets("MC Tracker Data").Range("AG" & i).Value = Sheets("Tracking").Range("W" & c.row).Value
            Sheets("MC Tracker Data").Range("AH" & i).Value = Sheets("Tracking").Range("X" & c.row).Value
            
            Sheets("MC Tracker Data").Range("BJ" & i).Value = Sheets("Tracking").Range("AH" & c.row).Value
            Sheets("MC Tracker Data").Range("BM" & i).Value = Sheets("Tracking").Range("AI" & c.row).Value
            Sheets("MC Tracker Data").Range("BN" & i).Value = Sheets("Tracking").Range("AJ" & c.row).Value
            Sheets("MC Tracker Data").Range("BO" & i).Value = Sheets("Tracking").Range("AK" & c.row).Value
            Sheets("MC Tracker Data").Range("BP" & i).Value = Sheets("Tracking").Range("AL" & c.row).Value
            Sheets("MC Tracker Data").Range("BQ" & i).Value = Sheets("Tracking").Range("AM" & c.row).Value
            Sheets("MC Tracker Data").Range("BR" & i).Value = Sheets("Tracking").Range("AN" & c.row).Value
           
            
            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


Lines could be shorter, but readability would suffer (also for tracking column matching errors)
0
 

Author Comment

by:DougDodge
ID: 40564365
Interesting......

Using the code you provided it does nothing at all......
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40564378
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.
0
 

Author Comment

by:DougDodge
ID: 40564398
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
0
 

Author Comment

by:DougDodge
ID: 40564431
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....
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40564430
Okay, but usually when you're satisfied with the answer, you award the expert points :(
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40564432
Okay, but usually when you're satisfied with the answer, you award the expert points :(
0
 

Author Comment

by:DougDodge
ID: 40565662
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.....
0
 

Author Comment

by:DougDodge
ID: 40565663
I have graded this "A" twice now.....
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA asking for a password 12 42
Excel 2013 - Formula to count texts with capital letters 5 54
Excel sort for duplicate records 7 72
Calculating T-SCORE inside Excel. 3 138
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question