Application Define or object defined error

[dgrimm]

There are probably hundreds of posts on this error and each one is different. I am trying to open files and put some data in another file. I don't want to have to manually open all of them because there is something like 450 of them. So I came up with this code and I am getting the above error on the first "range =" and I am guessing that I will get it on the second one also. Here is the code.

Sub ClosedClaims()

Dim i As Long
Dim st As Range
Dim it As Range

Set st = Range("State")
Set it = Range("InjType")

Workbooks.Open Filename:= _
"I:\WC\CVM\Exposure Curves Project\Distributions\Output\Closed\" & st & "_" & it & "_Closed ($1B claims removed)v4.xlsm"

Windows("Combined output.xlsm").Activate
Range("B12").Select

For i = 12 To 1962

Range("B" & i).Formula = "='[" & st & "_" & it & "_Closed ($1B claims removed)v4.xlsm]for R'!" & Range("B" & i)
Range("C" & i).Formula = "='[" & st & "_" & it & "_Closed ($1B claims removed)v4.xlsm]for R'!" & Range("C" & i)

Next i

Windows(st & "_" & it & "_Closed ($1B claims removed)v4.xlsm").Activate
ActiveWindow.Close

Windows("Combined output.xlsx").Activate
Range("B12").Select

End Sub

I will be adding the other parts once I get this down.

Also if anyone has a better idea on how to do this that would be great also. As far as I can tell the ranges will all be the same B12:C1962.

Thanks in advanced

Dave

 

[Norie]

Dave

Do you mean when you are trying to create references to named ranges here?

Set st = Range("State")
Set it = Range("InjType")

Or is it somewhere else in the code?

 

[dgrimm]

Sorry about that. The line(s) in question are range().formula =

 

[Sektor]

And what formula begins with "["? Is it sheet name? But sheet name can't use "[".

 

[Norie]

Dave

Are the 2 name ranges single cells?

If they are do they have the correct values in them so you get the correct filename?

 

[Andrew Poulsom]

Is it eg?

Range("B" & i).Formula = "='[" & st & "_" & it & "_Closed ($1B claims removed)v4.xlsm]for R'!B" & i

 

[dgrimm]

That is the file name of the file that is opened above, the I:\\..... I got this code by manually creating it in the cells and then transferring it over to vba.

 

[dgrimm]

To Norie yes they are single cell values and I would assume that they are the correct values because they opened the file. But when I use them in the .formula line is when the error occurs.

To Andrew, I am sorry but I don't know what you mean by "eg". And I made the change of taking out range("B" & i) at the end to just B" & i and it seems to be working. However it is taking a long time. It seems to be doing some calculation.

Would it be better to just do a range copy and paste values in the long run???

 

[Norie]

Dave

If you just want the values then copy and pasting should be quicker.

With that code you are creating almost 4000 formulas.

I was actually going to post code for just copy/paste but wasn't sure that's what you want.

Here's it is anyway, is assumes the workbook you want to copy to is the one the code is in.

Option Explicit
 
Sub ClosedClaims()
Dim wbDst As Range
Dim wbSrc As Range
Dim st As Range
Dim it As Range
Dim rngSrc As Range
Dim rngDst As Range
Dim strFileName As String
    
    Set wbDst = ThisWorkbook
    
    Set st = Range("State")
    Set it = Range("InjType")
 
    ' could probably separate out path/filename
    strFileName = "I:\WC\CVM\Exposure Curves Project\Distributions\Output\Closed\" & st & "_" & it & "_Closed ($1B claims removed)v4.xlsm"
    
    Set rngSrc = Workbooks.Open(Filename:=strFileName)
 
    rngSrc.Worksheets("for R").Range("B12:C1962").Copy
 
    rngDst.Range("B12").PasteSpecial xlPasteValues
 
    wbSrc.Close False
 
End Sub

 

[dgrimm]

Thank you for all the help everyone.....Got it up and running.

Dave