1

I have the following code:

Sub CopyTest()

    Dim skuRow As Integer
    Dim curSku As String
    Dim numSkus As Integer
    Dim impType As String
    Dim copyRows As Integer
    Dim supAcc As String
    Dim arr_TotalList As Variant
    Dim locs As String
    Dim colorMax As String

    With ActiveSheet
        copyRows = 3
        impType = "-LE"
        supAcc = ""
        numSkus = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row
        skuRow = 1

        Rows("1:" & copyRows).Copy

        For i = copyRows + 1 To (copyRows * numSkus) + 1 Step copyRows
            arr_TotalList = Split(CurVal, "|")
            colorMax = Sheets(2).Range("C" & skuRow).Value
            If colorMax = "" Then
                colorMax = "4"
            End If

            curSku = Sheets(2).Range("A" & skuRow).Value
            locs = Sheets(2).Range("B" & skuRow).Value
            arr_TotalList = Split(locs, "|")
            Range("A" & i).Select
            ActiveSheet.Paste
            For n = 0 To copyRows - 1 Step 1
                If n = 0 Then
                    Range("B" & i + n) = supAcc & curSku & impType
                    Range("E" & i + n) = colorMax
                Else
                    Range("G" & i + n) = arr_TotalList(n - 1)
                    Range("B" & i + n) = supAcc & curSku & impType
                End If
            Next n

            skuRow = skuRow + 1

        Next i            
    End With

End Sub

This code basically uses a data template in Sheet 1 and copies that template and implements data from Sheet 2 how ever many times set in "copyRows"

Right now, I would have to adjust the template in Sheet 1 each time I change "copyRows" number. For example if copyRows is 4, in Sheet 1, the template would contain 4 rows of predefined stuff, the first row is required, the following rows is based on the "|" delimiter of values in Sheet 2's data. For example, Sheet 2 column B has data like, "Test1|Test2|Test3"

I am trying to automate this code a little further so I do not have to manually change copyRows and add or remove additional rows in the template sheet. In the template sheet, I want to just have two rows, the first is required, the second will be based on the number of "|" delimiter values.

I am thinking of adding a count formula for the copyRows, for example, =LEN(Sheets(2).B:B)-LEN(SUBSTITUTE(Sheets(2).B:B,"|","")) and then adding 1. How would I put in the formula in for copyRows?

2
  • ubound(arr_TotalList) should be the upper bound of the index of arr_totalList after you perform the Split on "|". If I remember correctly, Ubound would normally report the index count which is normally 1 greater than the index (so x(Ubound(x)-1) would be the highest index), but on a Variant it returns the highest index instead (so x(ubound(x) is highest). [Check this]
    – Yorik
    Aug 28, 2015 at 21:10
  • Agree with @Yorik, if I understand everything. In the case where you don't yet have arr_TotalList defined yet when you're setting copyRows up, you could set copyRows = 1 + UBound(Split(Sheets(2).Range("B" & skuRow).Value)) though.
    – panhandel
    Aug 28, 2015 at 21:14

3 Answers 3

0

This function will find the number of occurrences of a "|" character in column B of your worksheet.

Function FindDelimeters()

Dim lastrow As Long
Dim DelimCount As Long
lastrow = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row

DelimCount = Sheets(2).Evaluate("=SUM(LEN(B1:B" & lastrow & ")-LEN(SUBSTITUTE(B1:B" & lastrow & ",""|"","""")))")
FindDelimeters = DelimCount

End Function
0

I'm not 100% sure what the source data looks like, but I think you could define skuRow first, and then set up copyRows as follows:

copyRows = 1 + UBound(Split(Sheets(2).Range("B" & skuRow).Value, "|"))
0

Other options

=COUNTIF(B:B/"*|*")

Application.CountIf(Sheets(2).Columns(2), "*|*")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .