Как подсчитать точные совпадения с учетом ведущих нулей в Excel VBA

У меня есть проект под названием генератор инвентарных номеров. Цифры предназначены для каждого элемента каждой группы запасов, которая включает 2 цифры для основных групп продуктов, 2 цифры для вспомогательных групп продуктов, 2 цифры для групп компонентов, и в сумме они образуют первые 6 цифр инвентарного номера. Следующие 3 цифры предназначены для номера отдельного элемента, который я пытаюсь сгенерировать после подсчета точных совпадений номеров данной группы акций в списке инвентарных номеров и путем добавления 1 к результату подсчета.

_ _ _ _ _ _ _ _ _

M G S G C G I E N

MG = Main Group

SG = Sub Group

CG = Component Group

SG = Stock Group (The First 6 Digits)

IEN = Individual Element Number

Проблема здесь в том, что каждый номер группы может содержать ведущие нули или каждая цифра может быть нулем. Чтобы создать уникальные номера, мне нужно подсчитать точные совпадения данной группы запасов (которая на самом деле является объединенной версией трех групп), выполнив поиск в списке инвентарных номеров и добавив новый инвентарный номер, который создается добавлением 1 к результату подсчета и прибавление результата, который состоит максимум из 3 цифр, в конец группы акций сгруппирует 6 цифр. После их создания я также добавляю их в список инвентарных номеров. Например, если данная группа запасов - «00 00 10» и если в списке инвентарных номеров есть инвентарные номера, такие как «00 00 00 010», то счетчик их тоже считает.

Примечание. Между числами нет пробелов (""). Я поместил их здесь, чтобы было легче понять.

Вот коды, которые я написал:

Private Sub EkleSEB_Click() 'Stok Ekle kısmında Stok Ekle butonu tıklanırsa

Dim StokSS As Variant 'Stok Ekle Sıra Say
Dim StokS As Variant 'Stok Say
Dim StokEAS As Integer 'Stok Eşleşen Adet Say
Dim StokG As Long 'Stok Grubu
Dim StokA As Long

If AnaGKSEC <> "" And AltGKSEC <> "" And BilGKSEC <> "" And BirSEC <> "" And StokASET <> "" Then

    StokSS = WorksheetFunction.CountA(Worksheets("ÜretimKodları").Range("A:A")) + 1

    StokG = (AltGKSEC.Value * 100) + BilGKSEC.Value

    For StokS = 2 To StokSS

        If Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) < 4 And StokG = 0 Then

            StokEAS = (Application.WorksheetFunction.CountIf(Worksheets("ÜretimKodları").Range("A2", ThisWorkbook.Sheets("ÜretimKodları").Cells(StokSS - 1, 1)), "<1000")) + 1

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 4 And StokG > 0 And StokG < 10 Then

            StokEAS = 0

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 1) = Left(StokG, 1) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS + 1), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 5 And StokG > 9 And StokG < 100 Then

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 2) = Left(StokG, 2) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 6 And StokG > 99 And StokG < 1000 Then

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 3) = Left(StokG, 3) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 7 And StokG > 999 And StokG < 10000 Then

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 4) = Left(StokG, 4) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 8 And StokG > 9999 And StokG < 100000 Then

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 5) = Left(StokG, 5) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        ElseIf Len(Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokS, 1).Value, 9)) = 9 And StokG > 99999 And StokG < 1000000 Then

            For StokA = 2 To StokSS - 1

                If Left(ThisWorkbook.Sheets("ÜretimKodları").Cells(StokA, 1).Value, 6) = Left(StokG, 6) Then

                StokEAS = StokEAS + 1

                End If

            Next StokA

            If StokEAS = 999 Then

                MsgBox "Seçilen grupta kod sınırına ulaşıldı!"

            Else 'Stok Kodu oluştur

                Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
                Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
                Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
                StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

            End If

        Else

            Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(((StokG * 1000) + StokEAS), "#########000000000")
            Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
            Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value
            StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

        End If

    Next StokS

Else

    MsgBox "Tüm alanları Doldurun!"

End If

End Sub

