In one program this code works and in similar program this shows run time error 1004 in copying cell cell.value

Question

In one program this code works and in similar program this shows run time error 1004 in copying cell cell.value.

The error is column number is not assigning

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

While debugging, value 15 (column number which matches "Next_6_months" is not assigning to Next_6)

Why So?

Answer 1

The question is not very clear, so I will guess.

Your code has several points to fix:

1. You have to fully qualify your Ranges. This issue shows up once and again (e.g., this).

   What does this mean? Do not use Cells, Range, Rows or Columns without specifying which Worksheet they belong to, unless you specifically want to do that (and even in that case, explicitly using ActiveSheet improves readability and reduces the chances of errors, similar to using Option Explicit). For instance, you use

   MyWorksheetLastRow = Worksheets(1)...
   

   at one point, and in many other cases you use nothing, which defaults to ActiveSheet. Check if this is intended.

2. Fix declaration of variables and function. At the beginning of the module, use

   Option Explicit
   

   and then fix this

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

   and

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

Answer 2

As mentioned previously there are some typo's in your code declaring variables, but it is also likely your ColInstance function doesn't always return what you expect

The following rewrite should be of benefit...

Update

Altered slightly to allow the rows to point directly to sheet1

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