Creating an array up till the search value

[miisstina]

Hello,

Is there a way to create an array up until a search value (so the value someone puts into an input box).

 

[BiocideJ]

Vaguely speaking, yes. I would need a lot more information as to what you are trying to do though to give any specifics.

 

[miisstina]

Vaguely speaking, yes. I would need a lot more information as to what you are trying to do though to give any specifics.

So i have days of the week in one column and prices right besides it. I iniatilly have to search for a what day of the week i am searching for. Once that day is found, the code needs to be able to find the maximum price up to and including that day of the week and display that in a message box. I can do the first part, but I just dont understand how to find the maximum price.... I hope my explanation is sufficient.

 

[BiocideJ]

I believe something like this should do it.

Sub MaxinRange()
Dim LookupRange As Range
Dim ValRange As Range
    'this is just a sample of how I am setting the custom Date to search for
    Do
    userInp = InputBox("Enter the date to search for")
    If IsDate(userInp) Then
        userDate = DateValue(userInp)
    Else
        MsgBox userInp & " does not appear to be a valid date", vbExclamation, "Please re-enter"
    End If
    Loop Until userDate <> 0
    Set LookupRange = Range("A2:A200")  'the range of dates (however you are setting it)
    
    myR = 0
    On Error Resume Next
    myR = LookupRange.Find(userDate).Row 'the row of userDate
    On Error GoTo 0
    
    If myR = 0 Then 'find failed
        MsgBox "The date " & userDate & " was not found.", vbCritical, "Error"
        Exit Sub
    End If
    
    Set ValRange = Range(Cells(LookupRange.Row, LookupRange.Column + 1), Cells(myR, LookupRange.Column + 1))
    
    MsgBox WorksheetFunction.Max(ValRange)
    
End Sub

NOTE: If you meant that the price beside the date to the LEFT inside of to the RIGHT, change
Set ValRange = Range(Cells(LookupRange.Row, LookupRange.Column + 1), Cells(myR, LookupRange.Column + 1))

to

Set ValRange = Range(Cells(LookupRange.Row, LookupRange.Column - 1), Cells(myR, LookupRange.Column - 1))

 

[miisstina]

What is myR?
is it a dimension?

 

[BiocideJ]

It is a variable that holds a number which represents the ROW where your search criteria is found.
If your search criteria is not found, it remains defaulted to 0

It is set in this line of code

myR = LookupRange.Find(userDate).Row 'the row of userDate