VBA Loop for and do while-CodePudding

I am trying to create a For and Do while loop in VBA. I want that when the value 'X' is entered in column A and if column W is equal to "T", all the rows below (column A) should be checked "X" until the next value "T" in column W. My script does not work, only the row below is filled with "X" and the file closes (bug!)

Here is the complete code

Sub Chaine()
  
    For Each Cell In Range("A2:A3558")
        If UCase(Cell.Value) = "X" And Cells(Target.Row, 23) = "T" Then
            Do While Cell.Offset(0, 23) <> "T"
            Cell.Offset(1, 0).Value = "X"
            Loop
        End If
    
    Next Cell
End Sub

CodePudding user response：

Try this:

Sub Chaine()
    Dim c As Range, vW, flag As Boolean
    
    For Each c In ActiveSheet.Range("A2:A3558").Cells
        vW = UCase(c.EntireRow.Columns("W").value)
        If UCase(c.value) = "X" And vW = "T" Then
            flag = True 'insert "X" beginning on next row...
        Else
            If vW = "T" Then flag = False  'stop adding "X"
            If flag Then c.value = "X"
        End If
    Next c
End Sub

CodePudding user response：

Your Do While loop has to be problem as it doesn't change and will continue to check the same thing. It's unclear what you want to happen, but consider something like this as it moves to the right until you've exceeded the usedrange.

Sub Chaine()
Dim cell As Range

      For Each cell In Range("A2:A3558").Cells
        If UCase(cell.Value) = "X" And Cells(Target.Row, 23) = "T" Then
            Do While cell.Offset(0, 23) <> "T"
               Set cell = cell.Offset(0, 1)
               'not sure what this is supposed to do...?
               'cell.Offset(1, 0).Value = "X"
               
               If cell.Column > cell.Worksheet.UsedRange.Cells(1, cell.Worksheet.UsedRange.Columns.Count).Column Then
                  MsgBox "This has gone too far left..."
                  Stop
               End If
               
               
            Loop
        End If
    
    Next cell
End Sub

CodePudding user response：

I just went off your description in the question. Your code is not doing what you want and it's not really how you would do this in my opinion. I figured I would put an answer that does what you ask but, keep it simple.

I'm guessing Target in the code refers to an event.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SomethingBadHappened
'Checks if you are in the A column from the target cell that 
'was changed and checks if only X was typed.
    If (Target.Column = 1 And UCase(Target) = "X") Then
        Dim colToCheck_Index As Integer
        colToCheck_Index = 23 'W Column
        Dim colToCheck_Value As String
        Dim curRow_Index As Integer
        curRow_Index = Target.Cells.Row
'Checks if the column we are checking has only a T as the value.
        If (UCase(ActiveSheet.Cells(curRow_Index, colToCheck_Index).Value) = "T") Then
            Application.EnableEvents = False
            Do
'Set the proper cell to X
                Range("A" & curRow_Index).Value = "X"
                curRow_Index = curRow_Index   1
'Set the checking value to the next row and check it in the 
'while loop if it doesn't equal only T
                colToCheck_Value = ActiveSheet.Cells(curRow_Index, colToCheck_Index)
'Set the last row to X on the A column.
            Loop While UCase(colToCheck_Value) <> "T"
            Range("A" & curRow_Index).Value = "X"
            Application.EnableEvents = True
        End If
Exit Sub
SomethingBadHappened:
        Application.EnableEvents = True
    End If
End Sub