there was a little error into the code and the array is now only as

large as needed.

Change it to:



Sub Lookup_Four_Return_Fifth2()

Dim lngLstRow As Long

Dim str1 As String

Dim str2 As String

Dim i As Long

Dim intVStore() As Double

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")



For Each wsh In ThisWorkbook.Worksheets

With wsh

lngLstRow = .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

ReDim Preserve intVStore(intValVar)

intVStore(intValVar) = .Cells(i, 5).Value

intValVar = intValVar + 1

End If

Next

End With

Next wsh

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

End Sub


Regards

Claus B.


This really seems to do the trick quite well.

I added this to list the items searched for and the price.

Range("K1") = Cells(i, 1) & " " & Cells(i, 2)_
& " " & Cells(i, 3) & " " & Cells(i, 4) ' Items
Range("K2") = intVStore() 'Price

Thanks again, Claus.

Regards,
Howard