Solved

VBA - Excel 2007 - Validate Columns

Posted on 2012-04-05
7
516 Views
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.

1.

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.

2.

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.
EE-Template-NewV26-Stripped-Work.xlsm
0
Comment
Question by:elwayisgod
  • 4
  • 3
7 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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!"
                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


See attached.

Dave
EE-Template-NewV26-Stripped-Work.xlsm
0
 

Author Comment

by:elwayisgod
Comment Utility
Thats fricking sweet!!!
0
 

Author Closing Comment

by:elwayisgod
Comment Utility
Exactly what was needed.  Thanks.  I'm almost there.  Few more tweeks and I'm done, hopefully.  Users keep throwing me monkey wrench.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Thanks - PS line 62 needs to address column BB not Z.  

Cheers,

Dave
0
 

Author Comment

by:elwayisgod
Comment Utility
So:


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

Expert Comment

by:dlmille
Comment Utility
Yep
0
 

Author Comment

by:elwayisgod
Comment Utility
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 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

9 Experts available now in Live!

Get 1:1 Help Now