Excel VBA循环列引用问题：跨工作表复制同位置单元格

Hey Brian, I see the issue with that line—you can't directly reference the cell using the variable c like Worksheets("CSV").c because VBA doesn't recognize that syntax. Instead, we need to target the exact same row and column as c in the "CSV" worksheet.

Here's the corrected line for setting a:

Set a = Worksheets("CSV").Cells(c.Row, c.Column)

This works because c.Row pulls the row number of the current cell in CSV2, and c.Column grabs its column number. Using Cells(row, column) on the "CSV" worksheet lets us pinpoint the exact same position cell you need.

Your full corrected code would look like this (I also fixed a small logical operator issue in your If statement—you used & for concatenation instead of And for logical checks, which would have caused unexpected behavior):

Sub et()
 Dim c As Range
 Dim a As Range
 Worksheets("CSV2").Select
 '//loop it
 For Each c In Range(Range("E2"), Range("E2").End(xlDown))
 Set a = Worksheets("CSV").Cells(c.Row, c.Column) '// Fixed this line!
 If c.Value > 0 And c.Value < 3 Then '// Fixed logical operator here
 Sheets("CSV").Select
 a.Copy'// 此处我要复制CSV工作表的内容
 Sheets("CSV2").Select
 c.PasteSpecial Paste:=xlPasteValues'// 此处我要粘贴到CSV2工作表
 End If
 Next
 Worksheets("RETURN").Select
End Sub

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