时间戳

首页 > 解决方案 > 运行时错误“13”：类型不匹配邮局更新

excel - 运行时错误“13”：类型不匹配邮局更新

问题描述

我之前创建了代码来清理电子表格。它工作得很好，但似乎 O365 更新并且代码坏了。

我仍在从 COVID 中恢复，我很难读到这篇文章，因为我的视力受到了永久性的影响。谁能帮我理解它为什么会坏以及我哪里出错了？我有

我已经一年多没有更新这段代码了，这是我最后一次写 VBS 脚本，所以我现在有点迷茫。

代码在If rngCell.Value > "" Then处中断并且是 * 422行

目前正在尝试查找是否有办法在崩溃时提供更详细的错误，以便将来更轻松地进行故障排除。

Dim i As Long


For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
    If Cells(i, "J") <> "Yes" Then Rows(i).Delete
Next i

For i = Cells(Rows.Count, "K").End(xlUp).Row To 2 Step -1
    If Cells(i, "K") <> "Yes" Then Rows(i).Delete
Next i

For i = Cells(Rows.Count, "O").End(xlUp).Row To 2 Step -1
    If Cells(i, "O") <> "Headquarters" And Cells(i, "O") <> "4205" And Cells(i, "O") <> "2151" And Cells(i, "O") <> "3446" And Cells(i, "O") <> "205" And Cells(i, "O") <> "233" And Cells(i, "O") <> "2182" Then Rows(i).Delete
Next i

Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns(7).EntireColumn.Delete

Range("C:F" & LastRow).Replace "", "999", xlWhole

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(i, "C").Value2 > 10 And Cells(i, "D").Value2 > 10 And Cells(i, "E").Value2 > 10 And Cells(i, "F").Value2 > 10 Then
        Rows(i).Delete
    End If
Next i



ActiveSheet.Name = "Sheet1"

Range("G1").Value = "Queue Name"
Range("G2").Value = "=VLOOKUP(B2,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G3").Value = "=VLOOKUP(B3,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G4").Value = "=VLOOKUP(B4,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G5").Value = "=VLOOKUP(B5,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G6").Value = "=VLOOKUP(B6,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G7").Value = "=VLOOKUP(B7,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G8").Value = "=VLOOKUP(B8,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G9").Value = "=VLOOKUP(B9,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G10").Value = "=VLOOKUP(B10,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G11").Value = "=VLOOKUP(B11,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G12").Value = "=VLOOKUP(B12,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G13").Value = "=VLOOKUP(B13,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G14").Value = "=VLOOKUP(B14,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G15").Value = "=VLOOKUP(B15,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G16").Value = "=VLOOKUP(B16,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G17").Value = "=VLOOKUP(B17,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G18").Value = "=VLOOKUP(B18,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G19").Value = "=VLOOKUP(B19,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G20").Value = "=VLOOKUP(B20,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G21").Value = "=VLOOKUP(B21,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G22").Value = "=VLOOKUP(B22,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G23").Value = "=VLOOKUP(B23,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G24").Value = "=VLOOKUP(B24,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G25").Value = "=VLOOKUP(B25,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G26").Value = "=VLOOKUP(B26,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G27").Value = "=VLOOKUP(B27,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G28").Value = "=VLOOKUP(B28,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G29").Value = "=VLOOKUP(B29,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"
Range("G30").Value = "=VLOOKUP(B30,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,7,0)"


