PDA

View Full Version : Solved: Run-time Error '1004'



tammyl
12-03-2008, 04:17 PM
Hi,
I'm receiving a Run-time error '1004':
Method 'Range' of object '_Global' failed.

I'm sure it's really simple to solve, I just 'can't see the wood thru the trees' today.

The error is displayed when code gets to the "Set CCalRng...'line and the code is highlighted yellow.

Cheers
Tammy :sick:



Dim c As Range, Legend As Range, CCalRng As Range
Dim x As Object
Dim ws As Worksheet ' Variable for the Date Data Sheet
Dim iRow As Integer, iLRow As Integer ' Variable for the last row
Dim rcColor As Long 'BtnClick As Integer


Application.ScreenUpdating = False
Set ws = Sheets("AnnualA4")
ws.Activate
If Range("cColor").Value = 1 Then
iLRow = [Z65536].End(xlUp).Row ' Last Row
sdd = 26 ' Column Index for Start Date
edd = 28 ' Column Index for End Date
ddd = 27 ' Column Index for Duration time
idd = 26 ' Interium start date column index
ElseIf Range("cColor").Value = 2 Then
iLRow = [AD65536].End(xlUp).Row
sdd = 30 ' Column Index for Start Date
edd = 32 ' Column Index for End Date
ddd = 31 ' Column Index for Duration time
idd = 30 ' Interium start date column index
End If
Sheets(Array("CCFront", "CCBack")).Select
rcColor = Range("cColor").Interior.ColorIndex
Range("A5").Select
' Select and Activate each sheet in the array
For Each x In ActiveWindow.SelectedSheets
x.Activate
' Loop through each cell in the selected sheet
' and test for date condition

' Loop through each cell in the selected sheet and test for date condition
For iRow = 4 To iLRow
StDate = ws.Cells(iRow, sdd)
EnDate = ws.Cells(iRow, edd)
Dur = ws.Cells(iRow, ddd)
IntDate = ws.Cells(iRow, idd)
IntNew = IntDate + Dur

Set CCalRng = Range("A5:G10,I5:O10,Q5:W10,AA5:AG10,AI5:AO10,AQ5:AW10," & _
"A14:G19,I14:O19,Q14:W19,AA14:AG19,AI14:AO19,AQ14:AW19," & _
"A27:G32,I27:O32,Q27:W32,AA27:AG32,AI27:AO32,AQ27:AW32," & _
"A36:G41,I36:O41,Q36:W41,AA36:AG41,AI36:AO41,AQ36:AW41," & _
"A49:G54,I49:O54,Q49:W54,AA49:AG54,AI49:AO54,AQ49:AW54," & _
"A58:G63,I58:O63,Q58:W63,AA58:AG63,AI58:AO63,AQ58:AW63," & _
"A71:G76,I71:O76,Q71:W76,AA71:AG76,AI71:AO76,AQ71:AW76," & _
"A80:G85,I80:O85,Q80:W85,AA80:AG85,AI80:AO85,AQ80:AW85")

For Each c In CCalRng
c.Select

Kieran
12-03-2008, 08:10 PM
Try this. (It seems taht the original syntax didn't like the line breaks.


Set ccalrng = Application.Union(Range("A5:G10,I5:O10,Q5:W10,AA5:AG10,AI5:AO10,AQ5:AW10"), _
Range("A14:G19,I14:O19,Q14:W19,AA14:AG19,AI14:AO19,AQ14:AW19"), _
Range("A27:G32,I27:O32,Q27:W32,AA27:AG32,AI27:AO32,AQ27:AW32"), _
Range("A36:G41,I36:O41,Q36:W41,AA36:AG41,AI36:AO41,AQ36:AW41"), _
Range("A49:G54,I49:O54,Q49:W54,AA49:AG54,AI49:AO54,AQ49:AW54"), _
Range("A58:G63,I58:O63,Q58:W63,AA58:AG63,AI58:AO63,AQ58:AW63"), _
Range("A71:G76,I71:O76,Q71:W76,AA71:AG76,AI71:AO76,AQ71:AW76"), _
Range("A80:G85,I80:O85,Q80:W85,AA80:AG85,AI80:AO85,AQ80:AW85"))

tammyl
12-03-2008, 08:55 PM
Thanks very much Kieran, error resolved.
One down, a few more to go!! :)