if else macro

[shammer]

This is most likely on the easy side of a question. I created a toggle grouping that results in a value of 1-4 depending upon which button is selected. Now I want to have a separate cell to use those results to determine four other values. Can you help? Thanks.

 

[Tom Urtis]

You could do an If structure, but in case (no pun intended) your sheet expands to allow more than just 4 values, a Select Case structure will be easier and more intuitive to modify.

So, try this and modify it for target range, destination range, and conditional values.

Right click on your sheet tab, left click on View Code, and paste this in:

''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Range)

If Target <> [A1] Then Exit Sub

Select Case [A1].Value

Case 1
[B1] = "Value #1"
Case 2
[B1] = "Value #2"
Case 3
[B1] = "Value #3"
Case 4
[B1] = "Value #4"
Case Else
[B1] = "Enter 1, 2, 3, or 4 in A1"

End Select

End Sub

'''''''''''''''''''''''''''''''''''''''

HTH

Tom Urtis

 

[shammer]

Thank you so much for the quick reply!

I must admit, however, that I have been unsuccessful at implementing this code for two main reasons.

1. I don't know which fields are the data ranges that you are referring to, or do I know exactly how the cell references should read (i.e. C3 or "C3" or <C3>)

2. I don't think I know how to actually execute the code once it is correct.

If you have the time to replace the characters with a simple example, I would appreciate it.

For example, if I am referencing a cell (C3) that will have the value of 1, 2, 3, or 4. Cell D3 would have the value of 01/31/02 if C3 = 1, 02/28/02 if C3 = 2, etc.

Sorry for my ignorance, I will pursue finding a reference text to help me with these types of issues in the future.

 

[gplhl]

On 2002-02-28 11:28, shammer wrote:
Thank you so much for the quick reply!

I must admit, however, that I have been unsuccessful at implementing this code for two main reasons.

1. I don't know which fields are the data ranges that you are referring to, or do I know exactly how the cell references should read (i.e. C3 or "C3" or <C3>)

2. I don't think I know how to actually execute the code once it is correct.

If you have the time to replace the characters with a simple example, I would appreciate it.

For example, if I am referencing a cell (C3) that will have the value of 1, 2, 3, or 4. Cell D3 would have the value of 01/31/02 if C3 = 1, 02/28/02 if C3 = 2, etc.

Sorry for my ignorance, I will pursue finding a reference text to help me with these types of issues in the future.

If Target <> [A1] Then Exit Sub

Select Case [A1].Value

Case 1
[C3] = 1
Case 2
[C3] = 2
Case 3
[C3] = 3
Case 4
[C3] = 4
Case Else
[C3] = "Enter 1, 2, 3, or 4 in A1"

End Select

End Sub