How do we remake program. The program return error overflow error 6

A VBA Lookup: Lookup Values Efficiently

• Although you could make it work using the Find method, for a huge data set it would become too slow, so I didn't bother.
• Using arrays to read from and write to and a single Application.Match call to return the matching source row indices in an array will do the trick.

Sub Insertdata()
    
    ' Define constants.
    
    Const SRC_FILE_PATH As String = _
        "\\s-fa\PRICES\m\inputintoresultchr\input.xlsm"
    Const SRC_SHEET_NAME As String = "Sheet1"
    Const SRC_TOP_LOOKUP_CELL As String = "A2"
    Const SRC_RETURN_COLUMN As String = "E"
    
    Const DST_SHEET_NAME As String = "Sheet1"
    Const DST_TOP_LOOKUP_CELL As String = "A2" ' ??? you used 'A1'!?
    Const DST_RETURN_COLUMN As String = "C"
    
    Const IF_NOT_FOUND As Variant = Empty
    
    ' Reference the destination lookup and return ranges.
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Sheets(DST_SHEET_NAME)
    
    Dim dlrg As Range, dRowsCount As Long
    
    With dws.Range(DST_TOP_LOOKUP_CELL)
        dRowsCount = dws.Cells(dws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If dRowsCount < 1 Then
            MsgBox "No data in ""'" & dws.Name & "'!" _
                & .Resize(dws.Rows.Count - .Row + 1).Address(0, 0) & """!", _
                vbExclamation
            Exit Sub
        End If
        Set dlrg = .Resize(dRowsCount)
    End With
    
    Dim drrg As Range: Set drrg = dlrg.EntireRow.Columns(DST_RETURN_COLUMN)
    
    ' Reference the source lookup range and copy the values
    ' of the source return range to an array, the source return array.
    
    Dim swb As Workbook: Set swb = Workbooks.Open( _
        Filename:=SRC_FILE_PATH, UpdateLinks:=True, ReadOnly:=True)
    Dim sws As Worksheet: Set sws = swb.Sheets(SRC_SHEET_NAME)
    
    Dim slrg As Range, sRowsCount As Long
    
    With sws.Range(SRC_TOP_LOOKUP_CELL)
        sRowsCount = sws.Cells(sws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If sRowsCount < 1 Then
            MsgBox "No data in ""'" & sws.Name & "'!" _
                & .Resize(sws.Rows.Count - .Row + 1).Address(0, 0) & """!", _
                vbExclamation
            Exit Sub
        End If
        Set slrg = .Resize(sRowsCount)
    End With
            
    Dim srData() As Variant
    
    With slrg.EntireRow.Columns(SRC_RETURN_COLUMN) ' source return range
        If sRowsCount = 1 Then
            ReDim srData(1 To 1, 1 To 1): srData(1, 1) = .Value
        Else
            srData = .Value
        End If
    End With
            
    ' Return the matching row indices in an array, the destination return array.
            
    Dim drData As Variant: drData = Application.Match(dlrg, slrg, 0)
    
    ' Copy the matching data to the destination return range.
    
    If dRowsCount > 1 Then ' the destination ranges have multiple cells (rows)
    
        ' Loop through the elements (rows) of the destination return array.
        ' If the value is numeric (in this case an integer),
        ' use it as the source row to retrieve the matching return value
        ' from the source return array and replace the value with it.
        ' If the value is an error ('Error 2042' ('#N/A')),
        ' replace it with the 'IF_NOT_FOUND' constant.
    
        Dim dRow As Long, sRow As Variant
        
        For dRow = 1 To dRowsCount
            sRow = drData(dRow, 1)
            If IsNumeric(sRow) Then
                drData(dRow, 1) = srData(sRow, 1)
            Else
                drData(dRow, 1) = IF_NOT_FOUND
            End If
        Next dRow
        
        ' Copy the values from the destination return array
        ' to the destination return range.
        drrg.Value = drData
    
    Else ' the destination ranges have a single cell (row)
    
        ' Copy the matching value to the destination return cell.
        If IsNumeric(drData) Then
            drrg.Value = srData(drData, 1)
        Else
            drrg.Value = IF_NOT_FOUND
        End If
    
    End If

    ' Close the source workbook.

    'swb.Close SaveChanges:=False

    ' Inform.
    
    MsgBox "Data retrieved.", vbInformation
    
End Sub