excel - 检测单元格中超过 5 个字符的宏(邮政编码)

Question

我创建了一个基于列名检索范围的函数。这是我的代码:

Sub sep_Filter()

    Dim zip_rng As String

    With Sheet2
        zip_rng = getColRangeFunction("postalcode")
          If Len(Range(zip_rng)) > 5 Then
            Range(zip_rng).Interior.Color = RGB(255, 0, 0)
            Range(zip_rng).Select
          Else
            Range(zip_rng).Interior.Color = xlNone
          End If
    End With
End Sub

Sheet2 输入列 D
Sheet2 输出列 D
Sheet3 输出列 D


088762598

088762598

06610-5000

06610-5000

330161898

330161898

970152880

970152880

112202570

112202570

127420800

127420800

062262040

062262040

07631
07631

10029
10029

11803
11803

99336
99336

Answer 1

编辑 我误解了你在问什么，我更新了我的答案以与你的问题相关联。
这是一种基本方法，可以满足您的要求。它跳过第一行。

Sub onlyfirst5()

Const pRange As String = "D1"



Dim ws As Worksheet
Set ws = ActiveSheet

Dim crng As Range, cValues()

Set crng = Intersect(ws.UsedRange.Offset(1, 0), ws.UsedRange, ws.Range("D:D"))

cValues = crng.Value

Dim i As Long, j As Long

For i = LBound(cValues) To UBound(cValues)
    For j = LBound(cValues, 2) To UBound(cValues, 2)
    
    cValues(i, j) = Left(cValues(i, j), 5)
    Next j
Next i

'for same sheet different column
ws.Range("F2").Resize(UBound(cValues), UBound(cValues, 2)) = Application.Transpose(cValues)

'different sheet
Sheets("Sheet2").Range("F2").Resize(UBound(cValues), UBound(cValues, 2)) = Application.Transpose(cValues)

'different file
Workbooks("Zip Code Question.xlsb").Sheets("Sheet3").Range("F2").Resize(UBound(cValues), UBound(cValues, 2)) = Application.Transpose(cValues)

End Sub