Find error?

[ecarney14]

Hey, I have been working on this macro to analyze data. And I keep getting an error when i try to find lRowMin. It's driving me nuts, so any help would be greatly appreciated.

Thanks a lot.

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


Thanks again in advance.

Erin

 

[PHV]

I keep getting an error
any chance you could post the whole error message and which line is highlighted when in debug mode ?

Anyway, why are you starting your search from the last row ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ecarney14]

I get Runtime error '91'
Object Variable or with Block variable not set.

Anyway, why are you starting your search from the last row ?
I don't know exactly what you mean....how am i looking at the last row?


Thanks.

 

[PHV]

In fact I don't understand the red part:
lRowMax = rR.Find(iMax, [!]rR.Cells(rR.Rows.Count)[/!]).Row

you may try this instead:
lRowMax = rR.Find(What:=iMax, LookIn:=xlValues)).Row

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ecarney14]

That's what I dont understand. It works for the lRowMax, but not for the lRowMin.

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(What:=iMin, LookIn:=xlValues).Row

The (rR.Rows.Count) was meant to look in the range and count the actual row that the iMax was in.

If it helps at all. When getting the error messaage, and scrolling over the yellow highlighted line. It shows:

lRowMin= 0 the iMin = 4.8234328 E -05 and x1Values = -4185

But I don't understand the 0, or the -4185

Thanks, I know this is a little confusing the way i am writing it.