循环通过公式 Excel VBA [英] Loop through formula Excel VBA

查看:25
本文介绍了循环通过公式 Excel VBA的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如果没有减号,我需要检查现有公式并删除整行.有没有办法逐个字符地遍历公式,或者有另一种方法来识别公式中是否有-"?

I need to go through the existing formula and delete entire row if there is no minus sign in it. Is there a way to go through the formula char by char or there is another way to identify if there is a "-" in the formula?

Sub clearvalidation()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    Set vali = ws.Cells.Find("Validation", LookIn:=xlValues, LookAt:=xlPart)
    If Not vali Is Nothing Then
        fr = vali.Row + 2
        lr = ws.Cells(fr + 1, 6).End(xlDown).Row
        For exclrow = fr To lr
            For Each char In ws.Cells(exclrow, 7)
                If char = "-" Then
                    check = "ok"
                    Exit For
                End If
            Next char
            If check <> ok Then check = "delete"
            Select Case check
                Case Is = "ok"
                    Stop
                Case Is = "delete"
                    Stop
            End Select
        Next exclrow
    End If
    vali = Empty
Next ws

End Sub

推荐答案

你应该使用 Instr 如果你正在寻找的子字符串没有找到或者 index 如果找到该子字符串.

You should use Instr that will return 0 if the substring your are looking for isn't found or the index of that substring if it is found.

当你循环删除行时,你应该从下到上使用 Step -1 以避免丢失一些行(如果第 i 行被删除,第 i+1 行将变为新的第 i 行,你会在下一行之后错过它.

And when you loop on rows to delete, you should go from bottom to top using Step -1 to avoid missing some rows (if the row i is deleted, the row i+1 will become the new row i and you'll miss it after the next).

Sub clearvalidation()
Dim ws As Worksheet, _
    FirstAddress As String, _
    cF As Range


For Each ws In ActiveWorkbook.Sheets
    ws.Range("A1").Activate
    With ws.Cells
        Set vali = .Find("Validation", LookIn:=xlValues, LookAt:=xlPart)
        If Not vali Is Nothing Then
            FirstAddress = vali.Address
            'If there is a result, keep looking with FindNext method
            Do
                fr = vali.Row + 2
                lr = ws.Cells(fr + 1, 6).End(xlDown).Row
                For exclrow = lr To fr Step -1
                    If InStr(1, ws.Cells(exclrow, 7).Formula, "-") Then
                        ws.Cells(exclrow, 7).EntireRow.Delete shift:=xlUp
                    Else
                    End If
                Next exclrow
                Set vali = .FindNext(vali)
            'Look until you find again the first result
            Loop While Not vali Is Nothing And vali.Address <> FirstAddress
        End If
        vali = Empty
    End With
Next ws


End Sub

这篇关于循环通过公式 Excel VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