Help appreciated

[Tvercetti]

Hey everyone ..


Two columns:
Date Price
Jan-78 11.46
Feb -78 12.56
So on? So on?

I am trying to create a Do Loop that identifies the first row as RecordMonth and RecordPrice (i.e. the highest price so far in the data and the month associated with that price) and then goes down the column and replaces the RecordPrice and RecordMonth when it encounters a higher value.
In the beginning I have asked for a month (InputBox) and I want it to do this until and including the month specified by user.
So the outcome would be that the macro tells me the record price and in what month up until the month specified by the eventual user.

At this moment my code is a mix up and isn?t getting me the desired result. Any help would be appreciated. Thank you

[Bob Phillips]

... replaces the RecordPrice and RecordMonth when it encounters a higher value.

Replaces it with what, the previously high value?

And find the one with the same month? What about year? Won't this just flatten all of a month's data?

[Tvercetti]

I want to record the RecordPrice and RecordMonth as the data in first row and then go down each row and if the price is higher than the previous highest price replace the current values for RecordPrice and RecordMonth with this new higher price.
This would be done until the month given by the user at the start.

I was not specific, the month would be of the format e.g. Jun-07 so would combine month and year. So date ..

Thanks, let me know if its still unclear.

[Bob Phillips]

[vba]

Public Sub Test()
Dim LookupDate As Date
Dim iRow As Long
LookupDate = Application.InputBox("Please supply a valid date to check month and year of", Type:=1)
If IsDate(LookupDate) Then
On Error Resume Next
iRow = ActiveSheet.Evaluate( _
"MAX(IF((MONTH(A1:A100)=" & Month(LookupDate) & ")*" & _
"(YEAR(A1:A100)=" & Year(LookupDate) & "),B1:B100))")
On Error GoTo 0
If iRow > 0 Then
MsgBox Cells(iRow, 1).Value
End If
End If
End Sub
[/vba]

[Tvercetti]

Thanks, I am not sure I understand what's happening though.
I wanted to do this through a Do Loop, as it makes more sense to me since I understand this better.
The simpler the better, as have only a basic understanding of VBA

[YellowLabPro]

Bob did you better than a loop.
He took a particular range, did a search, used the MAX function to determine the largest value in the range and then performed your instructions.
This saves time of looping through every cell and comparing to the range over and over....
He did you a Solid! :-)

[Tvercetti]

K, I see, well thanks
I'm sorry to be a pain, but would you mind showing me how I would do it with a Do Loop, as I'm trying to learn this.

[YellowLabPro]

T-
I don't have time at the moment. But if I may make a suggestion, as I am still learning too. Post up what you can, work through how much you know and ask specific questions.
Here are a few guidelines to help you get started-
Determine the range you want to check, then determine how you are going to check this range, if it is a Do Loop, I am guessing you would tell it to Do something until it changes or the opposite.
Define your criteria you want to check, in this case if x is greater than y, then do something.
Although I am not providing you an answer, it should hopefully get you started thinking about what things you will solve for. Then choose the best option, or the best one you know, this took me sometime to figure out....so you are getting some valuable info from another beginner regarding this part of VBA.... if that makes sense.

[YellowLabPro]

T-
One other thing, if you don't understand particular parts, break it down and ask.
Have you started stepping through code as a way to see what is happening? This is an invaluable method to actually see how the code is manipulating the data in a sheet.

[Bob Phillips]

Untested

[vba]

Public Sub Test()
Dim LookupDate As Date
Dim SavedDate As Date
Dim SavedAmt As Double
Dim iRow As Long
Dim Lastrow As Long
LookupDate = Application.InputBox("Please supply a valid date to check month and year of", Type:=1)
If IsDate(LookupDate) Then
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Month(.Cells(i, "A").Value) = Month(LookupDate) And _
Year(.Cells(i, "A").Value) = Year(LookupDate) Then
If .Cells(i, "B").Value > SavedAmt Then
SavedAmt = .Cells(i, "B").Value
SavedDate = .Cells(i, "A").Value
End If
End If
Next i
End With
MsgBox SavedDate
End If
End Sub
[/vba]