Runtime error 1004 in VBA

Asked

Viewed 2,266 times

0

Good morning, I made the following code, it runs the code but always appears 400 error message or 1004 runtime error, someone could help me?

Sub atualizarprecos()

j = 2

For m = 1 To 1000
    For i = 4 To 1000

    If Worksheets("PlanilhaNova").Cells(i, 1) = Worksheets("Preços").Cells(j, 3) Then

    Worksheets("PlanilhaNova").Cells(i, 1).Offset(0, 2) = Application.WorksheetFunction.VLookup(Worksheets("Preços").Cells(j, 3), Worksheets("Preços").Range("C2:F85"), 4, 0)

    End If

    Next
    j = j + 1
Next   

End Sub
excel-vba

1 answer

0

all good?

The 1004 error it happens in the following situations:

Due to corruption in MS Excel desktop icon Conflict with other programs while opening the Excel VBA file When filtered data is copied and then pasted into MS Office Excel workbook Due to application or defined object error A range value is set programmatically with a collection of large strings.

See if your repeat loop does not "pop" or search in any worksheet that does not exist, or enter a very high loop

  • Hello! I was able to identify the error in the loop, thank you! But I need the tick to run at least to the 800 line of the spreadsheet and when I put that value in the be it gives him the same problem, it might help me in this loop error?

  • In which FOR loop does he make the mistake? m or i? I find it interesting that you also create an IF rule for the J counter, if the J > 800 counter you go to the end of the application, otherwise perform the activity

  • I fixed it this way: For j = 2 To 85 For i = 4 To 1000 If Worksheets("Planilhanova"). Cells(i, 1) = Worksheets("Prices"). Cells(j, 3) Then Worksheets("Planilhanova"). Cells(i, 1). Offset(0, 2) = Worksheetfunction.Vlookup(Worksheets("Prices").Cells(j, 3), Worksheets("Prices").Range("C2:85"), 4, 0) End If Next Next ||| But it was limited to the number of cells I have in the "Prices" spreadsheet, when I put a value greater than 85 in the j it gives the error.

  • Try to use a Iferror

Browser other questions tagged excel-vba

You are not signed in. Login or sign up in order to post.