VBA - Excel 2007 - Validate Columns

Posted on 2012-04-05
Last Modified: 2012-06-21
OK.  On the attached file on the 'BID_TEMPLATE' tab I need some validation done before users click the 'Copy Rows to Retrieval Tab' button.


On the 'Copy Rows to Retrieval' button, I need it modified to only copy over the rows that actually exist between rows 15 and 3014.  So if rows 15 to 18 exist then only those rows copy over and the Unique ID only goes from 1 to 4 and the Zip Code stops too.  So all rows 19 and below are empty.  Not sure if can handle it if rows 15 to 18 have data and then row 22 does too, skipping 19 to 21.  I guess if possible bring row 22 over too so the unique id's would be 1 to 4 and 8.


On the 'Bid Template' tab before the users click the Copy button I need some type of validation that whatever they put in the cells for each column that are to be copied actually have valid data in them.  So each value in the columns are compared to the list of valid choices that are located on the 'Outline' tab.  So if any cell has a value in it that is NOT in the list it will not allow them to copy and will give them a error letting them know which value in on the 'BID_Template' is invalid.  If a invalid member is copied over and they try and retrieve via Essbase is hoses up the retrieval tab big time.  So need some type of validation here first.  Now not sure it's just added to the Copy button before it gets to the Copy part or if it's better to have a separate 'Validate' button..... Either way is fine.

If you want these as two questions and 500pts ea, no problems.
Question by:elwayisgod
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 37815526
Ok - a bit of a rewrite but you also gave a good description of what you wanted.

The app copies over ONLY rows where the master retrieval range name has data using the SpecialCells approach to find constants.  As long as no one typed a space in a cell, this should not be a problem - data validation prevents that (more on that in a moment).

The zips are copied over to an empty column (range BB) and then your formula to pad the zip code is applied and reformatted then range BB associated rows are cleared.

Finally, I added dynamic ranges to all your data validation ranges that you had defined in Outline.  As a result, the user is prevented from typing an invalid value there.  So, all we need to do is check to see that data is entered in the range, prompting the user accordingly, as any data entered must be valid thanks to the data validation feature.

Data validation was created on all the columns in the master retrieval range, using the dynamic ranges.  just click on one of the cells in the master retrieval area and you'll see that there's a drop down arrow.  If you go to the Data tab and look at Data Validation you'll see how that's setup.  Also, if you look at the range names, for each of those defined in each column, you'll see the basic formula, re:

Zip_Range =Outline!$B$3:INDEX(Outline!$B:$B,MATCH(REPT("z",20),Outline!$B:$B))

Basically the top left is anchored with $B:$3 (those dollar signs are important).  The rest of the formula on the other side of the colon : anchors the bottom right.  As we're looking for the last text occurrence in this column B, we can use the match function to compare a large string and not finding one (match thinks the list is sorted) it give you the next highest which would be the last cell) then index kicks in to return the address in column B at that location that the match found.

Here's the code:
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!"
                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

See attached.


Author Comment

ID: 37815980
Thats fricking sweet!!!

Author Closing Comment

ID: 37815985
Exactly what was needed.  Thanks.  I'm almost there.  Few more tweeks and I'm done, hopefully.  Users keep throwing me monkey wrench.
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

LVL 42

Expert Comment

ID: 37816952
Thanks - PS line 62 needs to address column BB not Z.  



Author Comment

ID: 37817081

wksRetrieval.Range(wksRetrieval.Range("BB15"), wksRetrieval.Range("BB" & lastRow)).ClearContents
LVL 42

Expert Comment

ID: 37817087

Author Comment

ID: 37817148
OK.  Any chance you can take a look at my last question.  Need all my 'Moves' to overwrite if that Unique ID row exists in destination.  Allows users to change their pulls but keep same Unique ID.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 the scrolling table in Microsoft Excel using the INDEX function.

627 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