Loop to choose different ranges and print into PDF

Question

I have different monthly invoice to print to pdf within one worksheet. Each statement has same size of 5*35(W&H) and the range is selected by its unique code at column A I tried to do loop to shorten the code but the Rng(i) only capture partially when print out

Sub lengthy()
    Dim searchString(1 To 2) As String
    Dim searchColumn As Integer
     
    Worksheets("Fire & GA").Select
    'Populate the array
    searchString(1) = "TR2021000"
    searchString(2) = "TR2020000"
    searchColumn = 1
    Dim Rng(1 To 2) As Range
    Dim m(1 To 2) As Variant
    
    With Worksheets("Fire & GA")
        m(1) = WorksheetFunction.Match(searchString(1), Range("A:A"), 0)
        m(2) = WorksheetFunction.Match(searchString(2), Range("A:A"), 0)
    
        Set Rng(1) = Range(Cells(m(1), searchColumn).Offset(-12, 0), Cells(m(1), searchColumn).Offset(23, 4))
        Set Rng(2) = Range(Cells(m(2), searchColumn).Offset(-12, 0), Cells(m(2), searchColumn).Offset(23, 4))
    
        Dim strFileName(1 To 2) As String
        strFileName(1) = searchString(1) & ".pdf"
        strFileName(2) = searchString(2) & ".pdf"
    searchColumn = 1
        Rng(1).ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strFileName(1), _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        Rng(2).ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strFileName(2), _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    
End Sub

If somebody could help me would be much appreciated

Answer 1

It is best to break the problem down into small easy to test methods.

Here is a simple way to ensure that you are referencing the correct Range:

Application.Goto FireAndGAExportRange("TR2021000")

Sub Shorter()
    Dim SearchStrings As Variant
    SearchStrings = Array("TR2021000", "TR2020000", "KK")
    
    Dim Target As Range
    Dim Item As Variant
    Dim FileName As String
    
    For Each Item In SearchStrings
        Set Target = FireAndGAExportRange(CStr(Item))
        If Target Is Nothing Then
            Debug.Print Item; " not found"
        Else
            FileName = Item & ".pdf"
            Target.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next
End Sub

Function wsFireAndGA() As Worksheet
    Set wsFireAndGA = ThisWorkbook.Worksheets("Fire & GA")
End Function

Function FireAndGAExportRange(SearchString As String) As Range
    Dim RowIndex As Variant
    With wsFireAndGA
        Rem Exit if no values are in Column A
        If .UsedRange.Column > 1 Then Exit Function
        
        RowIndex = Application.Match(SearchString, .UsedRange.Columns("A"), 0)
        Rem Exit if no match is found
        If IsError(RowIndex) Then Exit Function
        Set FireAndGAExportRange = .Cells(RowIndex, 1).Offset(-12, 0).Resize(35, 5)
    End With
End Function