Help with VBA

[tony33r]

I am trying to write the VBA to determine the ranges of cells that pass a
certain criteria. Let's suppose that there is a set of data of 10 units, the
units that failed are 3, 4 and 8. I want to create a string that will look at
the pass units and generate a text like this: Units 1 through 2, 5 through 7
and 9 through 10 passed. Of course, the data might change with a new subset
of units, and the number of units tested can vary. I have succesfully
imported a text file that contains the data, analyze the data based on
pass-fail criteria, used an array to store the fail values (such as
fail(1)=3, fail(2)=4, fail(3)=8, total units inspected 10, totfailed
units=3), but I am struggling with the code to create such pass data string.
Any help will be very helpful.

 

[Uru Nime]

instead of using VBA have you looked at the DSUM, DCOUNT formulas?
By setting a criteria cell you can calculate info within a particular range.
Also, you can write over the old data and the range will remain the same.
Not sure if it'll help you, but it's a thought!

 

[JLGWhiz]

This night not be what you want, but it loads the passed items into and
array based on some assumptions. Assumes a header row and assumes the unit
ID is in column A with the pass/fail entry in column B.
Sub numPass()
Dim lr As Long, C As Range
Dim pass() As Variant
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ReDim pass(lr)
For Each C In Range("A2:A" & lr) 'Assumes header
If Not C Is Nothing Then
If LCase(Trim(C.Offset(0, 1))) = "pass" Then
i = i + 1
pass(i) = C.Value
End If
End If
Next
End Sub