VBA停车管理工具：无法动态获取Range对象进行格式化求助

你的代码核心问题是无法通过字符串拼接变量名直接获取Range对象，VBA不支持这种动态变量引用方式。下面提供两种修复方案，优先推荐字典方案，更适合后续扩展数百个车位的需求：

方案一：使用字典映射车位编号与区域（推荐） #

用字典存储车位编号和对应的Range对象，后续新增车位只需往字典里添加条目，代码扩展性极强：

Sub No_01_to_05a()
    Dim gfList As Worksheet
    Dim gfPlan As Worksheet
    Dim status As String
    Dim CurrentLot As Range
    Dim i As Integer
    Dim No As String
    Dim lotDict As Object ' 字典对象
    
    Set gfList = ThisWorkbook.Worksheets("GF List")
    Set gfPlan = ThisWorkbook.Worksheets("GF")
    Set lotDict = CreateObject("Scripting.Dictionary")
    
    ' 填充字典：键=车位编号，值=对应的Range区域
    lotDict("1") = gfPlan.Range("B2:C2")
    lotDict("2") = gfPlan.Range("D2:E2")
    lotDict("3") = gfPlan.Range("F2:G2")
    lotDict("4") = gfPlan.Range("H2:I2")
    lotDict("5") = gfPlan.Range("J2:K2")
    lotDict("5a") = gfPlan.Range("M2:M3")
    
    ' 遍历GF List中的数据
    For i = 4 To gfList.Range("E" & Rows.Count).End(xlUp).Row
        status = gfList.Range("E" & i).Value
        No = gfList.Range("B" & i).Value
        
        ' 检查字典中是否存在该车位编号
        If lotDict.Exists(No) Then
            Set CurrentLot = lotDict(No)
            ' 根据状态设置格式
            Select Case status
                Case "Vacant"
                    CurrentLot.Interior.Color = RGB(255, 255, 0)
                Case "Let"
                    CurrentLot.Interior.Color = RGB(146, 208, 80)
                Case "Reserved"
                    CurrentLot.Interior.Color = RGB(0, 176, 240)
                Case Else
                    CurrentLot.Interior.Color = RGB(255, 255, 255)
            End Select
            CurrentLot.Font.Color = RGB(0, 0, 0) ' 统一设置字体颜色
        End If
    Next i
End Sub

方案二：使用Select Case匹配变量（适合少量车位） #

如果暂时不想用字典，也可以用Select Case直接匹配车位编号，给CurrentLot赋值：

Sub No_01_to_05a()
    Dim gfList As Worksheet
    Dim gfPlan As Worksheet
    Dim status As String
    Dim CurrentLot As Range
    Dim i As Integer
    Dim No As String
    
    Set gfList = ThisWorkbook.Worksheets("GF List")
    Set gfPlan = ThisWorkbook.Worksheets("GF")
    
    ' 提前定义所有车位区域
    Dim LotNo1 As Range, LotNo2 As Range, LotNo3 As Range
    Dim LotNo4 As Range, LotNo5 As Range, LotNo5a As Range
    Set LotNo1 = gfPlan.Range("B2:C2")
    Set LotNo2 = gfPlan.Range("D2:E2")
    Set LotNo3 = gfPlan.Range("F2:G2")
    Set LotNo4 = gfPlan.Range("H2:I2")
    Set LotNo5 = gfPlan.Range("J2:K2")
    Set LotNo5a = gfPlan.Range("M2:M3")
    
    ' 遍历GF List中的数据
    For i = 4 To gfList.Range("E" & Rows.Count).End(xlUp).Row
        status = gfList.Range("E" & i).Value
        No = gfList.Range("B" & i).Value
        
        ' 匹配车位编号，赋值CurrentLot
        Select Case No
            Case "1": Set CurrentLot = LotNo1
            Case "2": Set CurrentLot = LotNo2
            Case "3": Set CurrentLot = LotNo3
            Case "4": Set CurrentLot = LotNo4
            Case "5": Set CurrentLot = LotNo5
            Case "5a": Set CurrentLot = LotNo5a
            Case Else: Set CurrentLot = Nothing ' 未知编号跳过
        End Select
        
        ' 仅当CurrentLot有效时执行格式化
        If Not CurrentLot Is Nothing Then
            If status = "Vacant" Then
                CurrentLot.Interior.Color = RGB(255, 255, 0)
            ElseIf status = "Let" Then
                CurrentLot.Interior.Color = RGB(146, 208, 80)
            ElseIf status = "Reserved" Then
                CurrentLot.Interior.Color = RGB(0, 176, 240)
            Else
                CurrentLot.Interior.Color = RGB(255, 255, 255)
            End If
            CurrentLot.Font.Color = RGB(0, 0, 0)
        End If
    Next i
End Sub

关键修复点说明 #

1. 动态变量引用问题：原代码CurrentLot = "LotNo" & No试图用字符串拼接获取变量，这在VBA中不成立，必须通过字典或Select Case显式映射。
2. Range对象赋值：给Range类型变量赋值必须用Set关键字，原代码缺少Set也会报错。
3. 扩展性优化：字典方案后续新增车位只需在字典中添加一行，无需修改循环内的逻辑，完美适配数百个车位的需求。

内容的提问来源于stack exchange，提问作者hndrx99