Update statement using if

**mindbender** · 09-12-2017, 05:48 AM

I am hoping to get it to look like this once finished only with the subtotals populating correctly

Click image for larger version.

Name: Capture.JPG
Views: 7
Size: 52.8 KB
ID: 30283

**mindbender** · 09-12-2017, 07:13 AM

I changed qry_monthly_export to

Code:

TheValue: Sum(IIf([FldName]="DIALS",[DIALS].[DIALS],CStr([TALKTIME].[TT]/86000)))

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:

 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

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.

Is there a way to get this to work?
Thanks for all the help. I am so close.

**June7** · 09-12-2017, 01:15 PM

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

**mindbender** · 09-12-2017, 01:47 PM

I have gotten this far

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)

It works as expected until I uncomment out the subtotal line then it throws an error.

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

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)[/

and so on until I have all the possible columns (AW) done?

I really do appreciate the help and apologize for my lack of knowledge. I am in over my head for sure

**June7** · 09-12-2017, 01:55 PM

Code I am not familiar with and can't test it so you are on your own now unless someone else pops in.

**mindbender** · 09-13-2017, 05:39 AM

Thank you for all your help. I truly appreciate it.

I will keep trying things and maybe someone else will chime in

**June7** · 09-13-2017, 11:06 AM

Explicit cell reference like that in the function won't work. Try:

If Not IsEmpty(ActiveSheet.Range("G1")) Then

**mindbender** · 09-13-2017, 11:23 AM

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.

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)

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.

Is there a way to do this?

**June7** · 09-13-2017, 11:36 AM

Bing: Excel TotalList

This seems to address your issue https://www.mrexcel.com/forum/excel-...-subtotal.html

**mindbender** · 09-13-2017, 01:19 PM

Thank you for all of your help. I got it to work.

here is how it ended up

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
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

I am still doing testing but so far it looks to be exactly what I needed

Update statement using if

Thread Tools

Similar Threads

SQL UPDATE statement

VBA Update Statement to update Date field

Update to SQL statement

UPDATE TO IIF Statement

SQL Update statement help

Posting Permissions