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.
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delete all words in cell more than 5 words 7 48
Automating Excel Weekly Report 13 64
Subtotal clarification and between two sheets comparison 9 17
Filling Blank Cells 14 21
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 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

27 Experts available now in Live!

Get 1:1 Help Now