Excel VBA run time error 1004

Question

0.00/5 (No votes)

See more:

Excel-2016

hello everyone
o have below code

Sub EstraiData()

Application.DisplayAlerts = False
Dim Risposta As Byte

Dim Source As Workbook, SourceSheet As Worksheet, PrimaRiga As Integer, UtlimaRiga As Integer, Dati As Range
Dim QuestoSheet As Worksheet, QuestoBook As Workbook
Set QuestoBook = ThisWorkbook
Set QuestoSheet = QuestoBook.Sheets(1) '<---------------- definisco foglio tabella



Set Source = Workbooks.Open(percorso & "/" & nomefile, False, False) '<-------------- apri file read only
Set SourceSheet = Source.Sheets(1)

    Dim i As Integer, Temporary As Integer, UtlimaColonna As Integer, Counter As Integer
        For i = 1 To 26
        Counter = WorksheetFunction.CountA(Columns(i).EntireColumn)
    
            If Cells(1, i).Value = "" And Counter > 0 Then
                Temporary = Cells(1, i).End(xlDown).Row
                
                UtlimaColonna = i
                
                If PrimaRiga < Temporary Then
                    PrimaRiga = Temporary
                End If
            End If

        Next
Stop
Dim UtlimaRigaDATI As Integer

        UtlimaRigaDATI = SourceSheet.Cells(1048576, 1).End(xlUp).Row
        UtlimaRiga = SourceSheet.Cells(1048576, 10).End(xlUp).Row - 1

For i = 1 To UtlimaRiga
    Temporary = (i + PrimaRiga - 1)
    Cells(Temporary, 8).Formula = "=INDEX($A$2:$B$" & UtlimaRigaDATI & ";MATCH($J" & Temporary & ";$A$2:$A$" & UtlimaRigaDATI & ";0);2)"    '<----- ERROR LINE
Next i



UtlimaRiga = Cells(1048576, UtlimaColonna).End(xlUp).Row - 1

End Sub

THE error pops out in error line

What I have tried:

i replaced the string formula with whatever string and it works
tried to insert 'set' command at beginning of line but it s wrong

Posted 2-Jun-22 23:12pm

Mat 257

Updated 30-Jun-22 2:51am

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-06-02T23:37:00

Start by using the debugger (https://www.myonlinetraininghub.com/debugging-vba-code[^] will help if you have no idea how to do that) and look ate exactly what the string concatenation generates:

percorso & "/" & nomefile

It contains two variables, so it's important to find out what the resulting string is, exactly - assumptions and guesses don't count here!

When you have that string, first check it is a valid path, and then look at the path to the file it represents using windows explorer and check that the file and path all exist, and that the user running the code has the correct permissions to access the full path. Then check that the file is actually an excel file ...

Sorry, but we can't do any of that for you!

Mat 257 · Answer 2 · 2022-06-03T03:38:00

Solution 2

hi, solution found,

"https://stackoverflow.com/questions/28557438/excel-vba-run-time-error-1004-when-inserting-or-value-formula-into-cell"

Posted 3-Jun-22 3:38am

Mat 257

matblue25 · Answer 3 · 2022-06-03T07:31:00

Solution 3

You have semicolons instead of commas in your formula.
That will give you a 1004 every time.

In the ERROR LINE, change ";" to "," in four places.

Posted 3-Jun-22 7:31am

matblue25

Updated 3-Jun-22 7:32am

v2

Mat 257 · Answer 4 · 2022-06-03T21:59:00

Solution 4

Thanks to all but my solution it Is the correct one

Posted 3-Jun-22 21:59pm

Mat 257

behruz12 · Answer 5 · 2022-06-30T02:51:00

Solution 5

It maybe relates to open as open has components:
Open(Filename As String, [UpdatedLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad])

Posted 30-Jun-22 2:51am

behruz12

Comments

CHill60 30-Jun-22 9:10am

The OP quite clearly marked which line was causing the error - it was no where near the Open statement.

behruz12 5-Jul-22 9:21am

In case for open a workbook as read only you can't make a change for workbook and need to save a copy of it. here:
set source = workbooks.open(Filename:="your path/your workbook name", ,Readonly:=True)
https://excelchamps.com/vba/open-workbook

CHill60 5-Jul-22 12:47pm

I know how to save a workbook. Your "solution" does not address the problem that the OP posted

behruz12 5-Jul-22 14:24pm

What does this mean: apri file read only & if file is read only then third component should be ReadOnly:= True and not False (please give your solution).

CHill60 7-Jul-22 4:19am

I don't have to give a solution. The solution is ALREADY posted as "Solution 2". The original problem has NOTHING to do with opening a file - read the question!!

Cells(Temporary, 8).Formula = "=INDEX($A$2:$B$" & UtlimaRigaDATI & ";MATCH($J" & Temporary & ";$A$2:$A$" & UtlimaRigaDATI & ";0);2)"    '<----- ERROR LINE

behruz12 7-Jul-22 8:25am

Thanks.

Excel VBA run time error 1004

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us