Global Range Problem

[ecarney14]

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!

 

[pgc01]

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

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]

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]

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]

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:

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]

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]

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

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

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]

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]

Hi ecarney14

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

For example, for the loop solution

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]

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