Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

VBA - Excel 2007 - Validate Columns

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
elwayisgod
Asked:
elwayisgod
  • 4
  • 3
1 Solution
 
dlmilleCommented:
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
 
elwayisgodAuthor Commented:
Thats fricking sweet!!!
0
 
elwayisgodAuthor Commented:
Exactly what was needed.  Thanks.  I'm almost there.  Few more tweeks and I'm done, hopefully.  Users keep throwing me monkey wrench.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Cheers,

Dave
0
 
elwayisgodAuthor Commented:
So:


wksRetrieval.Range(wksRetrieval.Range("BB15"), wksRetrieval.Range("BB" & lastRow)).ClearContents
0
 
dlmilleCommented:
Yep
0
 
elwayisgodAuthor Commented:
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now