Range("H1").Value = "Location"
Range("H2").Value = "=VLOOKUP(B2,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H3").Value = "=VLOOKUP(B3,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H4").Value = "=VLOOKUP(B4,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H5").Value = "=VLOOKUP(B5,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H6").Value = "=VLOOKUP(B6,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H7").Value = "=VLOOKUP(B7,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H8").Value = "=VLOOKUP(B8,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H9").Value = "=VLOOKUP(B9,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H10").Value = "=VLOOKUP(B10,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H11").Value = "=VLOOKUP(B11,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H12").Value = "=VLOOKUP(B12,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H13").Value = "=VLOOKUP(B13,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H14").Value = "=VLOOKUP(B14,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H15").Value = "=VLOOKUP(B15,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H16").Value = "=VLOOKUP(B16,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H17").Value = "=VLOOKUP(B17,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H18").Value = "=VLOOKUP(B18,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H19").Value = "=VLOOKUP(B19,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H20").Value = "=VLOOKUP(B20,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H21").Value = "=VLOOKUP(B21,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H22").Value = "=VLOOKUP(B22,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H23").Value = "=VLOOKUP(B23,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H24").Value = "=VLOOKUP(B24,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H25").Value = "=VLOOKUP(B25,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H26").Value = "=VLOOKUP(B26,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H27").Value = "=VLOOKUP(B27,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H28").Value = "=VLOOKUP(B28,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H29").Value = "=VLOOKUP(B29,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"
Range("H30").Value = "=VLOOKUP(B30,'\\server\server\server\server\[placement.xlsx]Printers'!$A$1:$N$230,5,0)"

Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True

Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("H2:H130" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:O130")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
   End With


    Dim FindString As String
    Dim Rng As Range

        FindString = "TYS"

        With Sheets("Sheet1").Range("H:H")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1, -1).Activate
                ActiveCell.Value = "TYSONS (9TH FLOOR)"
                Range(Cells(Selection.Row, 1), Cells(Selection.Row, 7)).Select
                With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
       Selection.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "Model"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Serial Number"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Black"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Cyan"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Magenta"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Yellow"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Queue Name"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
            Else
            End If
        End With


        FindString = "SPF"

        With Sheets("Sheet1").Range("H:H")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
           If Not Rng Is Nothing Then
                Application.Goto Rng, True
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1, -1).Activate
                ActiveCell.Value = "SPF"
                Range(Cells(Selection.Row, 1), Cells(Selection.Row, 7)).Select
                With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "Model"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Serial Number"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Black"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Cyan"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Magenta"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Yellow"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Queue Name"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
            Else
            End If
        End With

    FindString = "WFR"

        With Sheets("Sheet1").Range("H:H")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1, -1).Activate
                ActiveCell.Value = "WHARF"
                Range(Cells(Selection.Row, 1), Cells(Selection.Row, 7)).Select
                With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
          Selection.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "Model"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Serial Number"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Black"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Cyan"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Magenta"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Yellow"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Queue Name"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
            Else
           End If
        End With

  FindString = "TES"

        With Sheets("Sheet1").Range("H:H")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1, -1).Activate
                ActiveCell.Value = "TYSONS (WGES)"
                Range(Cells(Selection.Row, 1), Cells(Selection.Row, 7)).Select
                With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "Model"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Serial Number"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Black"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Cyan"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Magenta"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
     ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Yellow"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Value = "Queue Name"
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.ColorIndex = 1
    ActiveCell.Font.Color = RGB(255, 255, 255)
            Else
            End If
        End With
        

Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count

Dim rngCell As Variant
For Each rngCell In Range("A1:G100" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
   End If
Next

Application.ScreenUpdating = True
Columns(8).EntireColumn.Delete

Range("C:F" & LastRow).Replace "", "999", xlWhole
For i = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
    If Cells(i, "C").Value2 <= 10 Then
    Cells(i, "C").Interior.ColorIndex = 15
    End If
Next i

For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
    If Cells(i, "D").Value2 <= 10 Then
    Cells(i, "D").Interior.ColorIndex = 8
    End If
Next i

For i = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(i, "E").Value2 <= 10 Then
    Cells(i, "E").Interior.ColorIndex = 3
    End If
Next i

For i = Cells(Rows.Count, "F").End(xlUp).Row To 2 Step -1
    If Cells(i, "F").Value2 <= 10 Then
   Cells(i, "F").Interior.ColorIndex = 6
    End If
Next i


Application.Calculation = xlAutomatic
Range("C:F" & LastRow).Replace "999", "", xlWhole

Cells.Select
    Cells.EntireColumn.AutoFit
    
Rows(1).Delete
Rows(1).Delete
Range("H1").Select

ActiveWindow.DisplayGridlines = False
End Sub

标签： excelvba

解决方案

参考下面的代码片段：

  1. Dim rngCell as Range- 不是变体
  2. 通常最后一行运算符类似于Range("A1:G" & lr). 你确定你不是那个意思？您将最后一行添加到末尾，G100因此如果您的最后一行是 5，您的循环将转到G1005. 不确定这是否是故意的
  3. 您正在检查该值是否大于没有意义的空白单元格。您是否正在检查一个数字是否大于某个数字？如果是这样，""不是一个数字。您是否在检查单元格是否为空？如果If rng.Cell = ""可以的话
Dim rngCell As Variant
For Each rngCell In Range("A1:G100" & lngLstRow)
    If rngCell.Value > "" Then

推荐阅读