Solved

VBA - Excel 2007 - Copy Contents and Validate

Posted on 2012-04-04
22
456 Views
Last Modified: 2012-04-06
Me again.. Almost done :)

I have a range named 'MoveToRetrieval' on a tab named 'Bid Template' and it's I15 to Q3014.  I created a button called 'Process to Retrieval'.  Now it gets tricky.  During or before the process I need some validation done on Zip Code column which is I15 to I3014.  First they all need to be 5 chars.  If they are not 5 chars, then fill in a 0 until it's 5 chars.  Thus 456 would become 00456 and 2345 would become 02345.  Next they all need to be TEXT not NUMERIC.  I think adding the single quote ' should work. If they need to be separate buttons, that's ok. Users can click more than one :)  Maybe a 'Correct Zip Code' button??

The destination range is on a tab called Retrieval.  That range is named 'FromBidTemplate'. It's range is C15 to K3014.  Columns are in same order so:

column I to C
column J to D
column K to E
column L to F
column M to G
column N to H
column O to I
column P to J
column Q to K

Also need cells A15 to A3014 from 'Bid Template' tab copied to cells B15 to B3014 on 'Retrieval' tab with just cell text and no formatting etc.  Also only the cell contents are copied.  Don't want any format changes etc...

I really appreciate all the help everyone has provided.
0
Comment
Question by:elwayisgod
  • 13
  • 8
22 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37810150
is this a one off?  will the range always be the same?
0
 

Author Comment

by:elwayisgod
ID: 37810639
For now range will.always be same.
0
 

Author Comment

by:elwayisgod
ID: 37810677
If i have the source and target ranges named coukdnt they theoretically change if i change the  ranges within those named ranges?   Can you copy fron/to a named range?  Or do you have to specify cells?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37811829
Try this.
Option Explicit

Sub MoveAndValidate()
' MoveToRetrieval -I15:Q3014
' FromBidToTemplate- C15:K3014

Dim rngZips As Range

    Worksheets("Bid Template").Range("MoveToRetrieval").Copy

    Worksheets("Retrieval").Range("C15").PasteSpecial xlPasteValues

    Worksheets("Bid Template").Range("A15:A3014").Copy

    Worksheets("Retrieval").Range("B15").PasteSpecial xlPasteValues

    Set rngZips = Worksheets("Retrieval").Range("C15:C3014")

    ' insert column before zip code column
    rngZips.Insert

    Set rngZips = rngZips.Offset(, -1)


    With rngZips
        .Formula = "=TEXT(D15, ""00000"")"    ' add leading zeros to zips if required
        .NumberFormat = "@"                    ' format column as text to keep zeros
        .Value = .Value                            ' replace formula with value
        .Offset(, 1).Delete                        ' delete original zip code column
    End With

    ' restore FromBidTemplate
    rngZips.Resize(, 9).Name = "FromBidTemplate"


End Sub

Open in new window

CopyAndValidate.xlsm
0
 

Author Comment

by:elwayisgod
ID: 37812001
Here's actual template...I'm getting some errors.  I put it in module 5
EE-Template-NewV23-Stripped-Work.zip
0
 
LVL 33

Expert Comment

by:Norie
ID: 37812507
The MoveToRetrieval range is on the Input Worksheet not the Bid Template workbook.
0
 

Author Closing Comment

by:elwayisgod
ID: 37812924
Perfect.  Posting another question for next step.
0
 

Author Comment

by:elwayisgod
ID: 37814758
Hey Norie,

Just realized this was you.  Anyways, question.  When I copy using this solution, how can I have it only copy the rows it that actually exist in the 'BID_TEMPLATE' tab?  So I inserted 3 lines with Unique ID 1 to 3.  When I copy to Retrieval, the Unique ID fills all the way down to Row 3014 in Column B.  Also the Zip Code cleansing that is done, fills in '00000' all the way way down in Column C.  Could it stop at Unique ID 3 or Row 17?

Another nitpick that users noticed.  They may want to modify a Unique ID.  So if they copy a Unique ID row over from Retrieval to Master, if the Unique ID exists already, can it prompt them to overwrite Y or N?  Or if it's easy, just overwrite the existing Unique ID row? Right now it will append to it and then there will be multiple Unique ID's with same value.

Anyways, let me know. I'm trying to figure out how to do this part, but it might be beyond my abilities.
EE-Template-NewV26-Stripped-Work.zip
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816013
Is that what you meant in the question you posted after this one?

Anyway, what I would do is not use the named ranges or at least make them dynamic.
0
 

Author Comment

by:elwayisgod
ID: 37816248
I was looking into Dynamic... But hosed up my spreadsheet trying to implement them.  Some other guy got my data validation working.  That was pretty cool. Now I know how to use that.  The zip code thing works perfect :)
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816279
Just work out the last row of data in the code and copy only the range with data.

    LastRow = Worksheets("Bid Template").Range("I" & Rows.Count).End(xlUp).Row
    
    Set rngSrc = Worksheets("Bid Template").Range("I15:Q" & LastRow)
    
    rngSrc.Copy

