Number each Row when printing VBA

Question

I'm finding the matches in two columns (myrange1 & myrange2), filling them in a third column ("R") of sheet2. I have my Range from column "R" printing out to a PDF just fine, but I want each one to be numbered sequentially on the PDF i.e. 1,2,3,4 etc. Help much appreciated. Pretty new to VBA as well.

Sub matchcopy()
    Dim myrange1 As Range, myrange2 As Range, cell As Range

    With Sheets("Sheet1")
        Set myrange1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End With

    With Sheets("Sheet2")
        Set myrange2 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End With

    For Each cell In myrange1
        If Not IsError(Application.Match(cell.Value, myrange2, 0)) Then  
            'cell.Value, myrange2, 0
            cell.Copy
            Sheet2.Range("R5000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        Else
            'MsgBox "no match is found in range"
        End If
    Next cell

    Columns("R:R").EntireColumn.AutoFit
    Call Set_PrintRnag
End Sub


Sub Set_PrintRnag()
    Dim LstRw As Long
    Dim Rng As Range

    LstRw = Cells(Rows.Count, "R").End(xlUp).Row
    Set Rng = Range("R1:R" & LstRw)

    With ActiveSheet.PageSetup
        .LeftHeader = "&C &B &20 Cohort List Report : " & Format(Date, 
    "mm/dd/yyyy")
    End With

    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & _
      "\CohortList " & " " & Format(Date, "mm-dd-yyyy") & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

so why not add another column in front of the R column and add the row numbers then print the two columns. — Sorceri, Nov 05 '18 at 21:33
That's my problem. I cannot figure out how to number each result sequentially either in the matchcopy() sub routine or the Set_PrintRnag(). I have gotten it to print the numbers, but not the match that it found. I need the matching value also. I appreciate your response. — Curtis, Nov 05 '18 at 22:36
Just to clarify my first response, I can either get it to print the numbers, but no match or the match, but no numbers. — Curtis, Nov 05 '18 at 22:43

score 2 · Answer 1 · answered Nov 05 '18 at 21:37

Do you need a VBA script to accomplish your desired goal? If you are just trying to compare two values and output the result in your Column R, you should be able to do it with an IF function: https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

If you want sequential numbering for results, I'd suggest having the number in an adjacent column and exploring the COUNTA function: https://support.office.com/en-us/article/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509

And if you do require this in VBA scripting format, you can do it with an Excel function first and record a macro afterwards. Makes creating the actual VBA syntax a little easier! https://support.office.com/en-us/article/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b

I appreciate your response. I already have the result from the two compared values outputting to another column. My issue is numbering them either when I output them to the other column or when I print (export) them in the PDF. — Curtis, Nov 05 '18 at 22:39

T.M. · Accepted Answer · 2018-11-06T11:11:28.063

As close as possible to your code, though looping through a range is always time consuming and you would be faster working with arrays of the columns to be compared:

Option Explicit

Sub matchcopy()
    Dim i&
    Dim myrange1 As Range, myrange2 As Range, cell As Range
  ' You can use the Codenames instead of Worksheet("Sheet1") etc.
    Set myrange1 = Sheet1.Range("A1", Sheet1.Range("A" & Rows.Count).End(xlUp))
    Set myrange2 = Sheet2.Range("A1", Sheet2.Range("A" & Rows.Count).End(xlUp))
    Sheet2.Range("R:S") = ""                 ' <~~ clear result columns

    For Each cell In myrange1               ' presumably unique items
        If Not IsError(Application.Match(cell.Value, myrange2, 0)) Then
            cell.Copy
            With Sheet2.Range("R5000").End(xlUp)
                i = i + 1                   ' <~~ counter
                .Offset(1, 0) = i           ' counter i equals .Row - 1
                .Offset(1, 1).PasteSpecial xlPasteFormulasAndNumberFormats
            End With
        Else
            'MsgBox "no match is found in range"
        End If
    Next cell

    Sheet2.Columns("R:S").EntireColumn.AutoFit
    Call Set_PrintRnag                      ' called procedure see OP
End Sub

Sub Set_PrintRnag()
Dim LstRw As Long
Dim Rng As Range

LstRw = Sheet2.Cells(Rows.Count, "R").End(xlUp).Row
Set Rng = Sheet2.Range("R1:S" & LstRw)

With Sheet2.PageSetup
    .LeftHeader = "&C &B &20 Cohort List Report : " & Format(Date, "mm/dd/yyyy")
End With

Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & _
  "\CohortList " & " " & Format(Date, "mm-dd-yyyy") & ".pdf", _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Additional hint

To get some ideas how to use a datafield array, see e.g. SO answer to Loop with multiple ranges

That worked like a champ! I appreciate your help!! Many many thanks!! — Curtis, Nov 06 '18 at 17:14
I'm trying to add a column that gets copied when a match is found. I need column A and B copied and pasted to "T:U" (formerly "R:S" above). My current code pastes the line numbers (Column T) but nothing to the right (Column U) like it should be. My myrange3 gets pasted in column U where that ends. I need the cells in Column A and B copied and Pasted to "T:U" when a match is found. Then I'll print that ("T:U") out to a PDF. — Curtis, Apr 23 '19 at 04:01
This contains my additions. Set myrange3 = Sheet2.Range("B1", Sheet2.Range("B" & Rows.Count).End(xlUp)) Sheet2.Range("T:U") = "" ' <~~ clear result columns For Each cell In myrange2 ' presumably unique items If Not IsError(Application.Match(cell.Value, myrange1, 0)) Then Sheet2.Cells(i, 2).Offset(, 1).Resize(1, 1).Copy cell.Copy — Curtis, Apr 23 '19 at 04:05
I indented four spaces, but the code looks like text. Did I do something wrong? — Curtis, Apr 23 '19 at 04:12
Code in comments cannot be formatted by indentation. Referring to your comments above: without seeing the entire code and a complete description what you are doing where (correct sheet references?), it's hard to understand your actual issue - consider to pose a new Question with sample data. — T.M., Apr 23 '19 at 18:54

Number each Row when printing VBA

2 Answers2