Subscript out of range - multi array

[Buffyslay]

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

 

[Bob Phillips]

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]

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

 

[Buffyslay]

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

 

[Buffyslay]

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

 

[Tushar Mehta]

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

 

[Bob Phillips]

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)