I'm having trouble looping through cells in a column. When using the commented out If statement instead of the 'Exit Do' below it I start and infinite loop. For some reason my code isn't moving down through the cells even with 'cell.Offset().Select'. When running the code it just returns the first box instead of checking each box until it gets to an empty box and leaving the 'Do loop' to start up the 'for loop' again.
'Macro is designed to Fill in blank spaces in column "F" depending on Passed, Non-Executed, or Failed Steps.
'Should fill in "F" if there exists an "F" in the filled in steps under it.
'Fills in "NE" if there is one present and no "F" present
'Fills in "P" if all else fails
Sub AutoFill()
Dim rng As Range, cell As Range
'Setting Variables, cell is used as current cell and rng is used as complete column of cells to check
Dim pf As String
'Variable that is assigned the cell values to check
'Set rng = Application.InputBox("Select a range", "Get Range", Type:=8)
Set rng = Range("F36:F66")
'Range setters, top one is input by the user once ran. It is input like follows '$Column$RowStart:$Column$RowEnd
For Each cell In rng
'Loops through set range and check each cell for the following
If IsEmpty(cell) = True And IsEmpty(Range("B" & cell.Row)) = True Then
'Checks if cell is empty and if the cell in the same row and column "B"
If IsEmpty(cell.Offset(1, 0)) = False Then
'Checks if cell under 'current cell' is empty
Dim TrackedCell As Range
Set TrackedCell = cell
'Creates variable that marks 'current empty cell'
pf = cell.Offset(1, 0).Value
'Assigns cell values of the cell under current cell to variable
cell.Offset(1, 0).Select
'Moves down 1 cell
'MsgBox "The value is" & TrackedCell.Value
Do
'Creates loop
'MsgBox "Loop starts"
If IsEmpty(TrackedCell) = True And pf = "F" Or (TrackedCell.Value = "P" And pf = "F") Or (TrackedCell.Value = "NE" And pf = "F") Then
'Checks if 'current empty cell' is still empty and if current cell value equals "F"
TrackedCell.Value = "F"
TrackedCell.Interior.ColorIndex = 6
'Sets 'current empty cell' to "F" and highlights cell
'MsgBox "If works"
ElseIf (IsEmpty(TrackedCell) = True And pf = "NE") Or (TrackedCell.Value = "P" And pf = "NE") Then
'Checks if 'current empty cell' is still empty and if current cell value equals "NE"
TrackedCell.Value = "NE"
TrackedCell.Interior.ColorIndex = 6
'Sets 'current empty cell' to "NE" and highlights cell
ElseIf IsEmpty(TrackedCell) = True And pf = "P" Then
'Checks if 'current empty cell' is still empty and if current cell value equals "P"
TrackedCell.Value = "P"
TrackedCell.Interior.ColorIndex = 6
'Sets 'current empty cell' to "P" and highlights cell
'Exit Do
'exits loop
End If
'MsgBox "What now"
pf = cell.Offset(1, 0).Value
cell.Offset(1, 0).Select
'Moves down 1 cell
'MsgBox "Should have moved down a cell"
'If IsEmpty(pf) = True Then
'MsgBox "Exit If Used"
'Exit Do
'End If
'exits loop
Exit Do
'MsgBox "Not Exit If"
Loop Until IsEmpty(pf) = True
'Loops through range checking and moving down cells until the next empty cell is found
End If
End If
Next cell
End Sub
The MsgBoxes were for me to check where there were issues in the program while running.
To explain further. I'm trying to fill in an empty cell in a column full of "F"'s, "P"'s, and "NE"'s. The idea is if an "F" is present between this empty cell in the column and the next empty cell in the same column than the top empty cell should have an "F" put in it. The same idea goes for "NE", but "F" would over power "NE". If there exists neither of those in the full cells under the empty cell a "P" is put it. Basically tests were done and were grouped in categories. If one test failed or was not executed in the category the whole thing is labeled its respective label.
In the sheet below I have an example of how it would look. I'm trying to make the macro autofill the sub or super test's empty box depending on the Procedures under it.
