PDA

View Full Version : Solved: Method 'Range' of object '_Global' failed



lukecj
03-08-2012, 05:02 PM
I continue to receive this error when running the below code IF the activesheet is not Sheet6, but when I execute it with Sheet6 as the activesheet, it works. I know I am probably not defining "RecordNumber" correctly. Any ideas? Would greatly appreciate the help. I have been googling for an hour and a half. Thanks.

Sub MonsterProject()

Dim RecordNumber As Long

Application.ScreenUpdating = False

Sheet6.[infinity_mean].End(xlToLeft).Offset(0, 1).Value = Sheet2.[security].Value

RecordNumber = Sheet6.Range("ref_mean", Range("ref_mean").End(xlToRight)).Count - 1

Sheet6.[Paste_Mean].Offset(0, RecordNumber).Value = Sheet16.[Saved_Mean].Value
Sheet6.[paste_OneSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_OneSDev].Value
Sheet6.[paste_TwoSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_TwoSDev].Value
Sheet6.[paste_ThreeSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_ThreeSDev].Value
Sheet6.[paste_Scenario1].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario1].Value
Sheet6.[paste_Scenario2].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario2].Value
Sheet6.[paste_Scenario3].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario3].Value
Sheet6.[paste_Scenario4].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario4].Value
Sheet6.[paste_Scenario5].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario5].Value

Application.ScreenUpdating = True

End Sub

Bob Phillips
03-08-2012, 05:29 PM
Sub MonsterProject()

Dim RecordNumber As Long

Application.ScreenUpdating = False

With Sheet6

.[infinity_mean].End(xlToLeft).Offset(0, 1).Value = Sheet2.[security].Value

RecordNumber = .Range("ref_mean", .Range("ref_mean").End(xlToRight)).Count - 1

.[Paste_Mean].Offset(0, RecordNumber).Value = Sheet16.[Saved_Mean].Value
.[paste_OneSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_OneSDev].Value
.[paste_TwoSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_TwoSDev].Value
.[paste_ThreeSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_ThreeSDev].Value
.[paste_Scenario1].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario1].Value
.[paste_Scenario2].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario2].Value
.[paste_Scenario3].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario3].Value
.[paste_Scenario4].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario4].Value
.[paste_Scenario5].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario5].Value
End With

Application.ScreenUpdating = True

End Sub

lukecj
03-09-2012, 07:19 AM
Xld - you sir are a man among boys. Thanks once again for your help.

lukecj
03-09-2012, 08:22 AM
This is a really stupid question, but I have not been on the forum for a while and, for the life of me, I cannot figure out how to mark this thread as solved. help??????

Bob Phillips
03-09-2012, 08:36 AM
It is in Thread Tools at the top of the threads.

lukecj
03-09-2012, 08:40 AM
All I get are three options in Thread Tools:

Show Printable Version
Email this Page
Unsubscribe from this Thread

No mark as solved...

Bob Phillips
03-09-2012, 11:32 AM
As the instigator of the thread you should get it. I think there may be a problem on Chrome if you are using that, but I have administrator privileges so I did it for you.