VBA 检查范围内单元格值的长度

VBA check length of cell value in a range

提问by anve

I have codes that populate cells in column A (A7:A446) of a main sheet. These values are listed in a different sheet (Sheet3) and the user double-clicks on a code they want to populate the active cell back on the main sheet. These codes have descriptions (Column E, E7:E446) that go with them and the codes themselves are supposed to be 21 characters long. Currently, the description column (Column E) has a IF(ISNA(VLOOKUP...)) formula to pull the account descriptions associated with the account code being double-clicked in Sheet3. I want to write in VBA, a sub that checks if the codes in column A are 21 characters long. If the account codes are not 21 characters long, I want the description, and only the description, to be "N/A" to indicate that the code is invalid. In addition, if the code in column A does not equal any of the account codes listed in Sheet3, I would like the same action, the description in column E to be "N/A, and the only the description.

我有代码填充主工作表的 A 列 (A7:A446) 中的单元格。这些值列在不同的工作表 (Sheet3) 中，用户双击他们想要在主工作表上填充活动单元格的代码。这些代码具有随附的描述（E 列，E7：E446），并且代码本身应该是 21 个字符长。目前，描述列（E 列）有一个 IF(ISNA(VLOOKUP...)) 公式，用于提取与在 Sheet3 中双击的帐户代码相关联的帐户描述。我想用 VBA 编写，这是一个检查 A 列中的代码是否为 21 个字符长的子程序。如果帐户代码的长度不是 21 个字符，我希望描述（并且只有描述）为“N/A”以指示代码无效。此外，

I'm aware I can probably do this with a formula or function inside the description column itself but in an attempt to better familiarize myself with VBA, I'd like to do it this way. I've tried to write a sub to do this but it has not been working. I think I have set some of the variable to incorrect datatypes but I'm not entirely sure. Any help/suggestions would be greatly appreciated.

我知道我可能可以使用描述列本身内的公式或函数来做到这一点，但为了更好地熟悉 VBA，我想这样做。我试图写一个子来做到这一点，但它一直没有工作。我想我已经将一些变量设置为不正确的数据类型，但我不完全确定。任何帮助/建议将不胜感激。

  Sub acctCodeVarification()

acctCode = Sheet2.Range("C7:C446").Value
acctDesc = Sheet2.Range("E7:E446").Text

For Each c In Range("C7:C446").Cells
    If Len(c) <> 21 Then
    c.acctDesc = "N/A"
    If c <> Sheet3.Range("A1:A20681").Value Then
    c.acctDesc = "N/A"

    End If
    End If
Next c

End Sub

回答by tigeravatar

Your description and your provided code don't match each other which raises some questions, but here are the assumptions I've made based on your provided code:

您的描述和您提供的代码彼此不匹配，这引发了一些问题，但以下是我根据您提供的代码做出的假设：

• Your "Main" sheet is Sheet2
• Your "Verify" sheet is Sheet3
• Account codes that are being verified are in the Main sheet, column C, starting at cell C7
• Descriptions (which are not checked or verified at all) are in the Main sheet, column E, starting at cell E7
• The list of actual account codes to verify against are in the Verify sheet, column A
• If the account code being checked is NOT exactly 21 characters, set that cell (column C in Main sheet) and its description to "N/A"
• If the account code IS 21 characters, but is NOT found in the verify list, set only the description to "N/A"

• 您的“主”表是 Sheet2
• 您的“验证”表是 Sheet3
• 正在验证的帐户代码位于主表中的 C 列中，从单元格 C7 开始
• 描述（根本没有检查或验证）位于主表中的 E 列，从单元格 E7 开始
• 要验证的实际帐户代码列表在验证表的 A 列中
• 如果正在检查的帐户代码不是正好 21 个字符，则将该单元格（主表中的 C 列）及其描述设置为“不适用”
• 如果帐户代码为 21 个字符，但未在验证列表中找到，则仅将描述设置为“N/A”

Following that logic, this code should work for you:

按照该逻辑，此代码应该适合您：

Sub tgr()

    Dim wb As Workbook
    Dim wsMain As Worksheet
    Dim wsVerify As Worksheet
    Dim rAcctCodes As Range
    Dim rAcctCell As Range

    Set wb = ActiveWorkbook
    Set wsMain = wb.Sheets("Sheet2")    'Change to actual name of worksheet
    Set wsVerify = wb.Sheets("Sheet3")  'Change to actual name of worksheet
    Set rAcctCodes = wsMain.Range("C7", wsMain.Cells(wsMain.Rows.Count, "C").End(xlUp))

    For Each rAcctCell In rAcctCodes.Cells
        If Len(rAcctCell.Value) <> 21 Then rAcctCell.Value = "N/A"
        If WorksheetFunction.CountIf(wsVerify.Columns("A"), rAcctCell.Value) = 0 Then wsMain.Cells(rAcctCell.Row, "E").Value = "N/A"
    Next rAcctCell

End Sub

回答by Krystian Koz?owski

Try this:

尝试这个：

Dim acctCode As Range
Set acctCode = Worksheets("Sheet2").Range("C7:C446")

For Each c In Range("C7:C446").Cells
If Len(c.Value) <> 21 Then
    Worksheets("Sheet2").Range("E" & c.Row) = "N/A"
Else
    If Worksheets("Sheet3").Range("A1:A20681").Find(c.Value,LookIn:=xlValues).Value Is Nothing Then
        Worksheets("Sheet2").Range("E" & c.Row) = "N/A"
    Else
        Worksheets("Sheet2").Range("E" & c.Row) = c.Value
    End If
End If
Next c

回答by Brian M Stafford

Since the following variables are declared as string, they do not need a "Set" statement. So instead of:

由于以下变量被声明为字符串，因此它们不需要“Set”语句。所以而不是：

Set acctCode = Sheet2.Range("C7:C446").Value
Set acctDesc = Sheet2.Range("E7:E446").Text

Do this:

做这个：

acctCode = Sheet2.Range("C7:C446").Value
acctDesc = Sheet2.Range("E7:E446").Text