VBA for to - Error 1004

[konradz]

Hi,

I'm drafting a simple VBA code, I would like to use it to cycle through different options and print a set of tables corresponding to combinations of different options. I'm using For To commands and counts to select different options. Unfortunately, I keep on getting 1004 error. I'm wondering if someone could help me with this small assignment? My code looks like follows:

Private Sub PrintAllTables_Click()
'
' PrintAllTables Macro
' Prints all tables for the workbook.
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim count As Integer
Dim year(2) As String
Dim service(2) As String
Dim countB As Integer
Dim countC As Integer

year(1) = "2010"
year(2) = "2011"

service(1) = "People Services"
service(2) = "Other Services"

For count = 1 To 4
   count_str = CStr(count)
   Range("family_number").Value = count
   Application.Run "Update"
    For countB = 1 To 2
        Range("year_selection").Value = year(countB)
        Application.Run "Update"
           For countC = 1 To 2
                Range("service_area").Value = service(countC)
                Application.Run "Update"
                             
                               
            Next
        Application.Run "Update"
    Next
Next
    MsgBox "PDFs were generated successfully.", vbInformation + vbOKOnly, "Macro finished"
End Sub

[Norie]

Konrad

Where/when do you get the error?

Also, where is the sub Update located and how is it declared?

Oh, and why do you run it 3 times?

[konradz]

Norie,

Thank you very much for taking interest in my modest post. The idea behind the macro is to generate set of PDF with use of a small table. Table represents values for different combinations of indicators, years and group. I've two groups for the indicator, two years and for family groups. Consequently, I've to genereta 2 x 2 x 4 tables. I get the error while trying to compile the code but there is no reference to a particular cell. Macro is assigned to a button in a worksheet. By the way, I updated the code to generate PDFs:

Private Sub PrintAllTables_Click()
'
' PrintAllTables Macro
' Prints all tables for the workbook.
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim count As Integer
Dim year(2) As String
Dim service(2) As String
Dim countB As Integer
Dim countC As Integer



year(1) = "2010"
year(2) = "2011"

service(1) = "People Services"
service(2) = "Other Services"


Set WshShell = CreateObject("WScript.Shell")
strDocuments = WshShell.SpecialFolders("MyDocuments")


For count = 1 To 4
   count_str = CStr(count)
   Range("family_number").Value = count
   Application.Run "Update"
    For countB = 1 To 2
        Range("year_selection").Value = year(countB)
        Application.Run "Update"
           For countC = 1 To 2
                Range("service_area").Value = service(countC)
                Application.Run "Update"
                             
                             
                             
                For Each x In Range("a1:ee21").Cells
                If x.Value <> "" And x.Row > maxrow Then maxrow = x.Row
                If x.Value <> "" And x.Column > maxcol Then maxcol = x.Column
                Next x
                Set lastcell = Range("a1").Offset(maxrow, maxcol)
                Set printcells = Range("a1" & ":" & lastcell.Address)
                printcells.Select
                ActiveSheet.PageSetup.PrintArea = printcells.Address
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strDocuments & "family_" & (count) & "_" & year(countB) & "_" & service(countC) & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
                             
                             
                             
                               
            Next
        Application.Run "Update"
    Next
Next
    MsgBox "PDFs were saved in your documents.", vbInformation + vbOKOnly, "Macro finished"
End Sub

[konradz]

The macro stops on:

               Range("service_area").Value = service(countC)

I did change the update procedure to Application.Calculate

[konradz]

Everything is working like a charm, mistakenly I had a wrong name in a range in Excel