Перенос функции SumProduct в VBA

Я пытаюсь написать код, содержащий следующую строку:

WorksheetFunction.SumProduct((Columns(3) = ActiveCell.Value) + 0)

Код всегда возвращает ошибку несоответствия типа!

Я использую эту строку кода, чтобы избежать использования функции Countif со строкой длиной более 255.

заранее спасибо

Это исходный код:

Dim MyColumn As Long, r As Long, lngLastRow As Long
MyColumn = ActiveCell.Column
With Sheets("Project Breakdown")
lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
    For r = lngLastRow To 1 Step -1
        If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
             If WorksheetFunction.CountIf(.Columns(MyColumn), .Cells(r, MyColumn).Value) > 1 Then
            .Cells(r, MyColumn).Delete Shift:=xlUp
             End If
        End If
    Next r
End With

countif возвращает ошибку, поскольку длина строки превышает 255, поэтому я попытался вместо этого использовать функцию sumproduct, но не смог заставить ее работать.


excel countif vba
person user7390329    schedule 08.01.2017    source источник
comment
Проверяли ли вы другие функции подсчета (например, COUNTA)?   -  person FDavidov    schedule 08.01.2017
comment
вы хотите умножить значения в столбце C на что? какой столбец или диапазон?   -  person Shai Rado    schedule 08.01.2017
comment
Пожалуйста, смотрите обновление. Благодарность   -  person user7390329    schedule 08.01.2017
comment
Можете ли вы объяснить, чего вы пытаетесь достичь с помощью своего кода? возможно, вам не нужна ни одна из функций   -  person Shai Rado    schedule 08.01.2017
comment
@ShaiRado хорошо. Я пытаюсь сделать код для удаления дубликатов в столбце C, но с условиями, основанными на столбце A, что код удаляет дубликаты в C, если столбец A не содержит ни слов DIV, ни SEC. Это работает очень хорошо, но когда длина строки превышает 255, возникает ошибка :(   -  person user7390329    schedule 08.01.2017
comment
@ user7390329 вы можете использовать AutoFilter, чтобы скрыть все строки с DIV или SEC в столбце A, а затем просто использовать RemoveDuplicates из столбца C (используйте SpecialCells(xlCellTypeVisible), чтобы определить новый диапазон отфильтрованных строк)   -  person Shai Rado    schedule 08.01.2017
comment
@ShaiRado Я пытался сделать это раньше, но решил, что удаление дубликатов также удаляет неотфильтрованные строки. Не могли бы вы уточнить информацию о (xlCellTypeVisible). VBA был для меня иероглифами пару часов назад.   -  person user7390329    schedule 08.01.2017

Ответы (1)


arrow_upward
0
arrow_downward

Проблема в том, что вы сравниваете диапазон с одним значением. Это работает в формуле excel, но не в vba.

Одним из обходных путей может быть использование дополнительного столбца на вашем листе, например.

=if($C1='some value',1,0)

а затем возьмите сумму этого столбца.

В качестве альтернативы вы можете рассчитать sumproduct в Excel. Запишите значение активной ячейки в предопределенной ячейке (например, A1), и ваш суммарный продукт будет использовать эту формулу:

=sumproduct((C:C=$A$1)+0)

Обновление: предполагается, что A1 является свободной ячейкой, а A2 содержит следующую формулу:

=SumProduct((C:C=$A$1)+0)

Изменив значение A1 на вашу текущую ячейку, эта формула содержит ваш результат.

Dim MyColumn As Long, r As Long, lngLastRow As Long
MyColumn = ActiveCell.Column
With Sheets("Project Breakdown")
lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
    For r = lngLastRow To 1 Step -1
        If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
             .cells(1,1).value = .cells(r, MyColumn).value
             ' Might be necessary to call .Calculate here to assure that A2 contains its new value
             If .cells(1,2) > 1 Then
            .Cells(r, MyColumn).Delete Shift:=xlUp
             End If
        End If
    Next r
End With
person jBuchholz    schedule 08.01.2017
comment
Мне очень жаль, но я до сих пор не могу понять, как информация, которую вы сказали, поможет мне заставить линейный код работать так, как хотелось бы. Пожалуйста, просмотрите обновление в исходном сообщении, так как я не могу исправить активную ячейку. Он делает изменения в цикле for next! - person user7390329; 08.01.2017
comment
Ну, это работает, но для завершения требуется значительное количество времени :( .. есть ли другой способ или, может быть, правильный синтаксис для правильной работы SumProduct - person user7390329; 08.01.2017
comment
Если вам нужно только знать, есть ли вхождение, вы можете использовать метод Range.find. если не .columns(3).find(ActiveCell.value) ничего... - person jBuchholz; 08.01.2017