?
Solved

Enhancement to Conditional Field Selection

Posted on 2011-03-15
13
Medium Priority
?
317 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
Independent Software Vendors: 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!

 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 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