Run time error 1004

jeskit · January 14th, 2011, 10:24 AM

hI

Thank you so much you have been really really helpful.

I have it working with

Code:

.Offset(1, 2).Formula = "=COUNTA($A:$A)"

But its not bringing back a result it just says 0 when i know there are rows with text in the files its looking at

mtranchi · January 14th, 2011, 10:32 AM

that's because

Code:

.Offset(1, 2).Formula = "=COUNTA($A:$A)"

is referring to the workbook that was active when you started running the code. So in the active sheet of the active workbork, put some text in there to see if it picks up that text.
Also, maybe you could try doing everything you need to do and then after the loop open Extract_Size and see if that works

jeskit · January 14th, 2011, 10:44 AM

hi

You;re right it is reference the active workbook. I put some text in column a the ExtractSizeChecker document and it picked it up straight away.

I have put the workbook its opening as a reference so the code looks like this

Code:

Sub Get_Dealer_Count()
Dim ws As Worksheet
Dim dd As Workbook
Dim xlCalc As XlCalculation
Dim strFldr As String
Dim strFile As String
Dim wb1 As Workbook

Application.ScreenUpdating = False
        
Set wb1 = Workbooks.Open("C:\Documents and Settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

Set ws = wb1.Sheets("Dealer Extracts")

ws.Range("F17:H38").ClearContents
ws.Range("F17:H17").Value = [{"Directory", "Filename", "Row Number"}]

strFldr = ("C:\Production2\ATX\Extracts\201001\DealerData")

strFile = Dir(strFldr & "\*.csv*")

If Len(strFile) > 0 Then
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    Do
        Set dd = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        With ws.Cells(ws.Rows.Count, "F").End(xlUp)
            .Offset(1).Value = strFldr
            .Offset(1, 1).Value = strFile
            .Offset(1, 2).Formula = "=COUNTA($A:$A)"
            .Value = .Value
        End With
        wb.Close False
        strFile = Dir
        Application.StatusBar = strFile
    Loop Until Len(strFile) = 0
End If

With ws.Range("F17:H17")
    .Font.Bold = False
    .EntireColumn.AutoFit
End With

End Sub

I tried to reference wb.sheets(1) in the forumla but it did not work and came up with the error from earlier

mtranchi · January 14th, 2011, 10:48 AM

can you give me the specific code that's not working? It looks like you're missing the .name part of the wb.sheets(1).name

jeskit · January 14th, 2011, 10:50 AM

Yep no problem.

the code thats not working is

Code:

 .Offset(1, 2).Formula = "=COUNTA($A:$A)"

I took out the .name bit of the code to see when it broke but when i put the .name bit in the error appeared again

mtranchi · January 14th, 2011, 10:53 AM

but you said earlier that

Code:

 .Offset(1, 2).Formula = "=COUNTA($A:$A)"

does work and picks up the data in the active workbook.

jeskit · January 14th, 2011, 10:58 AM

Sorry

Yes i meant that bit does work and picks up data from the extractsizechecker document. I tested it by placing text in column a in the extractsizechecker and ran and its picking the text up in there not the csv files from dealerdata.

mtranchi · January 14th, 2011, 11:22 AM

ok so your next step is to continue to build that Counta formula. You know it works referring to the currently active workbook, manually open another workbook, put some data in the A:A range, then in your code set a reference to that manually opened workbook, then see if you can count the number of rows that have text in that manually opened workbook. Keep gradually building that formula up.

Also, what version of excel are you using? And help clear up the picture for me a bit. You have one workbook that is the one holding the code and that's the only one you've been running the code from correct? And that's the one where the data that you're extracting from is going into at least temporarily. What I don't understand is where the ExtractSizeChecker workbook comes in. I see nowhere in your code where you ever refer to it. You open it but never do anything with it. Why do you need it open? And if you need it open, why can't you open it after the looping procedure?

jeskit · January 14th, 2011, 11:34 AM

Ok

I been able to get the code to got to

Code:

 .Offset(1, 2).Formula = "=COUNTA('Sheet1'!$A:$A)"

But then when i try to add

Code:

 .Offset(1, 2).Formula = "=COUNTA('["wb"]Sheet1'!$A:$A)"

it falls over

I am using 2010

Yes there is one workbook which is holding code, another workbook (extractsizechecker) which is where i want the results to go and teh folder with the files i want the forumula to be performed on.

Yes

Currently it is extracting data from extractsizechecker

It has gotton very confusing the code started with be able to put the results in the workbook which was holding the code but i was told i had to store the reuslts in another document and i have been trying to do that with no luck.

I hope that helps.

jeskit · January 14th, 2011, 11:35 AM

Also i cant put it after the loop because the loop its self reference's ws which references wb1.......If that makes sense