将数据移动到另一个工作表时出现运行时错误"1004"

Question

我正在分析一些光谱，并尝试选择对应于特定波长的强度读数，并将它们粘贴到新的工作表中。代码确实运行，但它呈现

运行时错误"1004" - 工作表 (2( 上的应用程序定义或对象定义的错误。单元格(i， "B"(。值 = 工作表(1(。单元格(绿色，列号(。值代码行

Sub GreenWlValues()
Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet 
targetwl = 9 'example value
columnnumber = 2
i = 2
For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Do While  Cells(greenwl, "A").Value = targetwl
Worksheets(2).Cells(i, "B").Value = Worksheets(1).Cells(greenwl, 
columnnumber).Value
columnnumber = columnnumber + 1
i = i + 1
Loop
Next greenwl

End Sub

Answer 1

您不能在"cell"对象中将列名作为字母传递，因此您必须将"B"更改为 2 。下面找到更新的代码：

Sub GreenWlValues()
Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet
targetwl = 9 'example value
columnnumber = 2
i = 2
For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Do While Cells(greenwl, 1).Value = targetwl
Worksheets(2).Cells(i, 2).Value = Worksheets(1).Cells(greenwl,columnnumber).Value
columnnumber = columnnumber + 1
i = i + 1
Loop
Next greenwl

End Sub