从if语句宏vba中排除某些单元格值 [英] Excluding certain cell values from if statement macro vba

问题描述

我有一个宏，该宏运行3000 ++行数据，并且我想创建一个排除列表，例如，当我的if语句去查看某个范围(如果它与某个名称集匹配)以使其跳过时，那一行，继续前进.

I have a macro that runs through 3000 ++ lines of data and I want to create an exclusion list, so for instance when my if statement goes to look at a range if it matches a certain set of names for it to skip that row and keep going.

示例为:

如果列A的值为"J史密斯"，以使其跳过该行并照原样继续，则该姓氏已更改为史密斯"，但它们可能会有所不同.

If Column A has a value of "J Smith" for it to skip that line and continue as it was, the surnames have been changed to 'Smith' but these may vary.

如果我从宏中删除代码，则围绕它的整个代码都可以正常工作，但没有任何问题，但是一旦添加它，我就可以给它处理的数据给我一点灵活性:

The entire code around it works if I remove the code from the macro it works perfectly without any problems but as soon as i add it to give me a bit flexibility with the data it dies:

 If Range("A" & r).Value = "C Hopcroft" Or "M Pyatt" Or "D Freeman" Or "D Jolley" Or "A Marsh" Or "M Heslop" Or "K Knowles" Or "J Robertson" Or "D Wood" Then
            r = r + 1
            Else

除了在A列中找到名称的时间时，它在类型不匹配"时崩溃，但该名称似乎完全匹配，好像我将鼠标悬停在"If Range("A"& r).Value上一样给我正确的值，如果我从数据中删除名称，请看宏将完美完成.

Other than when it finds when of the names within column A it crashes on a 'Type Mismatch' but the name seems to match perfectly as if I hover over 'If Range("A" & r).Value it gives me the correct value and if I remove the names from the data is look at the macro will complete perfectly.

有什么想法吗?

我的宏代码如下:

Sub unusedMacro()

Dim lr As Long, lr2 As Long, r As Long
    Set Sh1 = ThisWorkbook.Worksheets("Depot Dashboard")
    Set Sh2 = ThisWorkbook.Worksheets("Summary Data")
    Sh2.Select
    lr = Sh2.Cells(Rows.Count, "A").End(xlUp).Row
    x = 39
    For r = 2 To lr

        If Range("C" & r) = "TRUE" Then
        If Range("N" & r).Value = Worksheets("Depot Dashboard").Range("B17") Then
        If Range("A" & r).Value = "C Hopcroft" Or "M Pyatt" Or "D Freeman" Or "D Jolley" Or "A Marsh" Or "M Heslop" Or "K Knowles" Or "J Robertson" Or "D Wood" Then
        r = r + 1
        Else
            Sh2.Cells(r, 15).Copy
            Sh1.Cells(x, 7).PasteSpecial xlPasteValues  'Date
            Sh2.Cells(r, 1).Copy
            Sh1.Cells(x, 8).PasteSpecial xlPasteValues  'Name
            Sh2.Cells(r, 28).Copy
            Sh1.Cells(x, 9).PasteSpecial xlPasteValues  'Turn ID
            Sh2.Cells(r, 29).Copy
            Sh1.Cells(x, 10).PasteSpecial xlPasteValues  'Turn Desc
            x = x + 1
        End If
        End If
        End If
    Next r
    Sh1.Select
    Set Sh1 = Nothing
    Set Sh2 = Nothing
    Set x = Nothing


End Sub

推荐答案

IF 语句的 IF 行必须返回 Boolean 值[真假].就目前而言，如果中的第一个测试返回布尔值价值，但其他人则没有.要更正它，必须针对单元格中的值测试每个字符串(名称).

The IF line of an IF statement must return a Boolean value [True, False]. As it stands, the first test in If Range("A" & r).Value = "C Hopcroft" Or "M Pyatt" Or "D Freeman" Or "D Jolley" returns a Boolean value, but the others don't. To correct it, each string (name) must be tested against the value in the cell.

例如:

If Range("A" & r).Value = "C Hopcroft" Or Range("A" & r).value = "M Pyatt" Or Range("A" & r).value) = "D Freeman" Or Range("A" & r).value = "D Jolley"

这可以解决问题吗?

这篇关于从if语句宏vba中排除某些单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！