Object variable not set

[rohan8510]

hi, i keep getting object variable not set in the below code then i have to close the workbook and reopen again and then it works i have tried Dim rngcopy As Range and Dim rngPaste As Range but it did not work


Do i have to go to tools - references and select any option i see that visual basic for applications is already selected there is also another option vbaproject but iam not able to select that because it says "cannot select as that already exist" or do i have to change something in code? error pops at


Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)


many thanks








Sub test()






Dim fname As String
Dim fpath As String
Dim owb As Workbook
Dim rngcopy As Range
Dim rngPaste As Range



fname = "DummyIACB.xlsm"
fpath = "J:\HSS\Trad NAV-GAV\BAU Ops\IMS Asia\Valuation Packs\SNG\Blackrock\Working\CLIENT COPY TEMPLATES"
Set owb = Application.Workbooks.Open(fpath & "\" & fname)




Windows ("CNAV IACB "& Format(Sheets("Main").Range("C6").Value, "yyyymmdd" & ".xls").Activate)




Set rngcopy = Sheets("Raw data").Range("A1:IV1").Find("Accrual excl XD+3", LookIn:=xlValues)
Set rngcopy = Range(rngcopy.Offset(1, 0), Cells(Rows.Count, rngcopy.Column).End(xlUp))
Set rngPaste = Workbooks("DummyIACB.xlsm").Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats

End Sub

[Aflatoon]

It probably means your Find is failing - you should always test if the range is Nothing before using it. Personally, I'd use Match here:


Sub test()

Dim fname As String
Dim fpath As String
Dim owb As Workbook
Dim wbActive As Workbook
Dim vMatch
Dim rngcopy As Range
Dim rngPaste As Range


fname = "DummyIACB.xlsm"
fpath = "J:\HSS\Trad NAV-GAV\BAU Ops\IMS Asia\Valuation Packs\SNG\Blackrock\Working\CLIENT COPY TEMPLATES"


Set wbActive = ActiveWorkbook


Set owb = Application.Workbooks.Open(fpath & "\" & fname)


With wbActive.Sheets("Raw data")
vMatch = Application.Match("Accrual excl XD+3", .Range("A1:IV1"), 0)


If Not IsError(vMatch) Then
Set rngcopy = .Range(.Cells(2, vMatch), .Cells(.Rows.Count, vMatch).End(xlUp))
Set rngPaste = owb.Sheets("Sheet1").Range("l2")
rngcopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
End If
End With
End Sub

[rohan8510]

Thank you so much this works great :) thanks a lot :)