Loop thru xx No. of sheets until the four strValue combo's are found
Hi Howard,
Am Thu, 15 Aug 2013 07:07:06 -0700 (PDT) schrieb Howard:
Whoa! I like that!! thanks, Claus.
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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|