Как видите, я отсортировал каждый элемент в списке по месту первой ненулевой цифры, которая у них есть, но единственное, что я не мог сделать, это заставить функцию «Left ()» подсчитывать начальные нули, иначе мне понадобится новая функция для подсчета точных совпадений с учетом ведущих нулей.

Редактировать:

На самом деле я не знаю, в каком типе они хранятся, но я взял первый столбец инвентарных списков, в котором 9-значные инвентарные номера хранятся со специальным форматированием. Я постараюсь вкратце объяснить, как я пытаюсь создать новый инвентарный номер для нового элемента. Итак, сначала пользователь добавляет новые группы продуктов на вкладке пользовательской формы, а затем, связанный с группой продуктов, они добавляют новые группы субпродуктов на другую вкладку пользовательской формы. Группы компонентов добавляются также на другую вкладку пользовательской формы, но они не связаны с группами Product-SubProduct. Таким образом, в конце пользователь добавляет новый инвентарный номер на последней вкладке пользовательской формы, выбирая группу продуктов из поля со списком, а затем выбирая в зависимости от выбора группы продуктов из второго зависимого поля со списком SubProduct Group. После этого они выбирают группу компонентов из независимого поля со списком и дают имя новому элементу. Таким образом, группы продуктов и вспомогательных продуктов объединены и перечислены в поле со списком «Группа вспомогательных продуктов» с их описаниями рядом с их номерами. При добавлении 4 цифр из групп «Продукт-вспомогательный продукт» и 2 цифр из выпадающих списков «Группа компонентов» мы получаем в качестве входных данных номер нашей группы акций, состоящий из 6 цифр. Реальная задача заключалась в том, чтобы найти данный номер группы акций в инвентарном списке, который отформатирован так, чтобы отображать 9 цифр и иметь ведущие нули, а также подсчитывать точные совпадения. Добавляя 1 к результату подсчета, мы создаем наш новый элемент или инвентарный номер.

Изменить 2:

Проблемы, которые я решил с новой формулой:

Если первая строка пуста, функция match () аварийно завершает работу.
Если номер группы акций равен «0», то функция match () не работает.
Решена проблема с добавлением числа в список, который содержит меньше цифр, чем предыдущий, чем функция match () фокусируется на последнем добавленном числе или на числе, которое содержит меньше цифр.

4. Я также добавил функцию, которая останавливает добавление нового инвентарного номера в список (хотя в моем случае группа акций может иметь только 999 элементов), когда счетчик достигает «999».

И новый код VBA: (извините за турецкие комментарии, которые вы видите в кодах)

Private Sub EkleSEB_Click() 'Stok Ekle kısmında Stok Ekle butonu tıklanırsa

Dim StokK As Variant 'Stok Kodu
Dim StokKS As Variant 'Stok Kodu Say
Dim StokSS As Variant 'Stok Sıra Say
Dim StokG As Variant 'Stok Grubu
Dim StokGF As Variant 'Stok Grubu Formatlı
Dim StokGS As Variant 'Stok Grubu Say

If AnaGKSEC <> "" And AltGKSEC <> "" And BilGKSEC <> "" And BirSEC <> "" And StokASET <> "" Then

    StokSS = WorksheetFunction.CountA(Worksheets("ÜretimKodları").Range("A:A")) + 1

    StokG = ((AltGKSEC.Value * 100) + BilGKSEC.Value)

    StokGF = Format(StokG, "000000")

    StokKS = 0

    For StokGS = 2 To StokSS 'Stok Grubu Say

        If StokGF = Left(Worksheets("ÜretimKodları").Cells(StokGS, 1).Text, 6) Then

            StokKS = StokKS + 1

        End If

    Next StokGS

    If Not StokKS > 999 Then

        StokK = (StokG * 1000) + StokKS 'Stok Kodu Oluştur

        Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(StokK, "#########000000000")
        Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
        Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value

        StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

    Else

        MsgBox "Seçtiğiniz Grup Kodu İçin Üretilebilecek Stok Kodu Sınırına Ulaşıldı!"

    End If

