Macro to find Min value

Closed
JV - Sep 30, 2009 at 09:57 AM
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011 - May 25, 2011 at 03:21 AM
Hello,
I need code for part of my macro that will find the min value in a particular column and based on the min value clear the contents in other cells.

For example, I have dates in one column such as:

200906
200905
200808
200801
200710
200705
200702

So I need to find all the cells with the min date and clear the contents of other cells for the min value only.

Thanks
Jay

9 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 19, 2011 at 01:41 AM

https://authentification.site/files/28537524/khan.xls

. Is it ok

The macro is in vbeditor. (repeated here) . delete column G and again run macro "minimum" from the vb editor.

```Sub minimum()
Dim r As Range, mmin As Range
Set r = Range("F3")
Set mmin = r
Do
'r.Select
If r < r.Offset(-1, 0) And r < r.Offset(1, 0) Then
Set mmin = r
If Cells(mmin.Row, "D") > Cells(mmin.Row, "e") Then
GoTo nextstep
Else
GoTo loopagain
End If
Else
GoTo loopagain
End If
nextstep:
Cells(mmin.Row, "G") = "minimum price"
Set r = r.Offset(1, 0)
loopagain:
Set r = r.Offset(1, 0)
If r = "" Then Exit Do
Loop
End Sub
```

The second question

Quote

"So now if we try to find maximum price, we have 59€ at 2100hrs and its production is less than 45 as well plus their difference is more than 10€. so it can be done.

I would like to create a small scenario here, i.e. in case if at 2100hrs with the price 59€, if the production data is 50MW then we have to find 2nd Max which should be after the minimum price hour.. and in this case that would be 55€ at 1900hrs.. I would be grateful if you can check this condition as well."
unquote

This is not clear

Let us step by step

find max in column F. this is F23
this is more than 45+10
but the production D23 is 1.321 and not 50
what should I d0
if D23 is 50 what should I do

clarify.
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 19, 2011 at 05:04 AM
Thanks a lot Venkat, I somehow replied but that message is not appearing, anyway I will write it again...

Following are the links where I created scenarios and added comments which will help you in getting a clear idea as my previous descriptions are not very clear...

if that does not work then use this one..

The scenario: 1 is base case, simple scenario, which will find the profit through one transaction, but I want to incorporate scenario 2 & 3 in it to make it more comprehensive.

Scenario: 4 will find two transactions and give profit means I can get more profit out of the same data.

I hope when u see the files you will get a clear n holistic idea...

Thanks & Regards,
Khan
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 22, 2011 at 12:39 PM
Can you please take out some time to look into my problem.. I would really appreciate.
Thanks!!!
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 30, 2009 at 09:09 PM
keep your original file sagely somewhere so that is there is a mess up you can retrieve the data

try this macro

```Sub test()
Dim rng As Range, mn As Long, j As Integer, k As Integer
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
mn = WorksheetFunction.Min(rng)
j = Range("A1").End(xlDown).Row
MsgBox mn
MsgBox j
For k = j To 1 Step -1
If Cells(k, 1) <> mn Then Cells(k, 1).EntireRow.Delete
Next k

End Sub```

if this is ok confirm in the post
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 18, 2011 at 06:12 AM
Dear Venkat,
I have little bit complex problem, Hope you can easily help me out in that:

I have storage capacity installed in a power generation system and when hourly price is low it should store the energy and reproduce it when it's high. I have few conditions needed to be fulfilled and they are driving me crazy and making me confuse how to do it.

the conditions are:
- Store at low price and sell at high (the storage should be before selling hour from 00:00 till 2300hrs).
- The power production should be higher than storage capacity (i.e 3MW) of that minimum hour otherwise goto 2nd minimum and check this condition on 2nd minimum as well and so on, keeping in view the first condition as well.
- The power production should be less than 45MW of max otherwise select 2nd max and that should satisfy the first condition as well.
- The difference between min & max price should be 10€.
- Formula: Profit=(stored data * (max hourly price - min hourly price))

Following is the 1 day sample data..

