Sleeper: Nested if VBA

[ddesantis]

Hi,
I need to add an if statement to a code already up and running but I'm pretty new to vba
below the code

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
'Clear Range R and restore to values in Range P
If (Target.Address = "$P$6") Then
Range("$R$8:$R$65").ClearContents
Range("$R$8:$R$65").Value = Range("$P$8:$P$65").Value
'If range P changes, Change corresponding cell in Range R.
ElseIf Not Intersect(Target, Range("$P$8:$P$65")) Is Nothing Then 'Double negative = positive
If LCase(Target.Value) = "Apply same assumptions across currencies / asset classes" Or LCase(Target.Value) = "Apply different assumptions across currencies / asset classes" Then
Target.Offset(0, 2).Value = Target.Value
ElseIf LCase(Target.Value) = "please select" Then
Target.Offset(0, 2).Value = ""
End If
End If
End Sub

What I would need to add is: if P6="Apply same assumptions across currencies / asset classes" then whatever value I put in R8-R65 copy that into R69-R126, otherwise "please select".
it's worth noting that in R69-R126 I have a drop down menu (the same as in R8-R65)
I would appreciate any help on this!!
Daniele

[ddesantis]

I attached the file and made it simpler.

What I have to do is:

if C3=yes, range c5:c11 and c13:19 should show "please select". Cells will have a drop down menu where user can choose values between 5 and 7.
Each value selected will then replicated as follow:
c5:c11 into c24:c30 and c43:c49
c13:c19 into c32:c38 and c51:c57

if C3=no the user will see please select on each cell of the different ranges and will have to choose values between 5 and 7 for each cell.


Basically I want to say if the assumption is the same across currencies/asset classes just fill the template only for the first section; the other will be automatically populated;
otherwise if assumptions are different across currencies/asset classes the user has to fill in the template cell by cell


Hope it's clearer, I didn't realise straight away that my first post was a bit nebulous, apologies for that

[SamT]

Daniele,

Your first post had one bit of nebulosity, ie; where to add the next If.

Your attachment is the very poster child of nebulosity.

The difference between the two sources of information is that the first post's code is very, uber, extremely specific. Only the bit about where to place the next condition is vague.

And since there is not all that much information in both posts about what you are trying to accomplish, we can only guess as to how to improve that process.
(see my signture below.)

[Bob Phillips]

SamT,

Your post is the parent of obfuscation. Come on, give the bloke a break, and KISS.

[Kenneth Hobs]

I see what you need. I will post a solution in 3.5 hours at most.

Until then, and this is what I will do for this part, consider using a formula to do this:
c5:c11 into c24:c30 and c43:c49
c13:c19 into c32:c38 and c51:c57

The problem then is to just use the Change event to add the dropdowns if needed, or the "please select". Then one uses a Selection event to change a please select to a dropdown. Of course it might be easier to just add "please select" as the first entry in the dropdown list and then set that value as needed.

[ddesantis]

thanks Kenneth

[Kenneth Hobs]

Sorry, got tied up at lunch.

I modified the data validations on your cells and used a simple approach. Other ways can be used.

Private Sub Worksheet_Change(ByVal Target As Range)  
If Target.Address(False, False) <> "C3" Then Exit Sub
    Application.EnableEvents = False
    Select Case True
        Case Target.Value = "yes"
            Range("C5:C11,C13:C19").Value2 = "please select"
        Case Target.Value = "no"
            'Range("C5:C11,C13:C19").Value2 = "whatever"
        Case Else
            Reset
    End Select
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
  Reset
End Sub

Sub Reset()
  Range("C5:C11,C13:C19").Value = ""
End Sub

[ddesantis]

thanks Kenneth.

Quick questions:
1. is it possible to reset the range to "please select" via switching c3 on no/yes instead that via the button "reset"?
2. "please select" and "whatever" dont come up

[Kenneth Hobs]

