Solved

Enhancement to Conditional Field Selection

Posted on 2011-03-15
13
309 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
  • 7
  • 4
  • 2
13 Comments
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
Comment Utility
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
Comment Utility
Excellent JOB!  Thank you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Good Job akoster :)

Sid
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Bright01
Comment Utility
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:akoster
Comment Utility
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:akoster
Comment Utility
"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:akoster
Comment Utility
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
Comment Utility
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:akoster
Comment Utility
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:akoster
Comment Utility
although indeed the latter question on the spaces might have been better situated in another (related) question.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

11 Experts available now in Live!

Get 1:1 Help Now