Hi Howard,

Am Thu, 15 Aug 2013 06:08:01 -0700 (PDT) schrieb Howard:

I'm trying to modify some archived code to lookup four inputs and return a fifth, which is the price.

This works fine on a single sheet but I may have to take the bushel basket full of strValue's and look for that combination across 4,5 maybe 6 sheets.

The "For Each rngCell In Range("A1:A" & lngLstRow)" of sheet Data1, Data2, Data3etc. has me stumped.

I haven't gotten this far yet, but with that many inputs, I probably want to list the inputs and the price when found. I'll be working on that in the meantime.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2