Excel VBA，更快、更干净地查找匹配值/索引匹配并从另一列返回值的方法？

Question

我在下面编写的代码用于替换工作表中的一些索引匹配公式。它似乎工作得很好，但我认为循环有点笨拙，可能容易出错。有人有什么改进建议吗？

Sub match_SIC_code_sheet_loop()

'sic code needs to match value in column j or a in sic code sheet, '
'if not available = met10 works, but probably needs a bit more
'debugging to make it robust.

Dim ws As Integer
Dim lastrow As Long
Dim lastrow_sic As Long
Dim output_wb As Workbook
Dim SIC_sheet As Worksheet
Dim Demand_CAT As String
Dim sic_DMA As String
Dim i As Integer
Dim row As Integer
Dim WS_count As Long
Dim x As String
Dim y As String

Set output_wb = Workbooks("DMA_customers_SICTEST.xlsx") 'use thisworkbook instead
Set SIC_sheet = Workbooks("DMA_metered_tool_v12_SICTEST.xlsm").Sheets("SIC codes")

With SIC_sheet 'count the number of SIC codes to search through
    lastrow_sic = .Range("j" & .Rows.Count).End(xlUp).row
End With

With output_wb 'count the no. of sheets in the generated customer workbook
    WS_count = output_wb.Worksheets.Count
End With

With output_wb
    For ws = 1 To WS_count 'loop through each sheet in the customer workbook
        With output_wb.Sheets(ws)
            y = output_wb.Sheets(ws).Name
            lastrow = .Range("a" & .Rows.Count).End(xlUp).row ' number of rows in the
            'current customer sheet
            For i = 2 To lastrow 'data starts in row 2; sic code in column 9
                sic_DMA = .Cells(i, 9).Text 'the lookup value
                With SIC_sheet
                'SIC codes start in row 2, if the sic code matches,
                'the correct demand category is appointed, if the sic code does not
                'match, then MET_10 is given as the default value.
                    For row = 2 To lastrow_sic
                        x = .Cells(row, 3).Text
                        If x = sic_DMA Then
                            Demand_CAT = .Cells(row, 10).Text
                        Exit For
                        Else
                            Demand_CAT = "MET_10"
                        End If
                    Next row
                output_wb.Sheets(ws).Cells(i, 23).Value = Demand_CAT
                End With
            Next i
        End With
    Next ws
End With
output_wb.Save
End Sub

谢谢

Answer 1

首先，您可以将这个长过程分成几个较小的方法。例如，您可以有一个ProcessSheet过程，您可以将下面的每个工作表传递给该过程：

For ws = 1 To WS_count 'loop through each sheet in the customer workbook

这肯定有助于可读性等。如果您仍然不满意，那么继续将循环分解成更小的逻辑过程。只是不要太疯狂。

除此之外，一些错误检查和值验证将在一个深度嵌套的循环中进行。例如，确保各种计算变量（如“lastrow”）正确或在有效阈值内等。

最后，不要像神奇的伪装调试仙女一样在你的长循环中散布硬编码值；相反，更喜欢一些有意义的命名为Const的变量替代方案，即。

Private Const SIC_START_ROW = 2