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.

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!

"tony33r" wrote:

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.

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




"tony33r" wrote in message
...
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.