Case Statement Help

It basically examines the value that's returned from vValidation(i). It appears that there are at least three possible values and each is given its own 'Case'. The First case then says "If the lowercase value of the 1st character of rCheck.Value is not equal to "q" then pre-pend "q" to the value. The other two are similar.

If rCheck points to the cell itself, and it looks like it does, than that code changes the value, dependent on the vValidation(i) result. So it is not a check for a particular column, but for the result of a function.

So is this where it's actually changing the value of a '1' to 'q1' for instance?  Attached is the Template.  Columns  N, O and P on the Bid_Template tab.  Person who helped me create this isn't responding anymore. Thus need help.
Template-Final-020513-v4.xlsm

Yes it is. What do you need help with other than that?

Basically original requirement was user would enter a number 1 to 11 in column N.  However now they need ability to enter 'All Qty' too.  If they do, that does not get the 'q' prefix.....  Does that make sense?

I'll tell you in a few minutes after I look at the workbook.

The code is password protected so you'll need to tell me what it is if you want me to look at the code.

email me at [removed] and i'll send code?

Sorry, can't. If you post it here you can always change it to something else in your own version of the WB.

Yeah.  'bumblebee'

Module 5

In the RetrievalDataValidation module add "All Qty" to the constant.

Public Const strValidRanges = "Zip_Range,Segment_Range,CustomerType_Range,contractYear_Range,Recycle_Range,Qty_Range,Containe_Range,Commercial_Range,weight_Range, All Qty"

Or you could add a default to the Case statement that looks like this but I'd have to look a lot deeper into the code to know if that's dangerous or not.


                Select Case vValidation(i)
                Case "Qty_Range":
                    If Left(LCase(rCheck.Value), 1) <> "q" Then rCheck.Value = "q" & rCheck.Value
                Case "Containe_Range":
                    If Left(LCase(rCheck.Value), 1) <> "y" Then rCheck.Value = "y" & rCheck.Value
                Case "Commercial_Range":
                    If Left(LCase(rCheck.Value), 1) <> "f" Then rCheck.Value = "f" & rCheck.Value
                Case Else: If… 'whatever you want to do here
                End Select

Well those ranges are on the 'Outline' tab aren't they?

So on the 'Outline' tab the valid range is from G3 to G24.  Notice how there is a pop up window on the BID_Template tab if you click on cell N15... Where is that window derived from?  That might hold the key?  Forgot where/how this was done.....

Yes they are but the values that the Select case statement is working with come from strValidRanges which is the result of splitting up the vValidation constant.

It most likely from Data Validation.

OK. It is Data Validation.  So i increased the range and it still wont take the value.

Martin would you be interested in side work for a rate?

Sorry, but that's also against EE's rules but I'll be happy to help you here and in future questions.

OK.  There's just alot I don't understand how to fix and not sure back and forth on here will work.. We can try.

I'm used to back and forth. Take a look at this thread that I'm involved with:)

So on the 'Retrieval' tab the 'Move selected rows to 'Master' tab is only moving columns B to U.  I need it to move columns B to AC now.  I went through all the modules and looked for ':U' and changed it to ':AC' and that didn't work.  So somewhere I can't seem to find where this button is defined and which code it uses.,

OK.  I say I ask one question at a time and start new question for each issue?  That way it's worth the points you will deserve ?

I'm working on something else right now that's very involved so give me a 2 or 3 hours to get back to you.

It's in Module 1 here is code....

Option Explicit

Sub TransferData()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngData As Range
Dim rngResult As Range
Dim rngHeaders As Range
Dim cl1 As Range
Dim cl2 As Range
Dim NoCols As Long
Dim rngDst As Range
Dim rngCrit As Range
Dim lastRow As Long
Dim i As Long
Dim r As Range
Dim rng As Range
Dim vDuplicate As Variant

    Application.ScreenUpdating = False

    Call unProtectWorkbookAndSheets
   
    Select Case Application.Caller
    Case "Button 1"
        Set wsDst = Worksheets("Master")
        Set wsSrc = Worksheets("Retrieval")

    Case "Button 2"
        Set wsDst = Worksheets("Retrieval")
        Set wsSrc = Worksheets("Master")
    End Select

    With wsSrc

        lastRow = .Range("C" & Rows.Count).End(xlUp).Row

        If lastRow < 15 Then GoTo gracefulExit

        wsSrc.Range("A15").EntireRow.Insert xlShiftDown