Month Day Hour Production(MW) StorageCap(MW) HrlyPrice(€)
January 1 0:00 0.24 3 9.57
January 1 1:00 5.929 3 10
January 1 2:00 1.696 3 0
January 1 3:00 13.591 3 12
January 1 4:00 9.779 3 11
January 1 5:00 8.532 3 12
January 1 6:00 11.813 3 0
January 1 7:00 13.351 3 23
January 1 8:00 11.455 3 10
January 1 9:00 9.55 3 12
January 1 10:00 10.735 3 10
January 1 11:00 15.101 3 9
January 1 12:00 13.615 3 5
January 1 13:00 6.429 3 12
January 1 14:00 8.756 3 16
January 1 15:00 11.991 3 10
January 1 16:00 14.717 3 30
January 1 17:00 23.494 3 38
January 1 18:00 23.973 3 45
January 1 19:00 32.591 3 55
January 1 20:00 33.778 3 27.32
January 1 21:00 1.321 3 59
January 1 22:00 31.222 3 30.8
January 1 23:00 27.786 3 29.1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 18, 2011 at 06:53 AM
not very clear to me. give me two examples with respect to january 1 data you have posted.

for e.g. at
st 0:0 production is o.24 which is less than 3 and what is that 9.57

time 1:00 production is 5.929 more than storage of 3.10 that time. what should you want

like that give three examples on the basis of the data.

Didn't find the answer you are looking for?

SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 18, 2011 at 07:09 AM

Yes at 0:0 production is less than 3 so this transaction is disqualified, 9.57 is the price of this hour.

So in this case we have 1st minimum price 0€ at 0200hrs but the production data is less then 3 so we cant use this.
Therefore if we try to find 2nd minimum afterwards, we have again 0€ at 0600hrs and its production is more than 3 so we can take it as minimum price.

So now if we try to find maximum price, we have 59€ at 2100hrs and its production is less than 45 as well plus their difference is more than 10€. so it can be done.

I would like to create a small scenario here, i.e. in case if at 2100hrs with the price 59€, if the production data is 50MW then we have to find 2nd Max which should be after the minimum price hour.. and in this case that would be 55€ at 1900hrs.. I would be grateful if you can check this condition as well.

therefore change the production data of 2100hrs 50MW instead of 1.321MW.

Thanks again!!!
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 19, 2011 at 04:46 AM
Thanks a lot dude for the code and help.. Indeed my description of the case is not very clear. with your code and its output I have made 4 scenarios which will give clear step by step idea.

scenario 1 is simple base case, the most typical but I want to incorporate scenario 2 & 3 in it as well to make it more robust.

Ultimately I need scenario 4, which will maximise my profit and give me two transactions instead of one as in scenario 1.

So at the end I will compare both of them Scenario 1 (including 2,3) and scenario 4, and compare the profits....

after successfully doing these I will bother you for something else if you have time.

Following are the two links, first one is in zip format the other one is in xls format.

If they dont work kindly let me know on bahrite@hotmail.com, I will forward the to you.

Thanks & Regards,
Khan
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 22, 2011 at 09:00 PM
very complicated set of conditions. I shall look into it. give me a few days. till then I hope you will win in the stock trading.
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 23, 2011 at 04:35 AM
yeah thats true... take your time.. I will try to make it more simpler and work on your previous code.. Its part of my research project.. lets hope it works and I can convince my professors panel.. :))
Thanks again..
Regards,
Khan
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 23, 2011 at 09:21 AM

that is columns I to O

minimum is N21 which is 0
but in this row L21 is 1.5 and not greater than M21 whiich is 2 so rejected ok

next higher minimum is N20 which is 2.6 but not N11 which is 5
here in this row L20 is 20.22 is greater than M22 which is 2 so must be accepted
not row 11

is my conclusion correct???????????????

2. even though for clarity sake you have shows the data under each scenario in columns A to G, I to O and Q to V etc
but in the actual sheet ALL DATA WILL BE ONLY IN COLUMNS A TO G
CONFIRM

I have started writing the macro and got into the difficulty of item 1 above.
SKhanY Posts 7 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011
May 25, 2011 at 03:21 AM
Sorry for inconvenience.. You got it right... I somehow changed the value but couldn't save it..
And Yes this was made for comparison purpose... all the sheets will have save number of columns for each month... so this means April 2nd, 3rd.... will follow April 1st.. till 30th...
Thanks!!!!
Khan