It is a bit faster:



Sub Test()

Dim lngLstRow As Long

Dim str1 As String, str2 As String, str3 As String

Dim strTotal As String, str4 As String

Dim i As Long

Dim n As Long

Dim varIn() As Variant

Dim varout() As Double

Dim wsh As Worksheet

Dim st As Double


str1 = InputBox("Input Material:", "Material")

str2 = InputBox("Input PipeNon. Diameter:", "Pipe Nom Dia")

str3 = InputBox("Input Pipe Press Class:", "Pipe Press Cls")

strTotal = str1 & str2 & str3

st = Timer

For Each wsh In ThisWorkbook.Worksheets

With wsh

lngLstRow = .Cells(.Rows.Count, 2).End(xlUp).Row

varIn = .Range("B6:H" & lngLstRow)

For i = LBound(varIn) To UBound(varIn)

str4 = varIn(i, 1) & varIn(i, 2) & varIn(i, 3)

If StrComp(strTotal, str4, 1) = 0 Then

ReDim Preserve varout(n)

varout(n) = varIn(i, 7)

n = n + 1

End If

Next

End With

Next

[K1] = str1 & " " & str2 & " " & str3

[K2] = WorksheetFunction.Max(varout)

MsgBox Format(Timer - st, "0.000")

End Sub


Regards

Claus B.


Wow! Yes indeed, a lot faster!

Many thanks, Claus.

Regards,
Howard