1. "please select" is entered in the fields when c3 is "changed" to "yes". It is simply a matter of resetting to trigger the Change event. Was that not what you wanted?
2. See above as it does trigger. A commented line of code will not do anything during runtime. The "whatever" is an example. If you want "no" to trigger setting the values to "", change "whatever" to "" (null or empty if you will) and uncomment that line of code or simply delete that Case line and let the Case Else govern setting the null values. That is why I added it, to show you two ways to do it.

When setting the values to null, the formulas then reset as you may notice once you understand what I did.

Play around with C3 dropdown selections and I think you will see that (1) works as requested. It also does the null thingy explained in (2) when you modify the code in one of two ways that I explained.

Could I have use IF-Then-Else-End IF loop rather than Case Select, sure. Sometimes that is just what you want but when multiple conditions can occur and you only need to act on one case, Case solutions are best. The logic of them is easier to see sometimes and they are highly efficient when used as intended.

By taking the time to make the requested example file as you did in post #2, it helped me to best help you. Post #1 would not have helped me help you. Even so, small tweaks are needed to get you just where you need to be. Sometimes I understand the question and sometimes not. All I can do is try for a solution and get feedback to see if I understood the problem well enough.

Sometimes, a problem solver has to guess at a solution even when there is an extensive example with seemingly full details. As an Engineer, I can tell you that fully defining a problem, is critical to finding a decent solution. Hopefully, I addressed the bulk of what you wanted. I find the comment by Paul H. in post #3 to be true too often. The gist of that is that there are sometimes more than one way to solve a problem so be open to it. Of course if you are trying to learn a specific vba routine, that is another matter but even then, seeing the forest for the trees deal happens sometimes.

Feel free to post back if you need a tweak or pursue another path. If the path is in left field, just start another thread. You can always refer to some other thread by posting the link in the new thread to another. If mostly about this thread, just replying and asking for a few more tweaks is certainly correct.

[ddesantis]

Hi Kenneth,
THanks for the explanation, I'm trying to find my way in vba and your help is really appreciate.

I still have trouble with your spreadsheet:

1. it doesn't seem to work when I select "no". Even if I reset and switch to no, values in range C5:C11,C13:C19 are copied down.
2. this piece seems not to work as well Range("C5:C11,C13:C19").Value2 = "please select"
3. I would prefer not to have the button "reset" but rather trigger the reset by switching C3, do you think is possible?

Many thanks
daniele

[ddesantis]

I think I wasn't too clear. Let me try too provide more clarity

Initial status: all ranges (C5:C11,C13:C19,C24:C30,C32:C38,C43:C49,C51:C57) have a drop down menu with the following values: 5,6,7. Cells are blank

if C3="yes" then ranges C5:C11,C13:C19 = "please select". While ranges C24:C30,C32:C38,C43:C49,C51:C57 are still blank.
As soon as I select a value amongst 5,6,7 in ranges C5:C11,C13:C19 this is copied down to the respective cell in ranges C24:C30,C32:C38,C43:C49,C51:C57
example (C5=6,C6=5 THEN C24=6,C25=5 AND C43=6,C44=5)

if I switch C3 to "no", the previous content is cleared up and "please select" appear in C5:C11,C13:C19,C24:C30,C32:C38,C43:C49,C51:C57.
I can select a values amongst 5,6,7 per each cell of the range C5:C11,C13:C19,C24:C30,C32:C38,C43:C49,C51:C57 and Vvalues are not copied down.

Please let me know if it's still neboulous
Thanks
Daniele

[Kenneth Hobs]

I addressed most all of your issues in #9. As I said, be open to doing things in a bit different way as they can often make things much easier. Copying down is not needed. That is what cell formulas are for.

I don't see an issue with allowing a "please select" in the dropdown list. If not there, then the code gets much more involved as you have to remove data validation and then re-add it in the Selection event and even then it is complicated. Not that it can't be done I just thought a more simple approach was best.

As for the Reset button, it is easily deleted. IF you have not added the Developer group to the ribbon, it is handy when working with macros.

Doing what I said in post #9:

