I changed qry_monthly_export to
This now enables me to use the section of the code NumberFormat='[h]:mm:ss" to format the field and the subtotaling works perfect.Code:TheValue: Sum(IIf([FldName]="DIALS",[DIALS].[DIALS],CStr([TALKTIME].[TT]/86000)))
The only issue I have now is with the ranges to get the subtotals to work. Since this runs every day there will not always be data in the columns looking to be formatted or subtotaled and this causes it to error out. I tried using If NOTISNULL to try and have it only run on the columns with data in them. Although this seems to run without throwing an error it does not update any of the columns or subtotal them.Code:On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit ' If NotIsNull Then ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("G:G").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("I:I").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("K:K").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("K:K").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("M:M").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("O:O").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("Q:Q").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("S:S").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("U:U").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("W:W").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("Y:Y").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AA:AA").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AC:AC").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AE:AE").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AG:AG").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AI:AI").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AK:AK").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AM:AM").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AO:AO").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AQ:AQ").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AS:AS").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AU:AU").NumberFormat = "[h]:mm:ss" ActiveSheet.Range("AW:AW").NumberFormat = "[h]:mm:ss" Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49) ' End If Else ErrorYes = True End If End With
Is there a way to get this to work?
Thanks for all the help. I am so close.
Glad you got that much working, although I don't understand the format mask. Why is h in [] and I thought nn had to be used for minutes because mm is for month.
And I am now confused about conversion to string - if not exporting as time format why bother with any string conversion? If displaying as time format in Access is no longer a requirement then why convert number to a string?
Not IsNull() has to specify what is being tested for Null, such as a cell reference.
If Not IsNull(ActiveCell) Then
Cells can appear Null and not really be. Maybe use IsEmpty or test length of cell contents: Len(ActiveCell)>0
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I have gotten this far
It works as expected until I uncomment out the subtotal line then it throws an error.Code:If Not IsEmpty("E1") Then ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss" ' ActiveSheet.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5)
Seems to work in my other db. Probably something dumb I am doing.
Once I get this one to work will I have to do something like this to finish out the section
and so on until I have all the possible columns (AW) done?Code:If Not IsEmpty("G1") Then ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit ActiveSheet.Range("G:G").NumberFormat = "[h]:mm:ss" ' ActiveSheet.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5,6,7)[/
I really do appreciate the help and apologize for my lack of knowledge. I am in over my head for sure
Code I am not familiar with and can't test it so you are on your own now unless someone else pops in.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you for all your help. I truly appreciate it.
I will keep trying things and maybe someone else will chime in
Explicit cell reference like that in the function won't work. Try:
If Not IsEmpty(ActiveSheet.Range("G1")) Then
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
so I have moved to this to Bold the first row and Format every second row in the range which makes it so I don't have to call each column individually.
The last issue I am having is the TotalList=Array section of the subtotal. How can I get that array to stretch and contract and only subtotal the columns with data? I tried recalling the usedrange but it didn't like that.Code:On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then Worksheets("qry_Daily_Export").UsedRange.Select ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit For I = Range("E1").Column To Range("AW1").Column Step 2 Columns(I).NumberFormat = "[h]:mm:ss" Next I 'ActiveSheet.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(Worksheets("qry_Daily_Export").UsedRange)
Is there a way to do this?
Bing: Excel TotalList
This seems to address your issue https://www.mrexcel.com/forum/excel-...-subtotal.html
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you for all of your help. I got it to work.
here is how it ended up
I am still doing testing but so far it looks to be exactly what I neededCode:On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then Worksheets("qry_Daily_Export").UsedRange.Select ActiveSheet.Range("1:1").Font.Bold = True ActiveSheet.Range("1:1").EntireColumn.AutoFit For i = Range("E1").Column To Range("AW1").Column Step 2 Columns(i).NumberFormat = "[h]:mm:ss" Next i Dim myarr() finalcolumn = Cells(1, Columns.Count).End(xlToLeft).Column x = finalcolumn ReDim myarr(x - 4) For i = 4 To x myarr(i - 4) = i Next i Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=myarr