nested if statement

[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

[Richard Buttrey]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[ddesantis]

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I think we need to see the workbook. Manually add a sample of the results you expect to see and tell us which are the result cells.

It's not clear to me what you mean by if range P changes. If P6 changes then change corresponding cell in range R.
In addition this second If (currently commented out) will be invoked anyway by virtue of P6 changing, (assuming of course that range 'P' covers P6. Is this what you want?

Incidentally there was no need to repost, you could simply have edited your original.

[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

[Richard Buttrey]

Hi

Try this sheet change event

Please Login or Register  to view this content.

[ddesantis]

Thanks Richard. It works

There is only one point that is still to be fixed, let me know if you can help me.

If C3 is set on no and I input values in ranges c24:c30,c32:c38,c43:c49,c51:c57, when I switch C3 to yes, ranges c24:c30,c32:c38,c43:c49,c51:c57 retain those values.Instead I would like to clear the content.

Many thanks
Daniele

[Richard Buttrey]

Hi,

Untested but presumably after the first two lines

Please Login or Register  to view this content.

add

Please Login or Register  to view this content.

[ddesantis]

thanks!!!!! it works!

A minor point, let me know if takes too much to be fixed or not:

When I switch C3 to "yes" the macro clear the content as follows:
- C5:C11,c13:c19 = please select
- c24:c30,c32:c38,c43:c49,c51:c57 = blank
and this is in line with what I want

but as soon as I input a value in C5:C11,c13:c19, the value is copied down (and this again is what I want) but all the other cells in the range c24:c30,c32:c38,c43:c49,c51:c57 assume value "please select" instead of being blank. Example: C5=5, then C24=5, C43=5 , happy with this. but c25:c30,c32:c38,c44:c49,c51:c57 = please select instead of blank

[Richard Buttrey]

Hi,

Not sure I entirely understand but look for the ="Please select" items in the code and either change the cell range that's given this value or change the "Please select" to ""

Or indeed a combination of both.

[ddesantis]

ok, but I think it cannot be done, because the following go into conflict

With Range("C5:C11,c13:c19").Validation
Range("C5:C11,c13:c19") = "Please select"



If Not Intersect(Target, Range("C5:C11,c13:c19")) Is Nothing And LCase(Range("C3")) = "yes" Then
Application.EnableEvents = False
Range("C24:C30").Value = Range("C5:C11").Value
Range("C43:C49").Value = Range("C5:C11").Value
Range("C32:C38").Value = Range("C13:C19").Value
Range("C51:C57").Value = Range("C13:c19").Value
Application.EnableEvents = True
End If



If yes, the range c24:c30,c32:c38,c43:c49,c51:c57 get always the values in C5:C11,c13:c19 and because the value in C5:C11,c13:c19 is "please select" I cannot have the range c24:c30,c32:c38,c43:c49,c51:c57 blank

no worries and thanks for your help!