Excel VBA: количество разделителей

Question

У меня есть следующий код:

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

Этот код в основном использует шаблон данных на листе 1 и копирует этот шаблон и реализует данные из листа 2, сколько раз задано в "copyRows"

Прямо сейчас мне придется корректировать шаблон на листе 1 каждый раз, когда я меняю номер "copyRows". Например, если copyRows равен 4, на листе 1 шаблон будет содержать 4 строки предопределенного содержимого, первая строка обязательна, следующие строки основаны на «|» разделитель значений в данных листа 2. Например, столбец листа 2 содержит данные типа «Test1 | Test2 | Test3»

Я пытаюсь автоматизировать этот код немного дальше, поэтому мне не нужно вручную изменять copyRows и добавлять или удалять дополнительные строки в шаблоне листа. В шаблонном листе я хочу просто иметь две строки, первая обязательна, вторая будет основана на числе «|» значения разделителя.

Я думаю о добавлении формулы подсчета для copyRows, например, =LEN(Sheets(2).B:B)-LEN(SUBSTITUTE(Sheets(2).B:B,"|","")) и затем добавление 1. Как бы я добавил в формулу для copyRows?

Answer 1

Эта функция найдет количество вхождений "|" символ в столбце B вашего рабочего листа.

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

Answer 2

Я не уверен на 100%, как выглядят исходные данные, но я думаю, что сначала вы можете определить skuRow, а затем настроить copyRows следующим образом:

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

Answer 3

Другие опции

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

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