Global Range Problem

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Here's my macro

Sub FindMax()

'Find largest number in B column
i = 1

Do Until i = 4000
If Range("B" & i).Value >= Range("B" & i + 1).Value Then
i = i + 1
Else
imax = Range("B" & i + 1)
icell = i + 1
i = i + 1
End If
Loop

'Find second largest number in B column
i = 2
icell = icell - 1
ifmax = Range("B1").Value

Do Until i = icell
If ifmax >= Range("B" & i + 1).Value Then
i = i + 1
Else
ifmax = Range("B" & i + 1).Value
i = i + 1
End If
Loop

'Find min between two max numbers
i = ifmax
imin = ifmax

Do Until i = icell
If imin > Range("B" & i + 1).Value Then
imin = Range("B" & i + 1).Value
i = i + 1
Else
i = i + 1
End If
Loop


MsgBox ("First max: " & ifmax & " Overall max: " & imax & " Inside min: " & imin)

End Sub

It works for smaller samples, yet keeps screwing up on the blue line in bigger samples for some reason...


any help is appreciated!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ecarney14

I believe you are not initialising the variable i correctly in the loop that finds the min. You have:

i = ifmax

and I think you mean

i = row of the cell with value ifmax

You don't have the value of that row because you defined icell to store the row of the first max that you search, but you did not define any variable to store the row of the second max that you search. What you have to do is to define an icell2 that will store the row of the second max and then use it to initialise "i" in the last loop.

By the way, if I understand what you want, you can use the workheet functions instead of the loops.

Something like (if you want please test and maybe add error handling):

Code:
Sub MaxMin()
Dim iMax As Double, iFMax As Double, iMin As Double, lRowMax As Long, lRowMax2 As Long

iMax = Application.WorksheetFunction.Max(Range("B1:B4000"))
lRowMax = Range("B1:B4000").Find(iMax).Row
iFMax = Application.WorksheetFunction.Max(Range("B1:B" & lRowMax - 1))
lRowMax2 = Range("B1:B4000").Find(iFMax).Row
iMin = Application.WorksheetFunction.Min(Range("B" & lRowMax2 + 1 & ":B" & lRowMax - 1))
MsgBox ("First max: " & iFMax & " Overall max: " & iMax & " Inside min: " & iMin)
End Sub

Hope this helps
PGC
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Oh wow. Thanks!

With my data it seems to flucuate a little bit, and since I can't really figure out a good macro to eliminate that problem. I might just use yours to find the max, then the minimum before that point.

I was trying to find ifmax in order to eliminate having to edit the data before we put it in excel. Ah well.

Thanks for your help!


Erin
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Just curious,

do you know how to find the first max in a set of data?
This is all for research, so it is according to time.
In the previous macro I was getting the second highest maximum. Yet I am wondering if you can find the highest max that comes earliest. If that makes sense...
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again

I'm not sure if I understand what is the first max.

I assumed it was the first value bigger than the next value.

If that is the case, for example:

Code:
Sub FirstMax()
Dim rC As Range

For Each rC In Range("B1:B4000")
    If rC.Value > rC.Offset(1).Value Then Exit For
Next
Msgbox "First Max: " & rC.Value
End Sub

Hope this is what you want
PGC
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Kinda, Actually now I have been playing around with your first recommendation and I had a couple questions (sorry if they are basic, i am just getting the hang of these things)

1.What does the Double do?
Dim iMax As Double, iFMax As Double, iMin As Double, lRowMax As Long, lRowMax2 As Long

2. Once the iMin is found, how can I subtract the imin from each value in cells B2:B400 and display them in Column C?

Thanks. for all the help!!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again

1.What does the Double do?

Double is one of vba basic data types. Since I didn't know the nature of your numbers I didn't want to assume that they were integers. Double is a floating point.

From the help

Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

Remark: If your numbers are integers declare them as Integer or if they are big as Long.


2. Once the iMin is found, how can I subtract the imin from each value in cells B2:B400 and display them in Column C?

2 possible ways

a loop


Code:
Dim rC As Range

For Each rC In Range("b2:b400")
    rC.Offset(, 1) = rC - iMin
Next

a much quicker solution would be to use pastespecial. Assuming that imin is in the range b2:b400

Code:
Range("C2:C400").Value = Range("B2:B400").Value
Range("B2:B400").Find(imin).Copy
Range("C2:C400").PasteSpecial Operation:=xlPasteSpecialOperationSubtract

Remark: for a small range you may not notice difference in performance, but for a large range the second solution is much faster.

Hope it was clear
PGC
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Awesome, thanks for the clarification!

Now I just have to put it for more columns. Is there any trick for using the whole thing? Like The data is in Columns B-AW on sheet one, and I can put the difference from min on B-AW on sheet 2.

Thanks again
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ecarney14

You can use the same solutions but you have to reference the worksheets.

For example, for the loop solution

Code:
Dim rC As Range

For Each rC In Worksheets("Sheet1").Range("B1:AW500")
    Worksheets("Sheet2").Cells(rC.Row, rC.Column) = rC - imin
Next

Kind regards
PGC
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
Thank you so much. You really helped me out!! I'd offer you a stein at Oktoberfest, but I doubt you are in the Munich area...


Thanks again

Erin
 

Forum statistics

Threads
1,141,625
Messages
5,707,470
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top