# Global Range Problem

#### ecarney14

##### New Member
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
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
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.

Erin

#### ecarney14

##### New Member
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

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
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

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
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
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
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

Replies
6
Views
199
Replies
0
Views
260
Replies
2
Views
2K
Replies
4
Views
157
Replies
2
Views
89

### Forum statistics

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.

### Which adblocker are you using?    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

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