Creating an array up till the search value

miisstina

New Member
Joined
Sep 27, 2014
Messages
10
Hello,

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

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Vaguely speaking, yes. I would need a lot more information as to what you are trying to do though to give any specifics.
 

miisstina

New Member
Joined
Sep 27, 2014
Messages
10
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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
I believe something like this should do it.

Code:
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))
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
Code:
myR = LookupRange.Find(userDate).Row 'the row of userDate
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,235
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top