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
  • 4
  • 3
LVL 41

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.
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 41

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 41

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

831 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