Results 1 to 8 of 8

Thread: Macro to check a Cells Value and run different code depending on the cells value?

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Macro to check a Cells Value and run different code depending on the cells value?

    Hi
    I have the following code in a macro that creates sets the value of Cell B25.

    Code:
    If Range("B23").Value = 2 Or Range("B23").Value = 3 Or Range("B23").Value = 4 Or Range("B23").Value = 5 Then run code
    This works well but I now need it to do the following so it runs different code depending on the value of Cell B23:

    Code:
    If Range("B23").Value = 1 Then run code A
            
    but
    
    If Range("B23").Value = 2 Then run code B
            
    but
    
    If Range("B23").Value = 3 Then run code C
            
    but
    
    If Range("B23"). Value = 4 Or Range("B23").Value = 5 Then run code D
    
    I am learning so much from this forum and you helpful folks but my abilities have stalled on this?

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Posts
    554
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    Here you go

    Code:
    If Range("B23").Value = 1 Then run code A
    ElseIf Range("B23").Value = 2 Then run code B
    ElseIf Range("B23").Value = 3 Then run code C
    ElseIf Range("B23"). Value = 4 Or Range("B23").Value = 5 Then run code D
    End If

  3. #3
    New Member
    Join Date
    Jan 2011
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Re: Macro to check a Cells Value and run different code depending on the cells value?

    Quote Originally Posted by mrshl9898 View Post
    Here you go

    Code:
    If Range("B23").Value = 1 Then run code A
    ElseIf Range("B23").Value = 2 Then run code B
    ElseIf Range("B23").Value = 3 Then run code C
    ElseIf Range("B23"). Value = 4 Or Range("B23").Value = 5 Then run code D
    End If

    Thank you so much

    Why on earth I could not work that out myself or find it on google I dont know.

    You have really helped me out.

    Thanks again.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,215
    Post Thanks / Like
    Mentioned
    76 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    Another way, especially useful when multiple values &/or range(s) of values are involved - see my second last 'Case' statement
    Code:
    Select Case Range("B23").Value
      Case 1: Run code A
      Case 2: Run code B
      Case 3: Run code C
      Case 4, 5: Run code D
      Case 6, 9 To 15, 20: Run code E
      Case Else: Run code F
    End Select
    
    End Select
    Last edited by Peter_SSs; Oct 11th, 2018 at 10:12 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    Quote Originally Posted by Peter_SSs View Post
    Another way, especially useful when multiple values &/or range(s) of values are involved - see my second last 'Case' statement
    Code:
    Select Case Range("B23").Value
      Case 1: Run code A
      Case 2: Run code B
      Case 3: Run code C
      Case 4, 5: Run code D
      Case 6, 9 To 15, 20: Run code E
      Case Else: Run code F
    End Select
    
    End Select
    Hi Peter

    Thank you for your reply.

    On your 2nd last Case statement can you explain what that does (so I may learn)

    Many thanks

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,215
    Post Thanks / Like
    Mentioned
    76 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    Quote Originally Posted by Event2020 View Post
    On your 2nd last Case statement can you explain what that does (so I may learn)
    code E would run if cell B23 was any one of the following values
    6, 9, 10, 11, 12, 13, 14, 15, 20
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Jan 2011
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    Quote Originally Posted by Peter_SSs View Post
    code E would run if cell B23 was any one of the following values
    6, 9, 10, 11, 12, 13, 14, 15, 20

    Thats excellent Peter

    Thank you and I really appreciate you help.

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,215
    Post Thanks / Like
    Mentioned
    76 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Macro to check a Cells Value and run different code depending on the cells value?

    No problem. Glad to help.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •