Imin

[ecarney14]

Here is my macro

Sub c()

Dim iMax As Double, iMin As Double, lRowMax As Long

iMax = Application.WorksheetFunction.Max(Range("C2:C20000"))
lRowMax = Range("C2:C20000").Find(iMax).Row
iMin = Application.WorksheetFunction.Min(Range("C2" & ":C" & lRowMax - 1))

Dim rAX As Range

For Each rAX In Range("C2:C20000")
rAX.Offset(, 47) = rAX - iMin

Next
End Sub


I would like to change the highlighted part, so it only takes after the difference after the i min, for example the imin was in row 3, take the difference of row 4 and higher...

also is there anyway to mass produce it so that i can use the same code for each column (D, E, F... correlating to AX, AY, AZ etc)

Thanks.

 

[pgc01]

Hi ecarney14

You have to find where the iMin is and only loop after that row

Please try:

Sub c()

Dim iMax As Double, iMin As Double, lRowMax As Long, lRowMin As Long

iMax = Application.WorksheetFunction.Max(Range("C2:C20000"))
lRowMax = Range("C2:C20000").Find(iMax).Row
iMin = Application.WorksheetFunction.Min(Range("C2" & ":C" & lRowMax - 1))
lRowMin = Range("C2:C20000").Find(iMin, Range("C20000")).Row

Dim rAX As Range

For Each rAX In Range("C" & lRowMin + 1 & ":C20000")
    rAX.Offset(, 47) = rAX - iMin
Next
End Sub

I haven't had the time to test it properly because I'm leaving. If it's not OK I'll check your posts later.

Hope it's OK
PGC

 

[ecarney14]

Thanks,

That's kinda what I had too. But the only problem is. It will work for C, but as I try to reproduce it. For example :

Sub D()

Dim iMax As Double, iMin As Double, lRowMax As Long, lRowMin As Long

iMax = Application.WorksheetFunction.Max(Range("D2:D20000"))
lRowMax = Range("D2:D20000").Find(iMax).Row
iMin = Application.WorksheetFunction.Min(Range("D2" & ":D" & lRowMax - 1))
lRowMin = Range("D2:D20000").Find(iMin, Range("D20000")).Row

Dim rAZ As Range

For Each rAZ In Range("D" & lRowMin + 1 & ":D20000")
rAZ.Offset(, 48) = rAZ - iMin
Next

End Sub

It won't work. I need to do the same thing as C for columns B-AW, where I find the min for each column... and so on. Is there another way to do it other than making seperate macros for each one.

 

[pgc01]

Hi again

I added a loop through the columns. Notice that in the first loop you have from 3 to 7, that' just for testing, columns C to G. After testing replace the 7 by 49 (column AW).

Please test it and let me know how it works. I've just tested but ...

Sub CopyMinMax()
Dim iMax As Double, iMin As Double, lRowMax As Long, lCol As Long
Dim rAY As Range, rR As Range

Application.ScreenUpdating = False
For lCol = 3 To 7 '49  from column C to AW
    
    Set rR = Range(Cells(2, lCol), Cells(20000, lCol))
    iMax = Application.WorksheetFunction.Max(rR)
    lRowMax = rR.Find(iMax, rR.Cells(rR.Rows.Count), LookIn:=xlValues).Row
    iMin = Application.WorksheetFunction.Min(rR.Resize(lRowMax - rR.Row))
    lRowMin = rR.Find(iMin, rR.Cells(rR.Rows.Count)).Row
    
    For Each rAY In Range(Cells(lRowMin + 1, lCol), Cells(20000, lCol))
        rAY.Offset(, 44) = rAY - iMin
    Next rAY
Next lCol
Application.ScreenUpdating = True
End Sub

Hope it works
PGC

 

[ecarney14]

Hey,

First off thanks for all your help. I think this macro is starting to get a little bit over my head, and I guarentee I wouldn't have gotten this far if it wasn't for all your advice. I tried the latest one. At first it got an error

lRowMax = rR.Find(iMax, rR.Cells(rR.Rows.Count), LookIn:=xlValues).Row

line. So I deleted the LookIn part of it, and it works. But now I am getting an error in the

lRowMin = rR.Find(iMin, rR.cells(rR.Rows.Count)). Row

It Shows that lRowMin = 0 when you scroll over it... and I am wondering if that is the problem. If you think of what it might be let me know. I'll probably be messing around with it until I can get it to work today.

Thanks again

Erin


Ps. Here's the amended version, so that you can see what i am working with.

iMax = Application.WorksheetFunction.Max(rR)
lRowMax = rR.Find(iMax, rR.Cells(rR.Rows.Count)).Row
iMin = Application.WorksheetFunction.Min(rR.Resize(lRowMax - rR.Row))
lRowMin = rR.Find(iMin, rR.Cells(rR.Rows.Count)).Row

 

[ecarney14]

What I think the problem is now, the Range in which it is searching for lRowMin needs to be fixed. Because it needs to be the Resize Range rather than all 20000 rows. I am guessing maybe sometimes there is a similar number after the Max in which the macro would yield more than 1 lRowMin. But the only one I care about comes before the lRowMax.... if that makes sense to you.


Again, thanks for your help. I will keep fiddling with it, but let me know if you think of anything else.

 

[ecarney14]

I also tried this....

Sub carney()

Dim iMax As Double, iMin As Double, lRowMax As Long, lRowMin As Long, lCol As Long
Dim rAY As Range, rR As Range, rE As Range

Application.ScreenUpdating = False
For lCol = 3 To 7 '49 from column C to AW

Set rR = Range(Cells(2, lCol), Cells(35000, lCol))

iMax = Application.WorksheetFunction.Max(rR)
lRowMax = rR.Find(iMax, rR.Cells(rR.Rows.Count)).Row

Set rE = Range(Cells(2, lCol), Cells(lRowMax, lCol))
iMin = Application.WorksheetFunction.Min(rE)
lRowMin = rE.Find(iMin, rE.Cells(rE.Rows.Count)).Row



For Each rAY In Range(Cells(lRowMin + 1, lCol), Cells(35000, lCol))
rAY.Offset(, 44) = rAY - iMin
Next rAY
Next lCol

Application.ScreenUpdating = True
End Sub

and it still gets angry at the lrowMin Line....

 

[pgc01]

Hi ecarney14

I'm sorry it's not working for you. As I said I tested and it worked pretty well, but there is something that is preventing it from working for you. However, I'm sure you'll find it out.

First please try to introduce in the Finds another paramenter that has no default value, LookAt. This parameter tells Find to look either at parts of the cell or at the whole cell. Since it has no default value you may have it set to part and this means that if you found a value of 2 as the minimum, then Find could stop at 12 since it has a 2 inside.

So please try to add to the Finds

lRowMax = rR.Find(iMax, rR.Cells(rR.Rows.Count), LookIn:=xlValues, LookAt:=xlWhole).Row

lRowMin = rR.Find(iMin, rR.Cells(rR.Rows.Count),, LookIn:=xlValues, LookAt:=xlWhole).Row


Let me tell you how I would debug it. Maybe you can get ideas from it.

First I would restrict the range to the minimum. Instead of going for lots of columns and lots of rows I would go for 1 column and only some rows, meaning

For iCol = 3 to 3

Set rR = Range(Cells(2, lCol), Cells(20, lCol))

and adjust the rest of the code accordingly. With this setup I could work in a test worksheet and enter and change values manually.

When I had it working with no errors then I would increment the number of columns, leaving just some rows, so that I can look easily at all the values.

Remarks about things you said:

"So I deleted the LookIn part of it, and it works. " - this should not happen. that parameter means: look in the values of the cells and not in the formulas.

"the Range in which it is searching for lRowMin needs to be fixed ... I am guessing maybe sometimes there is a similar number after the Max ..."
You shouldn't have to worry about it, the second parameter of Find deals with it:

lRowMin = rR.Find(iMin, rR.Cells(rR.Rows.Count)).Row

The second parameter is the last cell in your range. The Find will start in the next cell. Since Find wraps around the range this means the next cell is the first cell in the range.


Another thing. All the values must be numbers, Please check that some numbers were not entered or imported as text.

As a final remark, working with few columns and few rows in a new worksheet with values entered manually will help a lot finding the problem.

By the looks of it, the bug seems to be some detail we are overlooking. Once you find the bug everything will start working.

I hope that the introduction of the LookAt parameter in the Find will help.

Good Luck. Please give news.
PGC

 

[ecarney14]

To clear up some of your questions. The values in the cells are all numbers so that shouldn't be a problem. I did make up my own data set and got it to work just fine. I am still perplex as to why with the larger data sets the macro will not find the lRowMax or lRowMin. It still brings up global range failure.

Not cool. haha... But I think I am going to take some of the data sets and work on this at home after the lab today, if you think of any other suggestions definetly let me know.

I am wondering if I should use Vlookup or perhaps some if statement. To ensure the program working...

Thanks for all your help though! You have been fantastic!

 

[pgc01]

Hi ecarney14

Unfortunately I don't have any new idea about your problem.

I just think that it was a big step forward that you got it to work with your own data set.

I think that you can start now wih the real data. First also start with some rows and then little by little augment the number of rows until you get the range error. Maybe like that you can understand why it happens. I tried it again and couldn't make it fail.

Good luck
PGC