Forum Discussion

UKHG-AJ's avatar
UKHG-AJ
Copper Contributor
May 31, 2023

Trying to print all options from a VLOOKUP

I have created a new spreadsheet that contains all the details for our supplier contracts. All the Data is on Tab 1, i have then created a contract on Tab 2 where you type select the contract number...
  • JKPieterse's avatar
    May 31, 2023

    UKHG-AJ This code should do the trick. Adjust cell addresses and worksheet names in the code before running this on a copy of your file.

     

    Sub PrintAllContracts()
        Dim contractNmbr As String
        Dim contracts As Collection
        Dim contractCell As Range
        Dim contract As Variant
        Set contracts = New Collection
        For Each contractCell In Worksheets("Sheet1").Range("A2:A10")
            contractNmbr = Trim(contractCell.Value2)
            If Len(contractNmbr) > 0 Then
                On Error Resume Next
                'Add only unique contract numbers
                contracts.Add contractNmbr, contractNmbr
            End If
        Next
    
        If contracts.Count > 0 Then
            For Each contract In contracts
                With Worksheets("Sheet2")
                    .Range("A15").Value = contract
                    .PrintOut
                End With
            Next
        End If
    End Sub
    

     

Resources