Else

    MsgBox "Tüm alanları Doldurun!"

End If

End Sub

Hakan Tosun 19.03.2019 источник

comment

Как хранятся эти числа? Сохраняются ли они как текст / строки или как истинные числа с фиксированным 9-значным числовым форматом? • Второй вопрос: сортируются ли ваши данные по этому номеру? - Pᴇʜ 19.03.2019

comment

Извините за поздний ответ (я живу в Германии). Я думаю, что они хранятся в виде чисел, но когда я использую функцию Left (), они превращаются в строки, верно? Но я отредактировал вопрос, чтобы вы могли лучше понять процедуру. Подскажите, пожалуйста, как я могу увидеть тип переменных в VBA? Большое спасибо. - Hakan Tosun 19.03.2019

comment

Что ж, вы единственный, кто может сказать, хранятся ли числа на самом деле как текст / строки или как числовые значения. Я думаю не поможет нам помочь вам, нам нужно знать это наверняка, потому что это имеет огромное значение. • Также я не могу дать вам полное решение. Вопрос в широком смысле. Но я мог бы привести только пример того, как это будет работать с числовыми значениями (см. Мой ответ). - Pᴇʜ 19.03.2019

Ответы (2)

arrow_upward
0
arrow_downward

Если вы используете вещественные числа в качестве инвентарных номеров (а не текст / строки), вы можете сделать следующее:

Столбец A содержит истинные числа, отформатированные в числовом формате 00 00 00 000 для облегчения наглядности. Фактическое число, которое хранится в ячейках в столбце A, показано в столбце B.
Столбец F содержит формулы, показанные в столбце I. Результатом этих формул является истинное число, как показано в столбце G. Столбец F также просто отформатирован в числовом формате.

Пользователь должен ввести полный 6-значный номер группы в F3 (красный), и формулы дадут вам следующий свободный инвентарный номер этой группы в F9 (зеленый).

Pᴇʜ 19.03.2019

comment

В VBA вы можете использовать метод WorksheetFunction.Match и метод WorksheetFunction.Index как эквивалент формул. Что именно вы имели в виду под Я застрял, форматируя значения в числа? Не могли бы вы добавить снимок экрана с вашими данными в исходный вопрос? - Pᴇʜ; 19.03.2019

comment

Сожалеем о первом комментарии (потеряна связь с Интернетом). Спасибо за ваши усилия и быстрый ответ. Я был бы очень благодарен, если бы вы помогли мне решить эту проблему без добавления нового столбца, а только с изменением кода в VBA. Настоящая проблема с этим кодом заключалась в том, чтобы взять форматированные значения из ячеек и использовать их в функции, чтобы их можно было подсчитать, поскольку они сохраняют формат. Поэтому я не смог найти решение использовать форматированные значения в любом типе функции подсчета, поэтому я попытался подсчитать ведущие нули, чтобы создать новую формулу подсчета, но в конце ведущие нули победили меня :) - Hakan Tosun; 19.03.2019

comment

@HakanTosun Извините, я не могу исправить ваш код. Вопрос, который вы задаете, слишком общий. Приведенный выше пример, вероятно, лучший пример того, как вы бы это сделали. • Обратите внимание, что я не добавлял никаких новых столбцов. Столбец B просто иллюстрирует реальное значение, которое на самом деле находится в столбце A (которое отформатировано только числовым форматом 00 00 00 000). Столбец B в моем примере вообще не используется, это просто для иллюстрации. Столбцы A и B имеют одно и то же значение. - Pᴇʜ; 19.03.2019

comment

Кстати, я думал о решении, аналогичном вашему, но я не хотел менять визуальные эффекты, а только код, чтобы я мог, возможно, сохранить фактические числа в переменной, или я действительно не знаю, как хранить несколько значений, таких как диапазон значений переменной. Диапазон - это тип переменной? Если да, нужно ли связывать переменную с фактическим диапазоном в таблице Excel или значения хранятся в другом месте? - Hakan Tosun; 19.03.2019

comment

Я получил свой ответ, но не хотел, чтобы вы меняли мой код. Вы уже решили мою проблему. Еще раз спасибо за все. Ты быстрее моих храбрых :) - Hakan Tosun; 19.03.2019

comment

Если ваши ячейки содержат истинные числа, вам не нужно ничего менять в своих ячейках. Просто вставьте в код VBA то, что я проиллюстрировал в формулах, вот и все. - Pᴇʜ; 19.03.2019

comment

Еще один вопрос: мне нужно проверить, достигают ли последние 3 цифры или номер элемента группы акций 999, тогда мне нужно прекратить добавлять новые номера инвентаря, потому что тогда это повлияет на раздел группы акций. Вы тоже можете мне помочь с этой проблемой? - Hakan Tosun; 19.03.2019

comment

Ваш код был почти идеальным для меня. Я немного настроил его и подогнал под свой проект. Я собираюсь поделиться отредактированной версией этого в конце моего вопроса. Большое тебе спасибо. - Hakan Tosun; 19.03.2019

comment

Просто проверьте в моем примере, F9 - F4 < 1000, чтобы ограничить его до 999. - Pᴇʜ; 20.03.2019

comment

В коде есть ошибки. Если я добавляю число с меньшим количеством цифр после любого числа, которое содержит больше цифр, чем добавленное, функция match () фокусируется на числе, которое содержит меньше цифр, или на новом добавленном номере. Но я думаю, что благодаря вам мы приближаемся к решению. - Hakan Tosun; 20.03.2019

comment

@HakanTosun Можете ли вы привести пример набора данных в исходном вопросе? Я либо не понял, что вы имеете в виду, либо не могу это воспроизвести. Обратите внимание, что этот подход предполагает отсортированные данные в столбце A. В противном случае совпадение не удается. - Pᴇʜ; 20.03.2019

arrow_upward
0
arrow_downward

Спасибо, но я нашел решение, которое искал сам: D

P.S .: Коды, которыми я делюсь здесь, включают турецкие комментарии, которые я написал, и они настроены для моего проекта, но если у вас есть какие-либо вопросы, просто задавайте.

Вот коды для подсчета точных совпадений в указанном диапазоне цифр от начала чисел, даже если они имеют ведущие нули:

Private Sub EkleSEB_Click() 'Stok Ekle kısmında Stok Ekle butonu tıklanırsa

Dim StokK As Variant 'Stok Kodu
Dim StokKS As Variant 'Stok Kodu Say
Dim StokSS As Variant 'Stok Sıra Say
Dim StokG As Variant 'Stok Grubu
Dim StokGF As Variant 'Stok Grubu Formatlı
Dim StokGS As Variant 'Stok Grubu Say

If AnaGKSEC <> "" And AltGKSEC <> "" And BilGKSEC <> "" And BirSEC <> "" And StokASET <> "" Then

    StokSS = WorksheetFunction.CountA(Worksheets("ÜretimKodları").Range("A:A")) + 1

    StokG = ((AltGKSEC.Value * 100) + BilGKSEC.Value)

    StokGF = Format(StokG, "000000")

    StokKS = 0

    For StokGS = 2 To StokSS 'Stok Grubu Say

        If StokGF = Left(Worksheets("ÜretimKodları").Cells(StokGS, 1).Text, 6) Then

            StokKS = StokKS + 1

        End If

    Next StokGS

    StokK = (StokG * 1000) + StokKS 'Stok Kodu Oluştur

    Worksheets("ÜretimKodları").Cells(StokSS, 1) = Format(StokK, "#########000000000")
    Worksheets("ÜretimKodları").Cells(StokSS, 2) = StokASET.Value
    Worksheets("ÜretimKodları").Cells(StokSS, 3) = BirSEC.Value

    StokKSET.Value = Format(Worksheets("ÜretimKodları").Cells(StokSS, 1).Value, "#########000000000")

Else

    MsgBox "Tüm alanları Doldurun!"

End If

End Sub

Hakan Tosun 21.03.2019

Как подсчитать точные совпадения с учетом ведущих нулей в Excel VBA

Ответы (2)

Вопросы по теме