Solved

Case Statement Help

Posted on 2013-02-05
38
399 Views
Last Modified: 2013-02-07
Hi,

I have this piece of code in one of my modules.  Basically in column N if user puts a '1' in there it will change it to 'q1' which is good.  Column O adds a 'y' and P adds an 'f'.  Not sure this is the part that does it or just validates it?  What exactly does this say?

                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
                End Select
0
Comment
Question by:elwayisgod
  • 19
  • 17
38 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857510
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.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 38857513
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.
0
 

Author Comment

by:elwayisgod
ID: 38857522
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857528
Yes it is. What do you need help with other than that?
0
 

Author Comment

by:elwayisgod
ID: 38857542
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857545
I'll tell you in a few minutes after I look at the workbook.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857552
The code is password protected so you'll need to tell me what it is if you want me to look at the code.
0
 

Author Comment

by:elwayisgod
ID: 38857557
email me at [removed] and i'll send code?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857565
Sorry, can't. If you post it here you can always change it to something else in your own version of the WB.
0
 

Author Comment

by:elwayisgod
ID: 38857570
Yeah.  'bumblebee'
0
 

Author Comment

by:elwayisgod
ID: 38857573
Module 5
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857601
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"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857607
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
0
 

Author Comment

by:elwayisgod
ID: 38857609
Well those ranges are on the 'Outline' tab aren't they?
0
 

Author Comment

by:elwayisgod
ID: 38857647
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.....
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857654
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857660
It most likely from Data Validation.
0
 

Author Comment

by:elwayisgod
ID: 38857788
OK. It is Data Validation.  So i increased the range and it still wont take the value.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:elwayisgod
ID: 38857822
Martin would you be interested in side work for a rate?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857837
Sorry, but that's also against EE's rules but I'll be happy to help you here and in future questions.
0
 

Author Comment

by:elwayisgod
ID: 38857864
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857865
I'm used to back and forth. Take a look at this thread that I'm involved with:)
0
 

Author Comment

by:elwayisgod
ID: 38857872
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.,
0
 

Author Comment

by:elwayisgod
ID: 38857873
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 ?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857878
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.
0
 

Author Comment

by:elwayisgod
ID: 38857884
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
0
 

Author Comment

by:elwayisgod
ID: 38857886
OK.  Sounds Good.
0
 

Author Comment

by:elwayisgod
ID: 38857908
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857910
Okay then, does that resolve this question?
0
 

Author Comment

by:elwayisgod
ID: 38857911
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857914
Okay will get back to you.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38858039
The values for the data validation dropdown on those columns on the BID_Template sheet comes from column G on the Outline sheet. If I change one of those values the change is reflected in the dropdown list but if I add a new value it doesn't show up.

The data validation formula in those cells is

=Outline!$G$3:INDEX(Outline!$G:$G,MATCH(99^99,Outline!$G:$G))

I'll be honest with you and tell you that I don't understand that formula, but if you change it to

=OFFSET(Outline!$G$3,0,0,COUNTA(Outline!$G:$G),1)

which is similar to data validation formulas in another sheet that I'm familiar with it works.

Save the old formula and substitute the new one, add the new values and give it a try.
0
 

Author Comment

by:elwayisgod
ID: 38859525
I can't figure out where this formula resides.  I'm in data validation for that column and I see nothng representing a formula
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38859834
If you select one of the cells and then go to Data|Data validation you'll see this
Data|Data Validation
If you then go to Formulas|Name Manger you'll see this.
Formulas|Name Manager
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.
0
 

Author Closing Comment

by:elwayisgod
ID: 38864144
Perfect
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38864263
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
0
 

Author Comment

by:elwayisgod
ID: 38864395
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 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

19 Experts available now in Live!

Get 1:1 Help Now