PDA

View Full Version : dynamically created array



nasomi
03-14-2011, 11:19 AM
I have 5 sheets each of which have a different number of populated cells. i wish to create an array witht he contents of each cell. this is what i'm doing:

dim arr() as string
for a = 1 to sheets.count
sheets(a).select
range("A1").select
for b = 1 to activesheet.usedrange.rows.count
arr(b) = activecell.value
activecell.offset(1,0).select
next b
...
However it's giving me a strange error of subscript out of range on the arr(b) portion.

Any suggestions?

Thanks.

nasomi
03-14-2011, 11:55 AM
I should say that the cells values contain email addresses.

When I mouse over it says "arr(b) = <Subscript out of range>"
When I mouse over (b) it says b = 1
When I mouse over activecell.value it says "activecell.value = email@address" of what's in the cell it's currently on.

Kenneth Hobs
03-14-2011, 12:02 PM
UsedRange will be two dimensional. If you are going to poke the values into an array, you need to know the size of the array. I guess you could poke all the data into a one dimensional array. You would need to use Redim. Poking all the sheets data into a two dimensional array will be tricky if you don't dimension it properly.

Using Select is not very efficient.

If you attach an example xls, it would be easier to help.

nasomi
03-14-2011, 12:08 PM
the 5 sheets are customer names, and the sheets contain people's email addresses that it needs to go to. Sometimes there, 2 email addresses, sometimes 15. The macro is automatically called upon the completion of a query against the mainframe where it spits out the data to a specified sheet and runs the macro.

Sub saveas()
Set objOutlook = CreateObject("Outlook.Application")
a = InputBox("Customer?")
ActiveWorkbook.saveas Filename:="\\vlfcpsvr01\corpb\SalesOps\Biz Analysis\Recall\" & a & " " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls", FileFormat:=xlExcel8
Application.Workbooks.Open ("\\vlfcpsvr01\corpb\SalesOps\Biz Analysis\Recall\names.xlsx")
For b = 1 To Sheets.Count
Sheets(b).Select
'MsgBox a & " " & ActiveSheet.Name
If Left(ActiveSheet.Name, Len(b) - 1) = Left(b, Len(b) - 1) Then
'MsgBox "Match!"
d = ActiveSheet.UsedRange.Rows.Count
Dim c As Integer
Dim arr() As String
For c = 1 To ActiveSheet.UsedRange.Rows.Count
arr(c) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Next c
End If
Next b
For e = 1 To d
Set objmailmessage = objOutlook.createitem(0)
With objmailmessage
.To = arr(e)
.Subject = a & " Recall Information"
.display
End With
Next e

End Sub