Results 1 to 7 of 7

Thread: Run code until
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2007
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run code until

    Code:
    Sub Ent1_S_S()
         If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0 Then
              Range("CT85").Value = Range("CS73").Value
         Else
              Range("CT85").Value = "No"
         End If
    End Sub
    I want to adapt this code to continuously look for the situation where: Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0
    When it finds this to be true, and enters the value of CS73 into CT85, I want this new value in CT85 to be fixed and the code to stop running. Can anyone help?
    Last edited by ddub25; Dec 12th, 2016 at 10:06 AM.

  2. #2

    Join Date
    Dec 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run code until

    Why not adding Exit Sub? Maybe I did not well understand...

    Yohann

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,060
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Run code until

    You might be able to do that with Worksheet_Change event code. It will run each time a change is made to the worksheet.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Range("CT85").Value <> Range("CS73").Value Then
         If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0 Then
              Range("CT85").Value = Range("CS73").Value
         Else
              Range("CT85").Value = "No"
         End If
    End If
    Application.EnableEvents = True
    End Sub
    The code runs each time a change is made to the sheet, but will stop executing once the CT85 value is equal to the CS73 value. Copy the code to the worksheet code module for the sheet where the key cells reside. To access that code module, right clidk the sheet name tab and click 'View Code' in the pop up menu. Once installed the code is triggered to run by making changes on the sheet.
    Last edited by JLGWhiz; Dec 12th, 2016 at 10:33 AM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  4. #4
    Board Regular
    Join Date
    Jan 2007
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run code until

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        Dim Server As String
        If Range("CS73").Value = "FALSE" Then
            Server = "P2"
        Else
            Server = "P1"
        End If
        
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0 Then
            Range("CT85").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 1 Then
            Range("CT86").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 2 Then
            Range("CT87").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 3 Then
            Range("CT88").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 4 Then
            Range("CT89").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 5 Then
            Range("CT90").Value = Server
        End If
        
    Application.EnableEvents = True
    End Sub
    Thanks, yes running the code on a worksheet change makes sense. I've adapted the code to the above.

    I've got a few questions:
    1) Can I run multiple "Private Sub Worksheet_Change" routines on the same work sheet? (When I copied the routine, VBA seemed to not like it being duplicated, but I couldn't see how to give it a unique name)
    2) Is this code a bit long-winded, and could be made more efficient?
    Last edited by ddub25; Dec 12th, 2016 at 01:13 PM.

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,060
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Run code until

    1) Excel will only allow one Worksheet_Change event code per sheet. However, you can merge other code into the same event code and use If...Then statements to isolate the variaus criteria for which you want to take action. It is a matter of logical process to properly merge different actions into a single event procedure. Try replacing your existing change event code with this one.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        Dim Server As String
        If Range("CS73").Value = "FALSE" Then
            Server = "P2"
        Else
            Server = "P1"
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0 Then
            Range("CT85").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 1 Then
            Range("CT86").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 2 Then
            Range("CT87").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 3 Then
            Range("CT88").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 4 Then
            Range("CT89").Value = Server
        End If
        If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 5 Then
            Range("CT90").Value = Server
        End If
        If Range("CT85").Value <> Range("CS73").Value Then
            If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = 0 Then
                Range("CT85").Value = Range("CS73").Value
            Else
                Range("CT85").Value = "No"
            End If
        End If
    Application.EnableEvents = True
    End Sub
    2) I think the code will work OK.

    The title line must be 'Private Sub WorkSheet_Change(ByVal Target As Range)' or the event will not run as intended.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  6. #6
    Board Regular chicagocomputerclasses's Avatar
    Join Date
    Mar 2015
    Location
    Chicago, IL
    Posts
    645
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run code until

    Try this

    Code:
    Sub DoStuff()
    
    
    Dim Server As String
    If Range("CS73").Value = "FALSE" Then
            Server = "P2"
    Else
            Server = "P1"
    End If
    
    
    for i = 0 to 5
    
    
    	If Range("CT73").Value + Range("CT74").Value = 0 And Range("CU73").Value + Range("CU74").Value = i Then
            	Range("CT" & (85 + i)).Value = Server
    	End If
    
    
    next i
    
    
    End Sub
    
    
    
    
    
    
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
       
    	DoStuff()
        
    Application.EnableEvents = True
    End Sub

  7. #7
    Board Regular
    Join Date
    Jan 2007
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run code until

    Thanks for all your help. I've gone with chicago's code as it's concise and does the job. Thanks Chicago. Problem solved

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
  •