Click here to Skip to main content
15,611,107 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

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
Updated 30-Jun-22 2:51am

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])
Share this answer
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 ="your path/your workbook name", ,Readonly:=True)
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
Thanks to all but my solution it Is the correct one
Share this answer
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.
Share this answer
hi, solution found,

Share this answer
Start by using the debugger ([^] 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!
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900