Searching through a column and extracting data

[jnagel]

Hi,

I am very new to VBA (self taught) so please excuse my ignorance.

I have a column of cells with values of either "Pass" or "Fail"

I want to search through this column, locate all the fails, store what row each one occurs on, and the number of fails that exist.

Is such a thing possible, and any advice how to do this?

Thanks!

 

[VoG]

For data in column A, results appended at the end of the column

Sub Fails()
Dim LR As Long, i As Long, j As Long, s As String
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = "Fail" Then
        j = j + 1
        s = s & i & ", "
    End If
Next i
If j > 0 Then
    Range("A" & LR + 1).Value = j
    Range("A" & LR + 2).Value = Left(s, Len(s) - 2)
End If
End Sub

 

[jnagel]

Thanks!

That works really well.

Now the fun part is trying to understand how it works

 

[cgcamal]

Hi jnagel,

Another option below, showing result in column B, assuming you have input data in column A.

Sub Find_Fails()
Dim Col As String, numFails As Integer, FailsPos As Range

Col = "A"
numFails = WorksheetFunction.CountIf(Columns(Col), "fail")

If numFails > 0 Then
    Set FailsPos = Range(Col & 1)
    For i = 1 To numFails
        Set FailsPos = Columns(Col).Find(What:="fail", After:=FailsPos, SearchOrder:=xlByRows)
        Cells(m + 1, "B") = Col & FailsPos.Row
        m = m + 1
    Next
    Cells(m + 1, "B") = "Total of ""fails""=" & numFails
Else
    MsgBox "No ""fails"" found"
End If
End Sub

Hope this helps,

Regards.