'Changed from U to AC 2/5/13 S. Toteve
        Set rngData = .Range("A15:AC" & lastRow + 1)

    End With

    Set rngHeaders = rngData.Rows(1)

    NoCols = rngData.Columns.Count

    rngHeaders.Cells(1, 1).Value = "Field1"

    rngData.Cells(1, 1).AutoFill rngHeaders.Rows(1), xlFillDefault

    With wsDst

        lastRow = .Range("C" & Rows.Count).End(xlUp).Row + 1

        If lastRow = 14 Then lastRow = lastRow + 1

        Set rngDst = .Range("B" & lastRow)

    End With
   
    'clears any Retrieval data with same Unique ID as master that is being moved over
    If wsSrc.Name = "Master" Then
        For Each r In wsSrc.Range(rngData.Columns(1).Address)
       
            If InStr(r.Value, "Move") <> 0 Then 'wants to be moved
                'check to see if duplicate exists in Retrieval
                vDuplicate = Evaluate("=MATCH(" & r.Offset(, 1).Value & ",Retrieval!$B$15:$B$" & lastRowPrj & ",0)")
                If Not IsError(vDuplicate) Then 'duplicate DOES exist, and vUnique holds the row
                    With wsDst.Range("A14:X14").Offset(vDuplicate, 0)
                        .ClearContents
                        .Interior.ColorIndex = xlNone
                    End With
                End If
            End If
        Next r
    End If
   
    'now set criteria for autofilter process, copying data to the destination sheet
    Set rngCrit = Worksheets("DoNotDelete").Range("A1:B2")

    rngCrit.Cells(1, 1).Value = "Field1"

    rngCrit.Cells(2, 1).Value = "Move to " & wsDst.Name

    rngCrit.Cells(1, 2).Value = "NotDuplicate"

    rngCrit.Cells(2, 2).Formula = "=IF(ISNA(MATCH(Retrieval!B16,Master!$B$15:$B$" & lastRowPrj & ",0)),TRUE,FALSE)"

    rngData.AdvancedFilter xlFilterCopy, rngCrit, rngCrit.Cells(1, 1).Offset(, 2), True

    rngHeaders.EntireRow.Delete

    lastRow = Worksheets("DoNotDelete").Range("C" & Rows.Count).End(xlUp).Row

    If lastRow = 1 Then
        Worksheets("DoNotDelete").Rows(1).Clear
    Else

        Set rngResult = Worksheets("DoNotDelete").Range("D2:W" & lastRow)

        rngResult.Interior.ColorIndex = xlNone

        For Each cl1 In rngResult.Columns(1).Cells
            For Each cl2 In rngData.Columns(2).Cells
                If cl2.Value = cl1.Value Then

                    cl2.Offset(, -1).Resize(, NoCols).ClearContents
                    cl2.Offset(, -1).Resize(, NoCols).Interior.ColorIndex = xlNone
                End If
            Next cl2
        Next cl1

        rngResult.Copy rngDst

        rngResult.Offset(, -3).Resize(, NoCols + 2).EntireColumn.Clear
    End If

    DataSortByID wsSrc
    DataSortByID wsDst
   
gracefulExit:

    Call protectWorkbookAndSheets

    Application.ScreenUpdating = True

    Application.CutCopyMode = False

End Sub

OK.  Sounds Good.

Found it.  Changed:

 Set rngResult = Worksheets("DoNotDelete").Range("D2:W" & lastRow)

To:

 Set rngResult = Worksheets("DoNotDelete").Range("D2:AE" & lastRow)

That accomodates the extra 8 columns.  Apparently there was a hidden worksheet named 'DoNotDelete' that is used for some processing.

Okay then, does that resolve this question?

So now we are just back to the original issue of how the BID_Template tab can accept 'All Qty' , 'All Yds' and 'All Freqs' in columns N to P.  For now I'm just using valid numbers but I will need to ge this to work.

Okay will get back to you.

I can't figure out where this formula resides.  I'm in data validation for that column and I see nothng representing a formula

If you select one of the cells and then go to Data|Data validation you'll see this

If you then go to Formulas|Name Manger you'll see this.

In the first picture you'll see that the validation for the cell is a List and that list's name is Qty-Range. In the second picture you see a list of all the named ranges in the WB and when Qty_Range is selected you see in 'Refers To' the formula that defines where the data comes from. In the formula I wrote it is from the 'Outline' sheet, column G from cell 3 to the last entry.

Perfect

You're welcome and I'm glad I was able to help. If you have new questions about this workbook please also post their URLs here, thanks.

Marty - MVP 2009 to 2012

https://www.experts-exchange.com/questions/28023591/Column-Filtering-via-VBA.html