Private Sub Worksheet_Change(ByVal Target As Range)  
If Target.Address(False, False) <> "C3" Then Exit Sub
  
  Application.EnableEvents = False
   
  Select Case True
    Case Target.Value = "yes"
      Range("C5:C11,C13:C19").Value2 = "please select"
    Case Target.Value = "no"
      Reset
    Case Else
      'Reset
  End Select
  
  Application.EnableEvents = True
End Sub


Private Sub CommandButton1_Click()
  Reset
End Sub


Sub Reset()
  Range("C5:C11,C13:C19").Value2 = ""
End Sub

Of course the code can be more concise and just use the code from Reset rather than calling that routine. I did it that way to show you how to modularize code.

In that case, the code could be:

Private Sub Worksheet_Change(ByVal Target As Range)  
  If Target.Address(False, False) <> "C3" Then Exit Sub
  
  Application.EnableEvents = False
   
  Select Case True
    Case Target.Value = "yes"
      Range("C5:C11,C13:C19").Value2 = "please select"
    Case Target.Value = "no"
      Range("C5:C11,C13:C19").Value2 = ""
  End Select
  
  Application.EnableEvents = True
End Sub

If you want the initial status blank for those cells then my original post should have sufficed. I could have just left the c3 value blank. Simply delete all the initial ranges and the c3 and you get the blanks. Several ways to get there in other words.

[ddesantis]

It works in case of Yes.

But in case of No, I don't have the drop down menu in ranges C24:C30,C32:C38,C43:C49,C51:C57.
Do you reckon it's too complicated to have them?

[Kenneth Hobs]

Why would you have them? IF you did, you defeat the purpose of the input cells. You will have a big mess on your hands and things will get extremely difficult to synchronize.

It can be done but things get circular if the intent is to synchronize the data.

If you don't mind getting things out of sync, I guess it could be done. I would have to more fully understand all the exceptions to what you asked for.

[ddesantis]

because in case of "no" I don't need to synchronize any cell. I can input whatever value is in the drop down list.
The synchronization applies only in case of "yes"

[Kenneth Hobs]

As I said, that gets messy. In any case, this should get you closer.

Private Sub Worksheet_Change(ByVal Target As Range)  
  Dim r As Range
  Set r = Intersect(Target, Range("C3,C5:C11,C13:C19"))
  If r Is Nothing Then Exit Sub


  On Error GoTo EndNow
  Application.EnableEvents = False
   
  Select Case True
    Case Target.Value = "yes" And Target.Address(False, False) = "C3"
      Range("C5:C11,C13:C19").Validation.Delete
      Range("C5:C11,C13:C19").Value2 = "please select"
      Range("C24:C30,C43:C49,C32:C38,C51:C57").Value2 = ""
    Case Target.Value = "no" And Target.Address(False, False) = "C3"
      Range("C5:C11,C13:C19").Value2 = ""
  End Select
  
EndNow:
  Application.EnableEvents = True
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range, c As Range, i As Integer
  Set r = Intersect(Target, Range("C5:C11,C13:C19"))
  If r Is Nothing Then Exit Sub
  
  On Error GoTo EndNow
  Application.EnableEvents = False
  
  For Each c In r
    If IsNumeric(c.Value) Then GoTo AddVals
    With c.Validation
      'Add Validation back in.
      c.Value = ""
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="5,6,7"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
      .InCellDropdown = True
      SendKeys "%{Down}", True
AddVals:
      With c
        i = .Row
        If (i >= 5 And i <= 11) Or (i >= 13 And i <= 19) Then _
            Range("C" & i + 19 & ",C" & i + 38).Value = .Value
      End With
    End With
  Next c
  
EndNow:
  Application.EnableEvents = True
End Sub

[ddesantis]

Hi Kenneth,
it's still not working. I found 2 problems:
- if C3=yes, no synchronization across ranges. E.g. C5=6, c24 and C43 stay blank instead of getting 6.
- if I switch C3 from yes to no C24:C30,C43:C49,C32:C38,C51:C57 are not cleared

if it's getting too complicated, I'll find some workarounds, no worries!
really appreciate your help so far