VBA无法读取单元格值：相似代码对比排查问题

我有两段结构相似、均使用For Each循环的VBA代码，其中FeedbackFilesExtract能正常获取单元格值，但ExtractFeedbackStats无法识别单元格内的数据，调整变量类型也未能解决，请求排查问题。

问题代码（ExtractFeedbackStats） #

Sub ExtractFeedbackStats()

Const Path As String ="C:\\feedbackFiles_4analysis\\"
Const fileExtension As String = "*.xlsx"

 'Variables
 Dim feedbackFile As Variant
 Dim analysisFile As Variant
 Dim wb As Workbook
 Dim ws As Worksheet
 Dim Cell As Range
 Dim errorRange As Range
 Dim lastRow As Variant
 Dim counter As Integer
 Dim cellValue As Variant

'Target path with extension
 feedbackFile = Dir(Path & fileExtension)

 'Set constants
 firstRow = "2"
 errorColumn = "AG"

  'Loop through file error detail report, look at HARD_ERROR_CD column
  Do While feedbackFile <> ""

'Open each spreadsheet in folder
Set wb = Workbooks.Open(Filename:=Path & feedbackFile)
DoEvents

'Set parameters for scanning
lastRow = Range("E2").Value + 1

'Set data range to be scanned
Set errorRange = Range(errorColumn & firstRow, errorColumn & lastRow)

'Establish counter for error code instances
counter = 0

'Loop through each row in spreadsheet
For Each Cell In errorRange.Cells
    cellValue = Cell.Value
    
    'If: GR001 Error, Then: Return Customer Code + unique_counter
    If cellValue = "GR001" Then counter = 100

        Next Cell

'Ensure Workbook has closed before moving on to the next line of code
wb.Close SaveChanges:=False
DoEvents

'Get next file name
feedbackFile = Dir

'Close Loop
 Loop
 
 End Sub

正常代码（FeedbackFilesExtract） #

Sub FeedbackFilesExtract()

'Update this folder to local
 Const destPath As String = "C:\\feedbackFiles_4analysis\\"
 Const srcPath As String = "M:\\Feedback\\"

 'Variables
 Dim ws As Worksheet
 Dim srcFile As Variant
 Dim destFile As Variant
 Dim File As Variant
 Dim fileArray() As Variant
 Dim dataRange As Range
 Dim Cell As Range
 Dim arrayCounter As Integer
 Dim counter As Integer

'Set data range
Set ws = Sheets("FEEDBACK_FILE_LIST")
secondColumn = "B"
firstRow = "2"
lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set dataRange = Range(secondColumn & firstRow, secondColumn & lastRow)             

'Set size of array to accomodate list
ReDim fileArray(dataRange.Cells.Count)
                                                                        

'Populate array with list of files from xlsm
 For Each Cell In dataRange.Cells
        fileArray(arrayCounter) = Cell.Value
        arrayCounter = arrayCounter + 1
 Next Cell
           

排查出的问题及修复方案 #

1. 未明确指定工作表对象 #

问题代码中Range("E2")、Range(errorColumn & firstRow, ...)均未指定所属工作表，VBA会默认使用当前激活的工作表，而打开目标工作簿后激活的工作表不一定是你要操作的那张。正常代码里明确指定了ws = Sheets("FEEDBACK_FILE_LIST")，所有Range操作都基于该工作表，这是核心差异。

修复：打开工作簿后立即指定目标工作表，比如：

Set ws = wb.Sheets("你的工作表名称") ' 或用索引wb.Sheets(1)表示第一个工作表

之后所有Range调用都改为ws.Range(...)。

2. lastRow计算逻辑不可靠 #

问题代码依赖Range("E2").Value + 1获取最后一行，如果E2为空、非数字，会导致lastRow错误，进而让errorRange范围失效。正常代码用ws.Cells(Rows.Count, "B").End(xlUp).Row获取实际数据的最后一行，逻辑更稳定。

修复：增加E2值的校验，或者改用可靠的方式获取最后一行：

If IsNumeric(ws.Range("E2").Value) Then
    lastRow = ws.Range("E2").Value + 1
Else
    ' 备用方案：自动获取该列最后一行
    lastRow = ws.Cells(ws.Rows.Count, errorColumn).End(xlUp).Row
End If

3. 未声明变量导致的潜在问题 #

问题代码中firstRow、errorColumn未声明，属于变体类型，虽然能运行但容易引发类型不匹配问题。

修复：添加变量声明：

Dim firstRow As String
Dim errorColumn As String

4. 单元格值比较的细节问题 #

如果单元格内的"GR001"带有前后空格，或者存储格式不是文本，直接cellValue = "GR001"会匹配失败。

修复：使用Trim()去除空格，或用Cell.Text获取显示值：

If Trim(Cell.Value) = "GR001" Then counter = 100

修复后的完整代码 #

Sub ExtractFeedbackStats()

Const Path As String = "C:\\feedbackFiles_4analysis\\"
Const fileExtension As String = "*.xlsx"

'Variables
Dim feedbackFile As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim Cell As Range
Dim errorRange As Range
Dim lastRow As Long
Dim counter As Integer
Dim cellValue As Variant
Dim firstRow As String
Dim errorColumn As String

'Target path with extension
feedbackFile = Dir(Path & fileExtension)

'Set constants
firstRow = "2"
errorColumn = "AG"

'Loop through file error detail report, look at HARD_ERROR_CD column
Do While feedbackFile <> ""
    'Open each spreadsheet in folder
    Set wb = Workbooks.Open(Filename:=Path & feedbackFile)
    DoEvents
    
    ' 指定目标工作表，根据实际情况修改名称
    Set ws = wb.Sheets("目标工作表名")
    
    'Set parameters for scanning
    If IsNumeric(ws.Range("E2").Value) Then
        lastRow = ws.Range("E2").Value + 1
    Else
        lastRow = ws.Cells(ws.Rows.Count, errorColumn).End(xlUp).Row
    End If
    
    'Set data range to be scanned
    Set errorRange = ws.Range(errorColumn & firstRow, errorColumn & lastRow)
    
    'Establish counter for error code instances
    counter = 0
    
    'Loop through each row in spreadsheet
    For Each Cell In errorRange.Cells
        cellValue = Cell.Value
        ' 去除空格后比较
        If Trim(cellValue) = "GR001" Then counter = 100
    Next Cell
    
    'Ensure Workbook has closed before moving on to the next line of code
    wb.Close SaveChanges:=False
    DoEvents
    
    'Get next file name
    feedbackFile = Dir
Loop
 
End Sub

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