How do I show the computations after S N 1048576 in a separate sheet

0 votes

I'm using the following VBA script I discovered online, however, the permutations cease at S/N 1048576. I have 5 columns worth of data: 3 rows, 40 rows, 40 rows, 40 rows, and 6 rows, respectively.

Sub ListAllCombinations()

    Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range
    Dim xRg As Range
    Dim xStr As String
    Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer
    Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String

    Set xDRg1 = Range("A2:A4") 'First column data
    Set xDRg2 = Range("B2:B41") 'Second column data
    Set xDRg3 = Range("C2:C41") 'Third column data
    Set xDRg4 = Range("D2:D41") 'Fourth column data
    Set xDRg5 = Range("E2:E7") 'Fifth column data

    xStr = "-" 'Separator Set
    xRg = Range("F2") 'Output cell

    For xFN1 = 1 To xDRg1.Count
        xSV1 = xDRg1.Item(xFN1).Text
        For xFN2 = 1 To xDRg2.Count
            xSV2 = xDRg2.Item(xFN2).Text
            For xFN3 = 1 To xDRg3.Count
                xSV3 = xDRg3.Item(xFN3).Text
                For xFN4 = 1 To xDRg4.Count
                    xSV4 = xDRg4.Item(xFN4).Text
                    For xFN5 = 1 To xDRg5.Count
                    xSV5 = xDRg5.Item(xFN5).Text
                    xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
                    Set xRg = xRg.Offset(1, 0)
                    Next
                Next
            Next
        Next
    Next
    End
Sub

I would like the excel sheet to generate a new sheet where S/N 1048577 and so on are generated.

Jan 31, 2023 in Others by Kithuzzz
• 38,000 points
382 views

1 answer to this question.

0 votes

Add:

If xRg.Row = xRg.Parent.Rows.Count Then
    Set xRg = Worksheets.Add.Range("F1")
End If

Before the line where you:

Set xRg = xRg.Offset(1, 0)

It should create a new sheet when it hits the bottom and move xRg to the top of that sheet before continuing.

answered Jan 31, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,600 points
1,620 views
0 votes
1 answer

How do I run a VBA Sub routine continuously when working in a Workbook and not only when the Workbook is opened?

on Thisworkbook, put: Private Sub Workbook_Open() Call checkPW(True) End Sub Then ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,600 points
622 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,600 points
1,052 views
0 votes
1 answer

How do I copy a specific range of cells after I use AutoFilter in VBA?

Set the range of filtered data Set Rng ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,600 points
890 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,245 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,691 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
951 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,082 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,600 points
1,949 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,600 points
1,493 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP