MAX in VBA not working

[tcnolan]

Hi,

I basically have a sheet with start and end dates (stored as text i.e.
"Week Ending 11/05/05"). I have another sheet which lists 52 weeks of
sales data.

I need to take each start/end date combination and look up those dates
to determine a range and find the max value in the Total Sales column
of that range.

My code seems to be working except the first range always returns 0.
Then the second and third range return the correct values.

Here is some of the code:
********************************************************************
'loop thru to find the max value of each past program using the start
and end dates
For Each CELL In Worksheets("Past Program Weeks").Range("H2:H" &
lastrowPast)
'get the values to lookup in the Raw Data sheet
startcell = CELL.Value
endcell = CELL.Offset(0, 1).Value
'lookup those dates and find the cell address
With currentbook.Sheets("Raw Data").Range("A4:A55")
Set rng = .Find(What:=startcell, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
With currentbook.Sheets("Raw Data").Range("A4:A55")
Set rng2 = .Find(What:=endcell, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

'set the range of data in the Raw Data sheet (14 = col N)
Set myrng = Range(Cells(rng2.Row, 14), Cells(rng.Row, 14))

'activate the Raw Data sheet
Set xlSht = currentbook.Sheets("Raw Data")
xlSht.Activate

'find the max value in that range
MaxValue = Application.WorksheetFunction.Max(myrng)

MsgBox MaxValue

Next CELL
********************************************************************

Any reason why wih the first range it would return 0 but the next time
it loops thru the Max value is correct?

Also, is there a way to return the cell address of the max value?

Thank you,

Terry

 

[Guest]

change

End With

'set the range of data in the Raw Data sheet (14 = col N)
Set myrng = Range(Cells(rng2.Row, 14), Cells(rng.Row, 14))

to

' End With

'set the range of data in the Raw Data sheet (14 = col N)
Set myrng = .Range(.Cells(rng2.Row, 14), .Cells(rng.Row, 14))
end with

since you apprently are working on the Raw Data sheet the first time (you
activate it after the first time).

the other option would be to activate it earlier.

 

[Sandy]

not sure why it's returning a 0 but use address for returning the
address ie, rng.Address, rng2.Address. If your code isn't too long
paste the whole thing so that we can see how you get to that point.

sandy

 

[Sandy]

to answer the cell address question use rng.Address, rng2.Address


Sandy

 

[Guest]

Missed the second question.

dim res as Variant
Dim rngFound as Range
Dim MaxValue as Double

MaxValue = Application.WorksheetFunction.Max(myrng)
res = application.match(MaxValue,myrng,0)
if not iserror(res) then
set rngFound = rng(res)
msgbox "Found at " & rngfound.Address & vbNewLine & _
"Row is: " & rngfound.row
end if

 

[tcnolan]

Hi Tom,

You were right. I had to activate the sheet earlier. I moved that up
before the set range and the Max function worked.

Your code for finding the cell address worked perfectly also.

Thanks so much for the help,

Terry