Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range Q
I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but
I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6 My code is as follows, any help appreciated Sub Mail_Reports() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant strdate = Format(Now, "dd-mm-yy h.mm") Application.ScreenUpdating = False Sheets("E-Mys Shop").Visible = True Sheets("E-Mys Shop").Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name _ & " Sent on" & " " & strdate & "" MyArr = Sheets("Home").Range("AA6:AB6") .SendMail MyArr, Sheets("Home").Range("X15").Value .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True Sheets("E-Mys Shop").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Home").Select Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range Q
I do actually Nick, only a amateur user of macros however, when I go into
debug and 'float' over MyArr it shows 'MyArr=Empty' "Nick Hodge" wrote in message ... John My guess is you don't have a sheet called home. Even if you had nothing in the cells the array will still fill with empty elements -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "John" wrote in message ... I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6 My code is as follows, any help appreciated Sub Mail_Reports() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant strdate = Format(Now, "dd-mm-yy h.mm") Application.ScreenUpdating = False Sheets("E-Mys Shop").Visible = True Sheets("E-Mys Shop").Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name _ & " Sent on" & " " & strdate & "" MyArr = Sheets("Home").Range("AA6:AB6") .SendMail MyArr, Sheets("Home").Range("X15").Value .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True Sheets("E-Mys Shop").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Home").Select Range("A1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range Q
John
I shortened it to just two lines when I tested it Sub Mail_Reports() Dim MyArr As Variant MyArr = Sheets("Home").Range("AA6:AB6") End Sub Worked fine for me -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "John" wrote in message ... I do actually Nick, only a amateur user of macros however, when I go into debug and 'float' over MyArr it shows 'MyArr=Empty' "Nick Hodge" wrote in message ... John My guess is you don't have a sheet called home. Even if you had nothing in the cells the array will still fill with empty elements -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "John" wrote in message ... I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6 My code is as follows, any help appreciated Sub Mail_Reports() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant strdate = Format(Now, "dd-mm-yy h.mm") Application.ScreenUpdating = False Sheets("E-Mys Shop").Visible = True Sheets("E-Mys Shop").Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name _ & " Sent on" & " " & strdate & "" MyArr = Sheets("Home").Range("AA6:AB6") .SendMail MyArr, Sheets("Home").Range("X15").Value .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True Sheets("E-Mys Shop").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Home").Select Range("A1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range Q
Hi John,
John wrote: I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6 The only thing I can think of is that you've got some mixed references to your workbooks. Sheets("E-Mys Shop").Copy Set wb = ActiveWorkbook Here, you set wb up to be a reference to the new workbook (containing only 1 sheet - "E-Mys Shop"). With wb .SaveAs ThisWorkbook.Name _ & " Sent on" & " " & strdate & "" Here, you are saving the new workbook. MyArr = Sheets("Home").Range("AA6:AB6") Assuming the executing code does not reside in a worksheet module, this code actually references the new workbook, which probably doesn't have a "Home" worksheet in it. Not sure why it's not raising a runtime error here, unless you are using On Error Resume Next. Even if this isn't an accurate assessment, you may want to set a reference to your original workbook. Then, use the fully-qualified references when referring to workbook and worksheet-level objects. That way, you remove all ambiguity and possibility of error relating to unqualified references. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range Q
Jake you have cracked it, MyArr = Sheets("Home").Range("AA6:AB6") - this
code references the new workbook, not the workbook where "Home" is located, so by setting a ref to original workbook, it worked Thanks all for taking the time to advise "Jake Marx" wrote in message ... Hi John, John wrote: I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6 The only thing I can think of is that you've got some mixed references to your workbooks. Sheets("E-Mys Shop").Copy Set wb = ActiveWorkbook Here, you set wb up to be a reference to the new workbook (containing only 1 sheet - "E-Mys Shop"). With wb .SaveAs ThisWorkbook.Name _ & " Sent on" & " " & strdate & "" Here, you are saving the new workbook. MyArr = Sheets("Home").Range("AA6:AB6") Assuming the executing code does not reside in a worksheet module, this code actually references the new workbook, which probably doesn't have a "Home" worksheet in it. Not sure why it's not raising a runtime error here, unless you are using On Error Resume Next. Even if this isn't an accurate assessment, you may want to set a reference to your original workbook. Then, use the fully-qualified references when referring to workbook and worksheet-level objects. That way, you remove all ambiguity and possibility of error relating to unqualified references. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript Out of Range | Excel Programming | |||
Subscript out of range | Excel Programming | |||
Subscript out of range? | Excel Programming | |||
Subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming |