Ошибка при попытке циклически перемещаться по ячейкам с автофильтрацией с помощью vba

Я использую приведенный ниже код для удаления недопустимых экземпляров текста, в данном случае операторов, начинающихся с двоеточий. Я знаю все шаги, которые мне нужно предпринять, но у меня возникли проблемы после Autofitering. Я пробовал перебирать видимые ячейки, используя

for x=1 to currentFilter.rows.count

а также

for each x in currentFilter.rows

Но независимо от того, как я пытался, я продолжаю получать какую-то ошибку при попытке избавиться от первого символа (двоеточие) с помощью (основной смысл):

Cell Value = Right(Cell Value, Len(Cell Value) - InStr(Cell Value, ",", vbTextCompare))

Мой полный код выглядит следующим образом:

Sub PRTCheck()
    'Column AN is Production Time & Column AP is Rush Time
    Dim endRange As Integer, ShipandRush As Range, CommaColons As Collection, cell, i

    endRange = ActiveSheet.Cells(Rows.count, "AN").End(xlUp).Row
    Set ShipandRush = Union(ActiveSheet.Range("AN2:AN" & endRange), ActiveSheet.Range("AP2:AP" & endRange))

    ShipandRush.NumberFormat = "@"
    Set CommaColons = FindAllMatches(ShipandRush, ",:")
    If Not CommaColons Is Nothing Then
        Dim times() As String
        For Each cell In CommaColons
            times = Split(cell.Value, ",")
            For i = LBound(times) To UBound(times)
                If InStr(times(i), ":") = 1 Then times(i) = ""
            Next
            cell.Value = Join(times, ",")
            Do While InStr(cell.Value, ",,") <> 0
                cell.Value = Replace(cell.Value, ",,", ",", vbTextCompare)
            Loop
            If InStr(cell.Value, ",") = 1 Then
                cell.Value = Right(cell.Value, Len(cell.Value) - 1)
            End If
            If InStr(Len(cell.Value), cell.Value, ",") = Len(cell.Value) Then
                cell.Value = Left(cell.Value, Len(cell.Value) - 1)
            End If
        Next cell
    End If

    Set ShipandRush = ActiveSheet.Range("AN1:AN" & endRange)
    Dim currentFilter As Range, r
    ShipandRush.AutoFilter Field:=1, Criteria1:=":*" 'Starts with colon
    Set currentFilter = ShipandRush.Offset(1).SpecialCells(xlCellTypeVisible)
    If currentFilter.Rows.count > 0 Then
        For r = 1 To currentFilter.Rows.count
        '-------Error occurs on the next line-------
            currentFilter.Cells(r).Value = Right(currentFilter.Cells(r).Value, Len(currentFilter.Cells(r).Value) - InStr(currentFilter.Cells(r).Value, ",", vbTextCompare))
        Next
    End If
    ActiveSheet.AutoFilterMode = False
    End Sub

'Custom find and replace that circumvents 255 character find limitation
Function FindAllMatches(rng As Range, txt As String) As Collection
    Dim rv As New Collection, f As Range, addr As String, txtSrch As String
    Dim IsLong As Boolean

    IsLong = Len(txt) > 250
    txtSrch = IIf(IsLong, Left(txt, 250), txt)

    Set f = rng.Find(what:=txtSrch, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
    Do While Not f Is Nothing
        If f.Address(False, False) = addr Then Exit Do
        If Len(addr) = 0 Then addr = f.Address(False, False)
    'check for the *full* value (case-insensitive)
        If InStr(1, f.Value, txt, vbTextCompare) > 0 Then rv.Add f
        Set f = rng.FindNext(After:=f)
    Loop
    Set FindAllMatches = rv
End Function

Мой вопрос:

Что я делаю не так? Как я могу перебирать каждое значение в видимых ячейках и успешно выполнять формулу, которую я отметил выше?


excel vba autofilter
person CaffeinatedMike    schedule 31.03.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

На самом деле вы имеете дело только с одним столбцом, но я постараюсь придерживаться вашего метода перебора строк вместо ячеек, которые в данном случае по существу являются одним и тем же (хотя Range.Rows — это не то же самое, что Range.Cells).

Несмежные диапазоны необходимо циклически перебирать с помощью их свойства Range.Areas. сначала, а затем свойство Range.Rows в каждой области.

dim a as long, r as long
with currentFilter
    If .Rows.count > 0 Then
        for a = 1 to .Areas.count
            For r = 1 To .Areas(a).Rows.count
                .Areas(a).Rows(r).Cells(1).Value = _
                   Right(.Areas(a).Rows(r).Cells(1).Value, _
                         Len(.Areas(a).Rows(r).Cells(1).Value) - _
                         InStr(1, .Areas(a).Rows(r).Cells(1).Value, ","))
            Next r
        Next a
    End If
end with

Может быть проще просто использовать For Each... Next.

dim cfr as range
with currentFilter
    for each cfr in .Cells
        cfr = Right(cfr.Value, Len(cfr.Value) - InStr(1, cfr.Value, ","))
    Next cfr
end with
person Community    schedule 31.03.2016
comment
Ааа, моя проблема заключалась в использовании цикла for each со строками вместо ячеек, как вы показываете цикл for each во втором фрагменте кода. На самом деле я делаю это для двух столбцов (AP и AN), но я показал только первый экземпляр этого кода. Я не был уверен, что автофильтр будет работать для несмежных областей (мне казалось, что я читал это в документации msdn). Я решил, что просто буду подражать формуле, с которой у меня были проблемы. Есть ли у вас какие-либо предложения о том, как охватить оба столбца одновременно, вместо того, чтобы делать их по отдельности, или мне следует придерживаться дублирования этого кода? - person CaffeinatedMike; 31.03.2016
comment
ShipandRush — это только столбец AN:AN, и именно его вы используете для установки currentFilter (например, ShipandRush.Offset(1).SpecialCells(xlCellTypeVisible) без изменения размера, который я мог бы добавить). Вот почему мне нравится работать с .CurrentRegion; это облегчает отслеживание экстентов. Но да, вы можете перебирать строки только в пределах непрерывной области. Вам нужно перебрать каждую область, а затем строки в каждой области. Вы можете попробовать перебрать .Cells в AN:AN и просто cfr.OffSet(0, 2), чтобы добраться до столбца AP. Смещение может выходить за пределы своего собственного ограниченного столбца, если вы не пытаетесь выйти за пределы ws. - person ; 31.03.2016
comment
Почему я должен использовать изменение размера в этом случае (и как)? Из того, что вы описали, я полагаю, что у меня не будет другого выбора, кроме как выполнять эти два действия по отдельности, поскольку я могу автофильтровать несмежные столбцы. Причина, по которой я должен сделать это отдельно, заключается в том, что мне придется сбросить фильтр для фильтрации всех ячеек, начинающихся с двоеточия в столбце AP. - person CaffeinatedMike; 31.03.2016
comment
Может быть, вы могли бы зациклить его и объединить два результата. Не знаю. Вы говорите о чем-то, что вы не обсуждали в своем вопросе. - person ; 31.03.2016