Defining a Range in Excel 1

[djburnheim]

I'm need to use the Max worksheet function but am having trouble because the range I want to search keeps changing. The first cell in the range is always the same but the last one changes. I can get the cell reference for the last cell ok but can't work out how to define the range using it.

***code***
' Search for last row containing data
With Sheets(4).Range("A1.A200&quot
Set DataEnd = .Find("Total for Admin&quot
If Not DataEnd Is Nothing Then
LastRow = DataEnd.Offset(-3, 6).Row
End If
End With

Sheets(1).Cells(dRow + 2, dColumn) = Application.WorksheetFunction.Max(Sheets(4).Range(Cells(15, 7).Cells(LastRow, 7)))

 

[Stretchwickster]

This line:

Sheets(1).Cells(dRow + 2, dColumn) = Application.WorksheetFunction.Max(Sheets(4).Range(Cells(15, 7).Cells(LastRow, 7)))

should be:

Sheets(1).Cells(dRow + 2, dColumn) = Application.WorksheetFunction.Max(Sheets(4).Range(Cells(15, 7), Cells(LastRow, 7)))

i.e. there should be a comma between the last 2 Cells statements.

Hope this helps!



Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[kphu]

I also noticed that this line:

With Sheets(4).Range("A1.A200&quot

should be

With Sheets(4).Range("A1:A200&quot

there should be a colon instead of a period.

 

[djburnheim]

I was hoping that it was going to be something simple but I changed the line as suggested but still get the run time error..anything else it could be? (I have set the value for dRow further back).

Thanks again
Dave

 

[Zathras]

Why not just use a formula?

Assuming your "Sheets(4)" has the name on the tab of "Expenses", you could just put this formula in "Sheets(1)" where you want the total:
[blue]

=SUM(INDIRECT("'Expenses'!G15:G"&(MATCH("Total for  Admin",Expenses!A1:A205,0)-3)))

[/color]
Of course, if you change the name on the tab, you will have to modify the formula. But then using "Sheets" to reference the sheets, if the user changes the order of the sheets, the code will no longer work.

 

[djburnheim]

I understand the point about using the sheet name rather than Sheets but in this case the sheet is deleted before the user can change the position..also I'm not trying to sum the data...I need to find the maximum value in the column.

 

[Zathras]

Sorry, all you have to do is change "SUM" to "MAX" in the formula.

But I don't understand about ...the sheet is deleted before the user can change the position... What does deleting a sheet have to do with anything? Can you really guarantee that the user isn't able to move sheets 1 thru 4 around?

You could get better assistance (from any of us here) if you would provide more details on just what exactly you are needing to do. It may be that VBA is the way to go, but it's hard to know from what you've posted.

 

[djburnheim]

I have a system that exports 3 different reports to excel spreadsheets. I have another spreadsheet that I collate the data on. My macro opens the other workbook, copies the sheets to the master spreadsheets, copies the required data and then deletes the sheets and source files..I don't know if I'm doing it the best way but it works except for the code above....my code is below

Dave

Sub DoDailyStats()

'Copy sheets to main workbook
Dim Summary, Answered, Abandoned As String

Summary = "C:\Statistics\Daily Stats\Summary.xls"
Answered = "C:\Statistics\Daily Stats\Answered.xls"
Abandoned = "C:\Statistics\Daily Stats\Abandoned.xls"

Workbooks.OpenText FileName:=Summary
Sheets(1).Select
Sheets(1).Move After:=Workbooks("May Daily Stats.xls&quot.Sheets(1)

Workbooks.OpenText FileName:=Answered
Sheets(1).Select
Sheets(1).Move After:=Workbooks("May Daily Stats.xls&quot.Sheets(2)

Workbooks.OpenText FileName:=Abandoned
Sheets(1).Select
Sheets(1).Move After:=Workbooks("May Daily Stats.xls&quot.Sheets(3)

' Find column in main sheet for report date
Sheets(2).Cells(4, 3) = Format(Sheets(2).Cells(5, 3), "dd-MMM&quot
ReportDate = Sheets(2).Cells(4, 3)

With Sheets(1).Range("a1:q40&quot
Set dCell = .Find(ReportDate)
If Not dCell Is Nothing Then
dColumn = dCell.Column
dRow = 2
End If
End With

'Admin Longest Queue Answered
Sheets(1).Cells(dRow + 1, dColumn) = Sheets(2).Range("I14&quot

'TSD Longest Queue Answered
Sheets(1).Cells(dRow + 9, dColumn) = Sheets(2).Range("I15&quot

'Admin Longest Queue Abandoned
With Sheets(4).Range("A1:A200&quot
Set DataEnd = .Find("Total for Admin&quot
If Not DataEnd Is Nothing Then
lRow = DataEnd.Offset(-3, 6).Row
nRow = DataEnd.Offset(2, 6).Row
End If
End With

Sheets(1).Cells(dRow + 2, dColumn) = Application.WorksheetFunction.Max(Sheets(4).Range(Cells(15, 7), Cells(lRow, 7)))

'TSD Longest Queue Abandoned
With Sheets(4).Range("A1:A200&quot
Set DataEnd = .Find("Total for Technical&quot
If Not DataEnd Is Nothing Then
lRow = DataEnd.Offset(-3, 6).Row
End If
End With

Sheets(1).Cells(dRow + 10, dColumn) = Application.WorksheetFunction.Max(Sheets(4).Cells(nRow, 7), Cells(lRow, 7))

'Admin Average Queue Time Answered
With Sheets(3).Range("A1:A200&quot
Set AvAns = .Find("Total for Admin&quot
If Not AvAns Is Nothing Then
Sheets(1).Cells(dRow + 3, dColumn) = AvAns.Offset(1, 5)
End If
End With

'TSD Average Queue Time Answered
With Sheets(3).Range("A1:A200&quot
Set AvAns = .Find("Total for Technical&quot
If Not AvAns Is Nothing Then
Sheets(1).Cells(dRow + 11, dColumn) = AvAns.Offset(1, 5)
End If
End With

'Admin Average Queue Time Abandoned
With Sheets(4).Range("A1:A200&quot
Set AvAban = .Find("Total for Admin&quot
If Not AvAban Is Nothing Then
Sheets(1).Cells(dRow + 4, dColumn) = AvAban.Offset(1, 6)
End If
End With

'TSD Average Queue Time Abandoned
With Sheets(4).Range("A1:A200&quot
Set AvAban = .Find("Total for Technical&quot
If Not AvAban Is Nothing Then
Sheets(1).Cells(dRow + 12, dColumn) = AvAban.Offset(1, 6)
End If
End With

'Admin Calls Offered
Sheets(1).Cells(dRow + 5, dColumn) = Sheets(2).Range("D14&quot

'TSD Calls Offered
Sheets(1).Cells(dRow + 13, dColumn) = Sheets(2).Range("D15&quot

'Admin Calls Answered
Sheets(1).Cells(dRow + 6, dColumn) = Sheets(2).Range("E14&quot

'TSD Calls Answered
Sheets(1).Cells(dRow + 14, dColumn) = Sheets(2).Range("E15&quot

'Admin Calls Abandoned
Sheets(1).Cells(dRow + 7, dColumn) = Sheets(1).Cells(dRow + 5, dColumn) - Sheets(1).Cells(dRow + 6, dColumn)

'TSD Calls Abandoned
Sheets(1).Cells(dRow + 15, dColumn) = Sheets(1).Cells(dRow + 12, dColumn) - Sheets(1).Cells(dRow + 13, dColumn)

Kill Summary
Kill Answered
Kill Abandoned

'Delete Worksheet 4 (Abandoned)
Application.DisplayAlerts = False
Sheets(4).Select
ActiveWindow.SelectedSheets.Delete

'Delete Worksheet 3 (Answered)
Application.DisplayAlerts = False
Sheets(3).Select
ActiveWindow.SelectedSheets.Delete

'Delete Worksheet 2 (Summary)
Application.DisplayAlerts = False
Sheets(2).Select
ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = True
Sheets(1).Select
Cells(2, 1).Select

End Sub

 

[Zathras]

So far, it looks like it may be a data problem. Do you have values in column "G" of the "Abandoned" sheet beginning with row 15? Once I plugged in some numbers it seemed to go ok. (At least as far as the "Kill" statements.)

 

[djburnheim]

Zathras...thanks for helping, I've found the problem. It was the data as you suggested but it's not that it wasn't there, it's because it is in time format (hh:mm:ss)...now all I need to do is workout how to find max time or I'll have to convert all the data first.

 

[djburnheim]

Ok I'm stuck! I can't find anyway of finding max time and I can workout how to convert the data from hh:mm:ss to a decimal value. Any ideas??

Thanks again
Dave

 

[Zathras]

What exactly does your data look like? I just tried opening a text file with values like this:

   08:13:15
   12:12:12
   14:14:14

and they converted correctly into time values that allowed the use of the worksheet function =MAX(A1:A3) and correctly returned 14:14:14.

Your data must be in some other shape than hh:mm:ss as you posted. Perhaps there are leading or trailing blanks?