Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: if else macro

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )

    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
    Regards,

    Gary Hewitt-Long

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •