Run-time error 6 Overflow - Excel 2016 Macro

Question

Hello all,

I've the code below, and every time that i execute it, i get the error from the topic, to be more specific the error is being detected on the bold formatted line: (K = K +1).

I've tried already converting the k variable to Long instead of integer, but when i do this i get another error "runtime error 1004 vba application defined or object defined error"

Using the variables as Integer even getting the runtime error 6 the Macro works perfectly but i want to get rid of that error, does anybody have a suggestion for me?.

Thanks!

Sub Workbook_Copy()

'Variables definition

    Dim j As Integer

    Dim i As Integer

    Dim k As Integer

    i = 2

'Open and activate files to work with

    Set x = Workbooks.Open("C:\Users\v99023\Desktop\FMP SoExports\Test Macros\Second Test - Full Swing Related Examples\FMP Open SOs.xlsx")

    Set y = Workbooks.Open("C:\Users\v99023\Desktop\FMP SoExports\Test Macros\Second Test - Full Swing Related Examples\Matching Item FMP-Matrix.xlsx")

    Set Z = Workbooks.Open("C:\Users\v99023\Desktop\FMP SoExports\Test Macros\Second Test - Full Swing Related Examples\SalesOrder.csv")

    x.Activate

    y.Activate

    Z.Activate

'Do whihle loop intiates and validates if the value of the column is different fron blank

 Do While x.Worksheets("Sheet2").Cells(i, 23).Value <> ""

 'If the condition is true it will enter to the cicle and it validates if the customer column is blank

    If x.Worksheets("Sheet2").Cells(i, 1).Value <> "" Then

        Z.Worksheets("SalesOrder").Cells(i, 1) = x.Worksheets("Sheet2").Cells(i, 1)

        Z.Worksheets("SalesOrder").Cells(i, 2) = x.Worksheets("Sheet2").Cells(i, 23)

        Else

        If x.Worksheets("Sheet2").Cells(i, 1).Value = "" Then

        k = i

        Do While x.Worksheets("Sheet2").Cells(k, 1).Value = ""

        Z.Worksheets("SalesOrder").Cells(k, 2).Value = x.Worksheets("Sheet2").Cells(k, 23)

        k = k + 1

        Loop

        End If

    End If

    i = i + 1

Loop

End Sub

Answer 1

IMHO it is obvious that this loop runs to the end of the column:

Do While x.Worksheets("Sheet2").Cells(k, 1).Value = ""

Means the issue is not due to the code, but to the data in your file.

Answer 2

Hi Andreas,

I copied the data to a new file and it worked that way, thank you!