Run code until

[ddub25]

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?

 

[Legacy 391147]

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

Yohann

 

[JLGWhiz]

You might be able to do that with Worksheet_Change event code. It will run each time a change is made to the worksheet.

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.

 

[ddub25]

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?

 

[JLGWhiz]

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.

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.

 

[chicagocomputerclasses]

Try this

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

 

[ddub25]

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