Excel VBA: Número de contagem de Delimitadores

Question

Eu tenho o seguinte código:

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

Este código basicamente usa um modelo de dados na Planilha 1 e copia esse modelo e implementa os dados da Planilha 2, quantas vezes definidos em "copyRows"

Neste momento, eu teria que ajustar o modelo na Folha 1 sempre que eu alterasse o número "copyRows". Por exemplo, se copyRows for 4, na Sheet 1, o template conterá 4 linhas de material predefinido, a primeira linha será necessária, as linhas a seguir serão baseadas no "|" delimitador de valores nos dados da Folha 2. Por exemplo, a coluna 2 da folha 2 tem dados como "Test1 | Test2 | Test3"

Estou tentando automatizar esse código um pouco mais, por isso não preciso alterar manualmente o copyRows e adicionar ou remover linhas adicionais na folha de modelo. Na folha de modelo, quero apenas ter duas linhas, a primeira é necessária, a segunda será baseada no número de "|" valores delimitadores.

Estou pensando em adicionar uma fórmula de contagem para o copyRows, por exemplo, =LEN(Sheets(2).B:B)-LEN(SUBSTITUTE(Sheets(2).B:B,"|","")) e, em seguida, adicionando 1. Como eu colocaria a fórmula em copyRows?

    

Answer 1

Esta função irá encontrar o número de ocorrências de um "|" caractere na coluna B da sua planilha.

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

Não tenho 100% de certeza de como são os dados de origem, mas acho que você poderia definir o skuRow primeiro e depois configurar o copyRows da seguinte forma:

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

    

Answer 3

Outras opções

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

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