Compile Error

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

I am getting complie error - Duplicate error in current
scope

Can some one please tell me what is wrong? TIA.

Dim rng(0) As Range
Set rng(0) = sheets("Approval-Summary_PER").Range
("J53:K53")
Set rng(1) = sheets("Approval-Summary_PER").Range
("J54:k54")
Set rng(2) = sheets("Approval-Summary_PER").Range
("J55:k55")
Set rng(3) = sheets("Approval-Summary_PER").Range
("J56:k56")

Dim i As Integer
i = 5
For Each rng In rng
With rng
..Formula = "=HighLevelSummary_PER!$C$i"
End With
i = i + 1
Next
 
Hi Dipsy,

I believe your error is in your dimension statement.

It should read....

Dim rng(3) as range [you have allowed rng 0 thru 3 for a total of 4
ranges]

Regards,
Kevin
 
Without changing your basic coding. I had difficulty understanding
what you were doing.

Sub Test()

Dim i As Integer
Dim rngI As Variant
Dim rng(3) As Range

Set rng(0) = Sheets("Approval-Summary_PER").Range("J53:K53")
Set rng(1) = Sheets("Approval-Summary_PER").Range("J54:k54")
Set rng(2) = Sheets("Approval-Summary_PER").Range("J55:k55")
Set rng(3) = Sheets("Approval-Summary_PER").Range("J56:k56")

i = 5
For Each rngI In rng
With rngI
..Formula = "=HighLevelSummary_PER!$C$" & i
End With
i = i + 1
Next

End Sub


Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
 
Hi Kevin:

I fixed that. Somehow it was not accepting "rng".

With rng
..Formula = "=HighLevelSummary_PER!$C$i"
End With

Now I am having problem with the line -
..Formula = "=HighLevelSummary_PER!$C$i"

I want to use the variable i to increment the cells. It
gives me error - Run time error 1004 - Application
defined or object defined error.
-----Original Message-----
Hi Dipsy,

I believe your error is in your dimension statement.

It should read....

Dim rng(3) as range [you have allowed rng 0 thru 3 for a total of 4
ranges]

Regards,
Kevin


dipsy said:
I am getting complie error - Duplicate error in current
scope

Can some one please tell me what is wrong? TIA.

Dim rng(0) As Range
Set rng(0) = sheets("Approval-Summary_PER").Range
("J53:K53")
Set rng(1) = sheets("Approval-Summary_PER").Range
("J54:k54")
Set rng(2) = sheets("Approval-Summary_PER").Range
("J55:k55")
Set rng(3) = sheets("Approval-Summary_PER").Range
("J56:k56")

Dim i As Integer
i = 5
For Each rng In rng
With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With
i = i + 1
Next
Click to expand...


.
Click to expand...
 
Try something like

..Formula = "=HighLevelSummary_PER!$C$" & I


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



dipsy said:
Hi Kevin:

I fixed that. Somehow it was not accepting "rng".

With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With

Now I am having problem with the line -
.Formula = "=HighLevelSummary_PER!$C$i"

I want to use the variable i to increment the cells. It
gives me error - Run time error 1004 - Application
defined or object defined error.
-----Original Message-----
Hi Dipsy,

I believe your error is in your dimension statement.

It should read....

Dim rng(3) as range [you have allowed rng 0 thru 3 for a total of 4
ranges]

Regards,
Kevin


dipsy said:
I am getting complie error - Duplicate error in current
scope

Can some one please tell me what is wrong? TIA.

Dim rng(0) As Range
Set rng(0) = sheets("Approval-Summary_PER").Range
("J53:K53")
Set rng(1) = sheets("Approval-Summary_PER").Range
("J54:k54")
Set rng(2) = sheets("Approval-Summary_PER").Range
("J55:k55")
Set rng(3) = sheets("Approval-Summary_PER").Range
("J56:k56")

Dim i As Integer
i = 5
For Each rng In rng
With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With
i = i + 1
Next
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you so much - it worked. Regards.
-----Original Message-----
Without changing your basic coding. I had difficulty understanding
what you were doing.

Sub Test()

Dim i As Integer
Dim rngI As Variant
Dim rng(3) As Range

Set rng(0) = Sheets("Approval-Summary_PER").Range ("J53:K53")
Set rng(1) = Sheets("Approval-Summary_PER").Range ("J54:k54")
Set rng(2) = Sheets("Approval-Summary_PER").Range ("J55:k55")
Set rng(3) = Sheets("Approval-Summary_PER").Range ("J56:k56")

i = 5
For Each rngI In rng
With rngI
..Formula = "=HighLevelSummary_PER!$C$" & i
End With
i = i + 1
Next

End Sub


Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
---------------------------------------------------------- ----------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
---------------------------------------------------------- ----------------------------------------------------

.
Click to expand...
 
Chip:
Thanks for the help.

Why did u put 2 dots

...formula

Thanks.
-----Original Message-----
Try something like

..Formula = "=HighLevelSummary_PER!$C$" & I


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



dipsy said:
Hi Kevin:

I fixed that. Somehow it was not accepting "rng".

With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With

Now I am having problem with the line -
.Formula = "=HighLevelSummary_PER!$C$i"

I want to use the variable i to increment the cells. It
gives me error - Run time error 1004 - Application
defined or object defined error.
-----Original Message-----
Hi Dipsy,

I believe your error is in your dimension statement.

It should read....

Dim rng(3) as range [you have allowed rng 0 thru 3
Click to expand...
for
a total of 4
ranges]

Regards,
Kevin


I am getting complie error - Duplicate error in current
scope

Can some one please tell me what is wrong? TIA.

Dim rng(0) As Range
Set rng(0) = sheets("Approval-Summary_PER").Range
("J53:K53")
Set rng(1) = sheets("Approval-Summary_PER").Range
("J54:k54")
Set rng(2) = sheets("Approval-Summary_PER").Range
("J55:k55")
Set rng(3) = sheets("Approval-Summary_PER").Range
("J56:k56")

Dim i As Integer
i = 5
For Each rng In rng
With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With
i = i + 1
Next




.
Click to expand...
Click to expand...


.
Click to expand...
 
The two dots are a typo. There should only be one dot.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

dipsy said:
Chip:
Thanks for the help.

Why did u put 2 dots

..formula

Thanks.
-----Original Message-----
Try something like

..Formula = "=HighLevelSummary_PER!$C$" & I


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)



dipsy said:
Hi Kevin:

I fixed that. Somehow it was not accepting "rng".

With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With

Now I am having problem with the line -
.Formula = "=HighLevelSummary_PER!$C$i"

I want to use the variable i to increment the cells. It
gives me error - Run time error 1004 - Application
defined or object defined error.

-----Original Message-----
Hi Dipsy,

I believe your error is in your dimension statement.

It should read....

Dim rng(3) as range [you have allowed rng 0 thru 3 for
a total of 4
ranges]

Regards,
Kevin


I am getting complie error - Duplicate error in current
scope

Can some one please tell me what is wrong? TIA.

Dim rng(0) As Range
Set rng(0) = sheets("Approval-Summary_PER").Range
("J53:K53")
Set rng(1) = sheets("Approval-Summary_PER").Range
("J54:k54")
Set rng(2) = sheets("Approval-Summary_PER").Range
("J55:k55")
Set rng(3) = sheets("Approval-Summary_PER").Range
("J56:k56")

Dim i As Integer
i = 5
For Each rng In rng
With rng
.Formula = "=HighLevelSummary_PER!$C$i"
End With
i = i + 1
Next




.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top