Subscript out of range - multi array

i keep getting an subscript out of range error here
[lrowcountref is active rows, start in e2 where the data starts, hence
2 to rowcount]


ReDim arrEmailAdd(2 To lRowCountRef, 2 To lRowCountRef)

Range("E2").Select
For z = 2 To lRowCountRef ********here is the error*********
For x = 2 To lRowCountRef
arrEmailAdd(z) = ActiveCell.Value
arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value
ActiveCell.Offset(1, 0).Select
Next
Next

Your problem is that you have defined a 2D array, but you are trying to load
it as if it were a single dimension.

I am struggling to see what you are trying to do, it seems that you want to
load every email address into the second dimension every time. What are you
really trying to do?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Buffyslay" <[email protected]> wrote in message
news:[email protected]...
> i keep getting an subscript out of range error here
> [lrowcountref is active rows, start in e2 where the data starts, hence
> 2 to rowcount]
>
>
> ReDim arrEmailAdd(2 To lRowCountRef, 2 To lRowCountRef)
>
> Range("E2").Select
> For z = 2 To lRowCountRef ********here is the error*********
> For x = 2 To lRowCountRef
> arrEmailAdd(z) = ActiveCell.Value
> arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value
> ActiveCell.Offset(1, 0).Select
> Next
> Next
>

hi there

want i want to do is have a 2 d array - one Name and one Email
address....

i am pulling my hair out and having a real tough time

what i am trying to do is load
arrEmailAdd(emailaddress,emailname)
arrEmailAdd(z,x)



Range("E2").Select
For z = 2 To lRowCountRef ********here is the error*********
For x = 2 To lRowCountRef
arrEmailAdd(z) = ActiveCell.Value
arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value
ActiveCell.Offset(1, 0).Select
Next
Next

what i am trying to do is load
arrEmailAdd(emailaddress,emailname)
arrEmailAdd(z,x)



Range("E2").Select
For z = 2 To lRowCountRef ********here is the error*********
For x = 2 To lRowCountRef
arrEmailAdd(z) = ActiveCell.Value
arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value
ActiveCell.Offset(1, 0).Select
Next
Next

You are much better off defining a user-defined type that reflects the data
structure you really want.

Option Explicit
Option Base 0
Type NameAndEmail
CustomerName As String
CustomerEmail As String
End Type
Function UDTforEmailAddresses(lRowCountRef As Long) As NameAndEmail()
Dim EmailAddresses() As NameAndEmail, _
I As Long

With Range("E2")
ReDim EmailAddresses(lRowCountRef - .Row)
For I = 0 To UBound(EmailAddresses)
EmailAddresses(I).CustomerEmail = .Offset(I, 0).Value
EmailAddresses(I).CustomerName = .Offset(I, -3).Value
Next I
End With
UDTforEmailAddresses = EmailAddresses
End Function
Sub testIt()
Dim Rslt() As NameAndEmail
Rslt = UDTforEmailAddresses(8)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> hi there
>
> want i want to do is have a 2 d array - one Name and one Email
> address....
>
> i am pulling my hair out and having a real tough time
>
>

Try this then

ReDim arrEmailAdd(2 To lRowCountRef, 1 To 2)

Range("E2").Select
For z = 2 To lRowCountRef
arrEmailAdd(z, 1) = Cells(z, "E").Value
arrEmailAdd(z, 2) = Cells(z, "B").Value
Next



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Buffyslay" <[email protected]> wrote in message
news:[email protected]...
> hi there
>
> want i want to do is have a 2 d array - one Name and one Email
> address....
>
> i am pulling my hair out and having a real tough time
>