CALCULATE

[oleg_v]

Hi
i need some help.
i attached file what i need is if in the entire row cells value in every cell in the row not bigger than nominal + max and not less than nominal-min write in cell A1 sheet2 the value of this row under the "serial"

attached file

[oleg_v]

Hi
i need some help.
i attached file what i need is if in the entire row cells value in every cell in the row not bigger than nominal + max and not less than nominal-min write in cell A1 sheet2 the value of this row under the "serial"

attached file

i can not attache any file it gives me an error help me to attach file and i will do it

thanks

[GTO]

How big is the wb?

[oleg_v]

490kb

[GTO]

Gosh Oleg, I do not know then. Presuming its .xls/(s/m), I do not know what the issue might be. You might try logging out and back in.

[oleg_v]

finally here is the attachment

[GTO]

Hi
i need some help.
i attached file what i need is if in the entire row cells value in every cell in the row not bigger than nominal + max and not less than nominal-min write in cell A1 sheet2 the value of this row under the "serial"

attached file

Could you edit your attachment? The above is less than clear. Add a sheet that shows the desired outcome.

[oleg_v]

THE UPDATED ATTACHMENT

[GTO]

Please note that I changed the sheet codenames as indicated.


Option Explicit

Sub exa()
Dim _
rngData As Range, _
rngSer As Range, _
aryMax As Variant, _
aryMin As Variant, _
aryNom As Variant, _
aryData As Variant, _
arySer As Variant, _
aryGood As Variant, _
i As Long, _
x As Long, _
y As Long, _
bolBad As Boolean

ReDim aryGood(0 To 0)

With shtOrion 'or> ThisWorkbook.Worksheets("ORION T")

aryNom = .Range("B1:T1").Value
aryMax = .Range("B2:T2").Value
aryMin = .Range("B3:T3").Value

For i = LBound(aryNom, 2) To UBound(aryNom, 2)
aryMax(1, i) = CDbl(aryMax(1, i) + aryNom(1, i))
aryMin(1, i) = CDbl(aryMin(1, i) + aryNom(1, i))
Next

Set rngData = .Range(.Cells(5, "B"), .Cells(.Rows.Count, "T").End(xlUp))
Set rngSer = .Range(.Cells(rngData.Cells(1).Row, "U"), _
.Cells(rngData.Cells(1).Offset(rngData.Rows.Count - 1).Row, "U"))

aryData = rngData.Value
arySer = rngSer.Value

For x = LBound(aryData, 1) To UBound(aryData, 1)

bolBad = False
For y = LBound(aryData, 2) To UBound(aryData, 2)
If aryData(x, y) > aryMax(1, y) _
Or aryData(x, y) < aryMin(1, y) Then
bolBad = True
Exit For
End If
Next
If Not bolBad Then
ReDim Preserve aryGood(1 To UBound(aryGood) + 1)
aryGood(UBound(aryGood)) = arySer(x, 1)
End If
Next
End With

'or> ThisWorkbook.Worksheets ("Sheet2") etc
shtSheet2.Range("A2").Resize(UBound(aryGood)).Value = Application.Transpose(aryGood)
End Sub

Hope that helps,

Mark

[oleg_v]

hi

thanks for the replay
but i have a problem with the last line of the code
it writs me "object defines error"

[oleg_v]

sorry
the problem solved

please can explain to me a little how your code works
this is great


thanks

[oleg_v]

Hi

i wanted to ask what can i do if the file has no good parts for macro to give me a message
because know it gives me an error

[GTO]

Sorry, I should have thought of that. Try:


' ....prefacing statements...

If IsEmpty(aryGood(LBound(aryGood))) Then
MsgBox "Bummer, not a single good val..."
Else
'or> ThisWorkbook.Worksheets ("Sheet2") etc
shtSheet2.Range("A2").Resize(UBound(aryGood)).Value _
= Application.Transpose(aryGood)
End If
End Sub

[oleg_v]

hi
i am trying to understand those lines and without success:

For i = LBound(aryNom, 2) To UBound(aryNom, 2)

and

For x = LBound(aryData, 1) To UBound(aryData, 1)
and
For y = LBound(aryData, 2) To UBound(aryData, 2)

i copied this from the code

please help with the interpretation for me to learn and to do some things myself

[GTO]

Here is the code commented. You'll want to read about Ubound and LBound in the VBA Help.


Option Explicit

Sub exa()
Dim _
rngData As Range, _
rngSer As Range, _
aryMax As Variant, _
aryMin As Variant, _
aryNom As Variant, _
aryData As Variant, _
arySer As Variant, _
aryGood As Variant, _
i As Long, _
x As Long, _
y As Long, _
bolBad As Boolean

ReDim aryGood(0 To 0)

With shtOrion 'or> ThisWorkbook.Worksheets("ORION T")

'// When we values from a range and plunk the values into an array, the array is//
'// the same shape and size as the Range from whence it came. //
aryNom = .Range("B1:T1").Value
'// It is important to remember that although the default base of a dimension //
'// is zero in Excel, when we create the array from a Range, Excel automatically//
'// gives the array a base of one. Thus - //
aryMax = .Range("B2:T2").Value
'// aryMax is sized as: aryMax(1 To 1, 1 to 19) //
'// The first dimension (1 To 1) represents the row(s) and the second (1 To 19) //
'// of course represents the columns. //
aryMin = .Range("B3:T3").Value

'// Look up (L/U)Bound in vba Help. Here we are using the array's second //
'// dimension (the columns) to give the starting and stopping vals of 'i'. //
'// Hence, this is the same as For i = 1 To 19 //
For i = LBound(aryNom, 2) To UBound(aryNom, 2)
aryMax(1, i) = CDbl(aryMax(1, i) + aryNom(1, i))
aryMin(1, i) = CDbl(aryMin(1, i) + aryNom(1, i))
Next

Set rngData = .Range(.Cells(5, "B"), .Cells(.Rows.Count, "T").End(xlUp))
Set rngSer = .Range(.Cells(rngData.Cells(1).Row, "U"), _
.Cells(rngData.Cells(1).Offset(rngData.Rows.Count - 1).Row, "U"))

aryData = rngData.Value
arySer = rngSer.Value

'// Keeping the above in mind, we now read and calculate each element in our //
'// array, reading cell by cell in each row by rotating through the cells in each//
'// row, by having the outer loop rotate through each row... //
For x = LBound(aryData, 1) To UBound(aryData, 1)

bolBad = False
'// ...and running through each cell in a given row in the inner loop //
For y = LBound(aryData, 2) To UBound(aryData, 2)
If aryData(x, y) > aryMax(1, y) _
Or aryData(x, y) < aryMin(1, y) Then
bolBad = True
Exit For
End If
Next
If Not bolBad Then
ReDim Preserve aryGood(1 To UBound(aryGood) + 1)
aryGood(UBound(aryGood)) = arySer(x, 1)
End If
Next
End With

If IsEmpty(aryGood(LBound(aryGood))) Then
MsgBox "Bummer, not a single good val..."
Else
'or> ThisWorkbook.Worksheets ("Sheet2") etc
shtSheet2.Range("A2").Resize(UBound(aryGood)).Value _
= Application.Transpose(aryGood)
End If
End Sub

Also, a handy way to see what is happening, is to step thru the code (F8), with the Locals window displayed. You can see as values change this way.

Hope that helps,

Mark