VBA coding to allow/bypass formulas

In the following attached file, we are trying to pull the data from the "Revisions" sheet to the "Title_Frame_Register" sheet in the AU and AV columns and match-up with the "Layout Tab ID" in the right row.

If those columns had data in them, then the code would tell it to go to the next columns that have "Revision Date" and "Revision Description", which would be the AY and AZ columns in "Title_Frame_Register" sheet.  And so on.  

Right now the code is bypassing all the columns it needs to go in (that have formulas) and putting it at the end of "Title_Frame_Register" sheet. Any assistance offered would be greatly appreciated.

=======
Prior related question: http://rdsrc.us/3FLHyw
Title_Frame_Register.xls
regsampAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
The code in the Copy_Revision() routine uses .End(xlToLeft) method.  Unlike the prior question, the revision history cells now contain formulas.  The formulas are the reason the new revisions are so far out to the right.
regsampAuthor Commented:
"The formulas are the reason the new revisions are so far out to the right." Right, the end users have added all of these formulas after the fact now.

Can we alter the code to account for the formulas?
aikimarkCommented:
We can clear the content before we start moving the data.  How about that?

You can protect those cells, preventing the users from putting in those formulas.  How about that?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

regsampAuthor Commented:
The users are insisting the formulas must be in there.

From what the users are saying, if there is something in the Revision Date and Revision Description then AT (Revision No.)  will become a 1 and Checked by will fill in when there is a something in Revision Description.

Once these are filled in they want it to move to the next section. I guess from what I understand they don't want the content to clear or are you saying a different area?

Let me do an example below:

Prog.jpg
aikimarkCommented:
Try this
Sub Copy_Revision()
    Dim wksSrc As Worksheet, wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngFind As Range
    Const cMinRevCol As Long = 47
    
    Set wksTgt = Sheets("Title_Frame_Register")
    Set wksSrc = Sheets("Revisions")
    
    Application.ScreenUpdating = False
    Set rngSrc = wksSrc.Range("B3")
    Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
    Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
    On Error Resume Next
    For Each rng In rngSrc
        Do      '<<<GROUP
        
        Set rngFind = wksTgt.Columns(2).Find(rng.Offset(0, -1).Value)
        If rngFind Is Nothing Then
            Exit Do
        End If
        Set rngTgt = wksTgt.Range(wksTgt.Cells(rngFind.Row, cMinRevCol), _
                                    wksTgt.Cells(rngFind.Row, wksTgt.Columns.Count)). _
                                    Cells.SpecialCells(xlCellTypeConstants)
        If rngTgt Is Nothing Then
            Set rngTgt = wksTgt.Cells(rngFind.Row, cMinRevCol)
        Else
            Set rngTgt = rngTgt.Areas(rngTgt.Areas.Count).Cells(1, 1)
            Set rngTgt = rngTgt.Offset(0, 4)
        End If
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = wksSrc.Range(rng, rng.Offset(0, 1)).Value
        
        Loop While False        '>>>GROUP
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

regsampAuthor Commented:
It is close but see how the data that should have been with "C002 on the Layout Tab" went to the next section? We are trying to get it right under with the tab from "Revisions" to the
Title_Frame_Register" to the right Layout Tab and then once that section is filled, moved to the next section.

Prog2.jpg
regsampAuthor Commented:
It will look something like this. I am not sure why the "test" descriptions are so different in the spacing though:

Prog3.jpg
aikimarkCommented:
It is how the cells in that column are formatted.  The one that seems strange is centered instead of being left justified
regsampAuthor Commented:
Can the code be altered so that the Revision Dates and the Revision Descriptions are with the corresponding Layout Tab and then once that section is filled from Revisions, it will transfer to the next Revision Dates and Revision Descriptions?
aikimarkCommented:
I don't understand
regsampAuthor Commented:
It will look like the JPEG above but the comments will be the same format and then the next time it is run it will move to AY and AZ.  The code you posted is close but see how it puts it in the same row?

Prog2.jpg
regsampAuthor Commented:
So the output will look more like this but the test would be right under each other.

Prog3.jpg
aikimarkCommented:
C001 row with its related revisions to the right
C002 row with its related revisions to the right
etc.
regsampAuthor Commented:
Yes, that should be right.
aikimarkCommented:
That is what is happening during my tests.  I have no idea what you are seeing on your end.
regsampAuthor Commented:
Can you see the images I have posted? I am seeing the exact output below in the image.
C002 should be below C001.
Prog2.jpg
aikimarkCommented:
Yes.  I see your screenshots.  I had only tested using a single row in the revisions table.

This version of the routine seems to copy the data correctly.
Sub Copy_Revision()
    Dim wksSrc As Worksheet, wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngFind As Range
    Const cMinRevCol As Long = 47
    
    Set wksTgt = Sheets("Title_Frame_Register")
    Set wksSrc = Sheets("Revisions")
    
    Application.ScreenUpdating = False
    Set rngSrc = wksSrc.Range("B3")
    Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
    Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
'    On Error Resume Next
    For Each rng In rngSrc
        Do      '<<<GROUP
        
        Set rngFind = wksTgt.Columns(2).Find(rng.Offset(0, -1).Value)
        If rngFind Is Nothing Then
            Exit Do
        End If
        If Len(Trim(wksTgt.Cells(rngFind.Row, cMinRevCol).Value)) = 0 Then
            Set rngTgt = wksTgt.Cells(rngFind.Row, cMinRevCol)
        Else
            Set rngTgt = wksTgt.Range(wksTgt.Cells(rngFind.Row, cMinRevCol), _
                                    wksTgt.Cells(rngFind.Row, wksTgt.Columns.Count)). _
                                    Cells.SpecialCells(xlCellTypeConstants)
            Set rngTgt = rngTgt.Areas(rngTgt.Areas.Count).Cells(1, 1)
            Set rngTgt = rngTgt.Offset(0, 4)
        End If
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = wksSrc.Range(rng, rng.Offset(0, 1)).Value
        
        Loop While False        '>>>GROUP
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regsampAuthor Commented:
Doing a little more testing but it is looking very good so far.
regsampAuthor Commented:
That worked great. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.