Solved

Enhancement to Conditional Field Selection

Posted on 2011-03-15
13
316 Views
Last Modified: 2012-05-11

EE Professionals,

I have a great little app. written for me by SiddharthRout which I'd like a slight enhancement for.  The Macro works great however, I'd like the ability to have a cell in the spreadsheet that actually determines how many "falses" are necessary to shut down the "further questions" (currently set at 3 ((hardcoded)).  I think it's an enhancement to this part of the code:

        On Error Resume Next
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 And Range("A" & i).Offset(-1).Value = "False" And _
            Range("A" & i).Offset(-2).Value = "False" And _
            Range("A" & i).Offset(-3).Value = "False" Then
                Target.Value = ""
                Application.EnableEvents = True
                Exit Sub

I have put the change cell (IN RED) in the file example so you could see what I'm attempting to do.  It's set at 3 in the code.  I want to vary the number of "falses" that must be consecutively entered in order to shut down the rest of the responses within the section  (for clarification, there are 12 sections w/15 responses/section but I believe they are all affected by the code above).

Thank you in advance.

Bright01
Conditional-Input-Macro.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35136561
replace the complete 'for ... next' loop with
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                valid = False
                For response = 1 To Range("L3")
                    valid = (Range("A" & i).Offset(-response).Value <> "Onwaar")
                    If valid Then Exit For
                Next response
                    If valid = False Then
                    Target.Value = ""
                    Application.EnableEvents = True
                    Exit Sub
                End If
            End If
        Next

Open in new window

0
 

Author Comment

by:Bright01
ID: 35137045
Akoster,

Thank you for your quick response.  Several questions after testing;

1.) What do you do about the         On Error GoTo 0    command at the end of the "for....next" loop?  Do I keep that in or delete it?  You can see this in the actual code.

2.) When I substitute the code, go to the worksheet, put in 4, it doesn't stop at 4 consecutive "Falses"; even when I shut down Excel and bring it back up.

Can you look at the sample I sent, make the adjustments you have suggested, test it once or twice and send it back?  I think we're close.

Thank you in advance,

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35137344
B,

Since Akoster is already working on it, let him give a try first :) I am sure he will be able to crack it. If you guys get stuck, lemme know and I will step in :)

Sid
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 19

Accepted Solution

by:
Arno Koster earned 500 total points
ID: 35147034
Bright,

1) it is definitely a good idea to keep the "On error goto 0" statement as long as one of the "On error resume next" or "On error goto [...]" statements are used.
    These statements determine how the macro handles exceptions and should always be used in pairs.

2) that's what I did, changed the code and when selecting new values, they were removed when they made 4 consecutive falses.
I see now that I made a slight mistake while posting the question :

considering me working on a dutch language office suite, and "False" translating to "Onwaar", you can see my mistake in line 6 :
you should be using this code
        '~~> To check for 3 false conditions
        For i = j To k Step -1
            If Len(Trim(Range("A" & i))) <> 0 Then
                valid = False
                For response = 1 To Range("L3")
                    valid = (Range("A" & i).Offset(-response).Value <> "False")
                    If valid Then Exit For
                Next response
                    If valid = False Then
                    Target.Value = ""
                    Application.EnableEvents = True
                    Exit Sub
                End If
            End If
        Next

Open in new window


sorry !
0
 

Author Closing Comment

by:Bright01
ID: 35147553
Excellent JOB!  Thank you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35147617
Good Job akoster :)

Sid
0
 

Author Comment

by:Bright01
ID: 35151053
Hey guys; I have a quick request.  I'm now trying to incorporate the model that both of you have contributed to; into my production system.  I'm having difficulty in understanding the code and trying to line up the right fields.  In my production copy, I have several rows in-between each section and I don't know how "case targets" work.  Also, if you look at your reference to j= and k=, I'm assuming that that is the "To/Be" cells/fields as j and k are the columns in the To/Be sections. Finally, I'm getting an error with regard to  Private Sub Worksheet_Change(ByVal Target As Range) because I have another macro named the same thing.  Can I combine them or change the name?

Thanks for the additional help.

Best regards,

B.

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35155184
the case target statements function like a big if ... then ... elseif ... then ... elseif ... then ... else ... end if

eg.

select case [question]
  case [answer1]
    [solution1]
  case [answer2]
    [solution2]
  [...]
end select

translates to

if [question] = [answer1] then
  [solution1]
elseif [question] = [answer2] then
  [solution2]
[...]
end if
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35155200
"j" and "k" indicate from which row to which row the false conditions are to be checked.
eg. j = 26, k = 15 indicates that questions 4 up to 15 are to be checked. As the check is done on column A, it would be the "As/Is" that is checked.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35155222
The worksheet_change function is triggered by any change in the worksheet. That is : when you change something in the worksheet containing this macro code, the macro will be executed.

So to answer you question : it depends on where the macro code is placed, and what you want to do with it.
Having the same macro on different worksheets is no problem as the macro will only function in the worksheet it is placed in.
When you have two or more worksheet_change macro's in a single worksheet, you will have to combine them in order for it to work.
When you change the name of one of those macro's, it will not be automatically called and you will have to make a button for it or make sure that it is called from another macro.
0
 

Author Comment

by:Bright01
ID: 35157141
Akoster,

Thank you for your response.  I can handle the Worksheet_Change function part.  Can you take a quick look at your code?  I think we may have confused the AS/IS selection with the TO/BE results in the macro (i.e. J and K are in the TO/BE column and the selection of "FALSE" X # of times is in the AS/IS selection).  

Finally, I need a way to identify how to discriminate between the extra spaces I have in my production copy.  In other words, in the production copy, I have 3 to 4 spaces between each CATOGORY.  The numbering in the code makes it difficult to figure out how to stop and start the logic within each Category.   I'm happy to open a related question if so advised.

Thank you,

B.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35157370
About the J&K :

the original code checks if the change is somewhere in the As/Is column (between cells A12 & A191).
It loops through a number of rows (set by the j & k variables) from the As/Is column, when an arbitrarily chosen number of rows (in the original code : 3 rows) contain values "False" in this column, then the value will be cleared.

When the change is not in the As/Is column, but instead in the To/Be column, then it checks for values "True" or N/A, in which case the value is cleared.


[b]the J & K variables do not act on the To.Be column.[/b]

On the spaces,
you could design a public function that replaces multiple spaces with single spaces or underscores or something.
For leading or trailing spaces (in front of a text or after a text), you can use the "TRIM" function.
eg :
Public Function cleanup(value As String) As String
Dim temp As String
    
    temp = Trim(value)
    While InStr(temp, "  ")
        temp = Replace(temp, "  ", " ")
    Wend
    cleanup = temp

End Function

Open in new window

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35157390
although indeed the latter question on the spaces might have been better situated in another (related) question.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

695 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