for me it works with numbers

But try changing .value to .text


Regards

Claus B.

Here is what my attempt looks like, I wasn't getting the error before I went to cell references, so that is why I guess it has to do with my changes.

I did make a change to return the results to two pages, and that works just fine.

Howard

Option Explicit
Option Compare Text

Sub TestClausDropDown()
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 = Range("C5").Text
str2 = Range("D5").Text
str3 = Range("E5").Text

'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

Sheets("Darcy-Weisbach").Range("F5") = str1 & " " & str2 & " " & str3
[O2] = str1 & " " & str2 & " " & str3
Sheets("Darcy-Weisbach").Range("F6") = WorksheetFunction.Max(varOut)
[P2] = WorksheetFunction.Max(varOut)
MsgBox Format(Timer - st, "0.000")
End Sub