Subscript out of range error while populating array from range.

[Aussiexile]

hi i was wondering if anyone could tell me why im getting a "Subscript Out of Range" error on this code??

    If Not Intersect(Target, Range("O:O")) Is Nothing Then
        For Each rngCell In Intersect(Target, Range("O:O"))
            If rngCell.row <> 1 Then
                Dim discArray As Variant
                discArray = Sheets("discSelection").Range("A1:A2")
                Range("AD" & rngCell.row).Value = discArray(1)
            End If
        Next rngCell
     End If

this code is located in the Worksheet OnChange function of Sheet 2 "Corro"

"discSelection" is sheet 16

any help would be much appreciated

[MarvinP]

What does discArray(0) equal?

Arrays are always zero based.

[Aussiexile]

sorry i forgot i changed the range the range is actuallt A1:A31 and contains this list

1 - Client
A - Accounting & Finance
B - Procurement
C - Project Controls
D - Community Relations
EA - Architectural
EC - Civil
EE - Electrical
EG - Engineering Management
EH - Environmental
EJ - Control Systems
EK - Geotech & Hydraulic Engineering Services
EM - Mechanical
EN - Materials Engineering Technology)
EP - Plant Design & Piping
ER - Pipeline & Marine
ES - Structural
EU - Loss Prevention
EV - Process
EZ - Project Engineering
F - Project Management
H - Safety & Health
K - Administration
M - Management
P - Information Systems & Technology
PR - Public Relations
Q - Quality Assurance
S - Safety
TF - Construction
U - Startup
V - Contracts

[mikerickson]

DiscArray is a 2 dimensional array, 2 rows X 1 column.

Try

Range("AD" & rngCell.row).Value = discArray(1, 1)

Or without looping

With Range("O:O")
   If Not Intersect(Target,Range(.Cells(2,1), .Cells(.Rows.Count,1).End(xlup)) Is Nothing Then

         Intersect(Target,Range(.Cells(2,1), .Cells(.Rows.Count,1).End(xlup)).EntireRow.Columns(30).Value _
            = Sheets("discSelection").Range("A1")

   End If
End With

[MarvinP]

Hi,

Here is how you need to do it at: http://support.microsoft.com/kb/213798
The second example down.

[Aussiexile]

Your both brilliant.

Thank you both for you help it is very much appreciated