try it with StringCompa



Sub Lookup_Four_Return_Fifth2()

Dim lngLstRow As Long

Dim str1 As String

Dim str2 As String

Dim i As Long

Dim intVStore(1 To 50) As Integer

Dim intValVar As Integer

Dim wsh As Worksheet



str1 = InputBox("Input DA:", "DA") & InputBox("Input AA:", "AA") _

& InputBox("Input P:", "P") & InputBox("Input HAULER:", "HAULER")

intValVar = 1



For Each wsh In ThisWorkbook.Worksheets

lngLstRow = wsh.UsedRange.Rows.Count

For i = 1 To lngLstRow

str2 = Cells(i, 1) & Cells(i, 2) & Cells(i, 3) & Cells(i, 4)

If StrComp(str1, str2, 1) = 0 Then

intVStore(intValVar) = Cells(i, 5).Value ' COL E

intValVar = intValVar + 1

End If

Next

Next wsh

MsgBox ("The Price is: " & WorksheetFunction.Max(intVStore()))

End Sub


Regards

Claus B.


Whoa! I like that!! thanks, Claus.

Regards,
Howard