在一个程序中，该代码有效，在类似的程序中，它显示了复制单元格值时的运行时错误1004

Question

在一个程序中，此代码有效，在类似的程序中，它显示了复制单元格值时的运行时错误1004。

错误是列号未分配

Dim Next_6, PriceChange, Price_i, MyWorksheetLastRow As Long
MyWorksheetLastRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Next_6 = ColInstance("Next_6_months", 1)
'Next_6 = 15
For Price_i = 2 To MyWorksheetLastRow
Cells(Price_i, Next_6).Value = Cells(Price_i, Next_6).Value & " " & Cells(Price_i, Next_6 + 1).Value
Next Price_i
Function ColInstance(HeadingString As String, InstanceNum As Long)
Dim ColNum As Long
On Error Resume Next
ColNum = 0
For X = 1 To InstanceNum
ColNum = (Range("A1").Offset(0, ColNum).Column) + Application.WorksheetFunction.Match(HeadingString, Range("A1").Offset(0, ColNum + 1).Resize(1, Columns.Count - (ColNum + 1)), 0)
Next
ColInstance = ColNum
End Function

调试时，值15（与"下一个_6_months"匹配的列号未分配给下一个6）

为什么会这样？

Answer 1

这个问题不是很清楚，所以我猜。

您的代码有几点需要修复：

1. 您必须完全符合Range的资格。这个问题一次又一次地出现（例如，这个）。

   这是什么意思在不指定Cells、Range、Rows或Columns属于哪个Worksheet的情况下，不要使用它们，除非您特别想这样做（即使在这种情况下，显式使用ActiveSheet也可以提高可读性并降低出错几率，类似于使用Option Explicit）。例如，您使用

   MyWorksheetLastRow = Worksheets(1)...
   

   在某一点上，在许多其他情况下，您什么都不使用，默认为ActiveSheet。检查是否有意这样做。

2. 修复变量和函数的声明。在模块开始时，使用

   Option Explicit
   

   然后修复这个

   Dim Next_6 As Long, PriceChange As ..., Price_i As Long, MyWorksheetLastRow As Long
   

   和

   Function ColInstance(HeadingString As String, InstanceNum As Long) As Long
   Dim ColNum As Long, X As Long
   

Answer 2

如前所述，在声明变量的代码中有一些拼写错误，但ColInstance函数也可能并不总是返回您期望的

下面的重写应该是有益的。。。

更新

略微更改以允许行直接指向纸张1

Sub AssignValues()
    Dim Next_6 As Long, PriceChange As Long, Price_i As Long, MyWorksheetLastRow As Long
    With ThisWorkbook.Worksheets(1)
        MyWorksheetLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Next_6 = ColInstance("Next_6_months", 1)
        If Next_6 > 0 Then
            For Price_i = 2 To MyWorksheetLastRow
                .Cells(Price_i, Next_6).Value = .Cells(Price_i, Next_6).Value & " " & .Cells(Price_i, Next_6 + 1).Value
            Next Price_i
        End If
    End With
End Sub
Function ColInstance(Header As String, Optional Instance As Long = 1) As Long
    ' Function returns 0 if Header doesn't exist in specified row
    ' Function returns -1 if Header exists but number of instances < specified
    ColInstance = 0
    Dim i As Long: i = 1
    Dim c As Range
    With ThisWorkbook.Worksheets(1).Rows(1)
        Set c = .Find(Header, LookIn:=xlValues)
        If Not c Is Nothing Then
            FirstAdr = c.Address
            Do
                i = i + 1
                If i > Instance Then
                    ColInstance = c.Column
                    Exit Do
                End If
                Set c = .FindNext(c)
            Loop While c.Address <> FirstAdr
            If c.Address = FirstAdr And Instance > 1 Then ColInstance = -1
        End If
    End With
End Function