Open in new window


PS What validation? Was it for the zip codes?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:elwayisgod
ID: 37816327
Validation was for all the columns that get copied to the retrieval tab.  I had to bounce them off a valid list as if there was invalid ones and one tried to get data from Essbase it would choke and hose up all the formatting etc.  Thus blocking them from putting in invalid members would solve that.  It might be a pain, but it's necessary.
0
 

Author Comment

by:elwayisgod
ID: 37816493
0
 

Author Comment

by:elwayisgod
ID: 37816520
I have no idea where to put that code :)
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816531
Replace line 9 in the last code with it.

You'll need to declare LastRow as Long and rngSrc as Range.
0
 

Author Comment

by:elwayisgod
ID: 37816542
I'm confused.  Where is this 'Line 9' located?  I'm looking at all the Modules and don't see where it makes sense on Line 9.  Or is it not code in a Module?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816585
There is no Line 9 in the code in VBA - look at the code on this page, the numbers are lined.
0
 

Author Comment

by:elwayisgod
ID: 37816757
Yes.  I only see 5 lines.  I'm so confused. Sorry, I'm misunderstanding something.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816764
The code in the solution has 30+ lines, that's what I was trying to refer to.
0
 

Author Comment

by:elwayisgod
ID: 37816787
Gotcah.. The 'MoveAndValidate' now looks like this though.  No wonder I was confused :)



Open in new window

Option Explicit

Sub MoveAndValidate()
' MoveToRetrieval -I15:Q3014
' FromBidToTemplate- C15:K3014

Dim rngZips As Range
Dim wksTemplate As Worksheet
Dim wksRetrieval As Worksheet
Dim rMoveToRetrieval As Range
Dim lastRow As Long
Dim r As Range
Dim rngNonBlank As Range
Dim rCheck As Range
Dim i As Long

    Set wksTemplate = Worksheets("Bid_Template")
    Set wksRetrieval = Worksheets("Retrieval")

    Set rMoveToRetrieval = wksTemplate.Range("MoveToRetrieval")
    lastRow = rMoveToRetrieval.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    If lastRow < rMoveToRetrieval.Cells(1, 1).Row Then Exit Sub    'nothing to move

    Set rngNonBlank = wksTemplate.Range("MoveToRetrieval").SpecialCells(xlCellTypeConstants)    'just work with rows that have non blank data

    'first validate that all entries have data.  Assume already that Unique Key has data, as the user is not supposed to be entering data there
    For Each r In rngNonBlank.EntireRow
        Debug.Print wksTemplate.Range(wksTemplate.Cells(r.Row, rMoveToRetrieval.Cells(1, 1).Column), wksTemplate.Cells(r.Row, wksTemplate.Cells(1, 1).Column + rMoveToRetrieval.Columns.Count - 1)).Address
        For Each rCheck In wksTemplate.Range(wksTemplate.Cells(r.Row, rMoveToRetrieval.Cells(1, 1).Column), wksTemplate.Cells(r.Row, rMoveToRetrieval.Cells(1, 1).Column + rMoveToRetrieval.Columns.Count - 1))
            Debug.Print rCheck.Address
            If rCheck.Value = vbNullString Then
                MsgBox "Invalid data at address: " & rCheck.Address & vbCrLf & vbCrLf & "You cannot move to retrieval without updating this value - please ensure there are NO blank values", vbCritical, "Aborting Move to Retrieval!"
                rCheck.Select
                Exit Sub
            End If
        Next rCheck
    Next r

    'move the data over for MoveToRetrieval section
    For Each r In rngNonBlank.Rows
        'move the data over for MoveToRetrieval section
        wksRetrieval.Range("C15").Resize(1, r.Columns.Count).Offset(i, 0).Value = r.Value

        'move column A over - the unique ID
        wksRetrieval.Range("B15").Offset(i, 0).Value = wksTemplate.Cells(r.Row, "A").Value

        'move column I over - the zip
        wksRetrieval.Range("BB15").Offset(i, 0).Value = wksTemplate.Cells(r.Row, "I").Value

        i = i + 1
    Next r

    'format zip codes
    With wksRetrieval.Range(wksRetrieval.Range("C15"), wksRetrieval.Range("C" & lastRow))
        .NumberFormat = "General"
        .Formula = "=TEXT(BB15,""00000"")"
        .NumberFormat = "@"
        .Value = .Value
    End With

    wksRetrieval.Range(wksRetrieval.Range("Z15"), wksRetrieval.Range("Z" & lastRow)).ClearContents

End Sub


Open in new window

0
 

Author Comment

by:elwayisgod
ID: 37816791
I'm sorry.  Original code changed a tad.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37816817
A tad?

Can't remember any looping in the original code, but then again this seems to be doing some sort of check for blanks.:)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now