VBA根据单元格文本获取列字母并在循环中使用的问题

我尝试根据单元格中的文本查找对应的列字母，目前已成功获取到列字母，但在后续循环中将该列字母（声明为trcol）作为目标复制列时遇到问题。需要让trcol在循环的Cells(i, trcol).Copy语句中正常生效，以下是我编写的代码：

Dim trcol as String
Workbooks.Open (ThisWorkbook.Path & workb)
Sheets(sh).Select
Set Cell = Cells.Find("Customer Score %", , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then

  ColLetter = Split(Cell.Address, "$")(1)
     trcol = """ & ColLetter & """ 
    
     
Else
  MsgBox "I cannot find that text on this sheet"
End If
''''''''''''''''''''''
'the loop
''''''''''''''''''''''
Dim N As Long, i As Long

Workbooks.Open (ThisWorkbook.Path & workb)
N = Cells(Rows.Count, sccol).End(xlUp).Row


For i = 2 To N
Dim totalRnNum As Integer: totalRnNum = Range("F100").End(xlUp).Row
Dim totalVal As Double: totalVal = Cells(totalRnNum, 6).Value
Cells(i, trcol).Copy '''''''here i need to have the result of the search as trcol


Next i

问题根源 #

你给trcol赋值时多套了一层引号：trcol = """ & ColLetter & """，这会让trcol变成带双引号的字符串（比如列是B的话，trcol实际值是"B"），但Cells的列参数接受的是纯列字母字符串（如"B"）或列号数字，带引号的字符串会被识别为无效引用，导致代码报错。

另外你重复打开了同一个工作簿两次，这不仅冗余，还可能引发文件占用错误；同时使用Select操作工作表容易导致后续代码操作对象混乱。

修正后的代码 #

Dim trcol As String
Dim wb As Workbook
'用变量保存打开的工作簿，避免重复打开
Set wb = Workbooks.Open(ThisWorkbook.Path & workb)

Set Cell = wb.Sheets(sh).Cells.Find("Customer Score %", , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
    '直接赋值纯列字母字符串
    trcol = Split(Cell.Address, "$")(1)
Else
    MsgBox "I cannot find that text on this sheet"
    '找不到目标时直接退出，避免后续代码报错
    Exit Sub
End If

'循环部分
Dim N As Long, i As Long
Dim totalRnNum As Integer
Dim totalVal As Double

'明确指定工作表和工作簿，避免对象混淆
N = wb.Sheets(sh).Cells(wb.Sheets(sh).Rows.Count, sccol).End(xlUp).Row

For i = 2 To N
    totalRnNum = wb.Sheets(sh).Range("F100").End(xlUp).Row
    totalVal = wb.Sheets(sh).Cells(totalRnNum, 6).Value
    '现在trcol是纯列字母，可正常用于Cells引用
    wb.Sheets(sh).Cells(i, trcol).Copy
Next i

关键修正点 #

1. 移除trcol赋值时的多余引号，让trcol直接存储纯列字母字符串
2. 用变量wb引用打开的工作簿，明确操作对象，避免重复打开文件
3. 找不到目标文本时执行Exit Sub，防止后续代码在无效状态下运行
4. 所有单元格操作都指定工作簿和工作表，避免因激活其他表导致的错误

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