Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Subscipt out of range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default Subscipt out of range
Hello,
Any ideas why this code gives a subscript out of range error, on the
first range, D11?

Private Sub DoneAddresses_Click()
Dim BillAgency, BillToName, Bill1, Bill2, BillCity, BillState, BillZip,
Phone1, Phone2, Phone3, Email1 As String
BillAgency = BillAgency1
BillToName = BillName
Bill1 = BillingAddress1
Bill2 = BillingAddress2
BillCity = City
BillState = State
BillZip = ZipCode
Phone1 = Tel1
Phone2 = Tel2
Phone3 = Tel3
Email1 = EmailAddy

Worksheets("GEMFEDCCOrderForm").Range("D11") = BillAgency1
Worksheets("GEMFEDCCOrderForm").Range("D12") = BillName
Worksheets("GEMFEDCCOrderForm").Range("D13") = BillingAddress1
Worksheets("GEMFEDCCOrderForm").Range("D14") = BillingAddress2
Worksheets("GEMFEDCCOrderForm").Range("D15") = BillCity & " " &
BillState & ", " & BillZip
Worksheets("GEMFEDCCOrderForm").Range("D16") = "(" & Phone1 & ") " &
Phone2 & "-" & Phone3
Worksheets("GEMFEDCCOrderForm").Range("D17") = Email1

If BillShipSame.Value = False Then
Dim ShipAgency, ShipToName, Ship1, Ship2, ShipCity, ShipState, ShipZip,
ShipTele1, ShipTele2, ShipTele3, ShipMail As String
ShipAgency = ShipAgency1
ShipToName = ShipName
Ship1 = ShipAddy1
Ship2 = ShipAddy2
ShipCity = City1
ShipState = State1
ShipZip = ZipCode1
ShipTele1 = ShipTel1
ShipTel2 = ShipTel2
ShipTel3 = ShipTel3
ShipEmail1 = ShipMail

Worksheets("GEMFEDCCOrderForm").Range("H11") = ShipAgency
Worksheets("GEMFEDCCOrderForm").Range("H12") = ShipToName
Worksheets("GEMFEDCCOrderForm").Range("H13") = Ship1
Worksheets("GEMFEDCCOrderForm").Range("H14") = Ship2
Worksheets("GEMFEDCCOrderForm").Range("H15") = ShipCity & " " &
ShipState & ", " & ShipZip
Worksheets("GEMFEDCCOrderForm").Range("H16") = "(" & ShipTele1 & ") " &
ShipTele2 & "-" & ShipTele3
Worksheets("GEMFEDCCOrderForm").Range("H17") = ShipMail1

End If
End Sub

This is part of a command button on a user form. All the variables point
to textboxes on the form. Any other info needed just let me know.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,089
Default Subscipt out of range
Typically, this would mean that the sheet name does not exist. Check the
spelling and look for spaces on the actual sheet tab.

Regards

Trevor


"Darrin Henry" wrote in message
...
Hello,
Any ideas why this code gives a subscript out of range error, on the
first range, D11?

Private Sub DoneAddresses_Click()
Dim BillAgency, BillToName, Bill1, Bill2, BillCity, BillState, BillZip,
Phone1, Phone2, Phone3, Email1 As String
BillAgency = BillAgency1
BillToName = BillName
Bill1 = BillingAddress1
Bill2 = BillingAddress2
BillCity = City
BillState = State
BillZip = ZipCode
Phone1 = Tel1
Phone2 = Tel2
Phone3 = Tel3
Email1 = EmailAddy

Worksheets("GEMFEDCCOrderForm").Range("D11") = BillAgency1
Worksheets("GEMFEDCCOrderForm").Range("D12") = BillName
Worksheets("GEMFEDCCOrderForm").Range("D13") = BillingAddress1
Worksheets("GEMFEDCCOrderForm").Range("D14") = BillingAddress2
Worksheets("GEMFEDCCOrderForm").Range("D15") = BillCity & " " &
BillState & ", " & BillZip
Worksheets("GEMFEDCCOrderForm").Range("D16") = "(" & Phone1 & ") " &
Phone2 & "-" & Phone3
Worksheets("GEMFEDCCOrderForm").Range("D17") = Email1

If BillShipSame.Value = False Then
Dim ShipAgency, ShipToName, Ship1, Ship2, ShipCity, ShipState, ShipZip,
ShipTele1, ShipTele2, ShipTele3, ShipMail As String
ShipAgency = ShipAgency1
ShipToName = ShipName
Ship1 = ShipAddy1
Ship2 = ShipAddy2
ShipCity = City1
ShipState = State1
ShipZip = ZipCode1
ShipTele1 = ShipTel1
ShipTel2 = ShipTel2
ShipTel3 = ShipTel3
ShipEmail1 = ShipMail

Worksheets("GEMFEDCCOrderForm").Range("H11") = ShipAgency
Worksheets("GEMFEDCCOrderForm").Range("H12") = ShipToName
Worksheets("GEMFEDCCOrderForm").Range("H13") = Ship1
Worksheets("GEMFEDCCOrderForm").Range("H14") = Ship2
Worksheets("GEMFEDCCOrderForm").Range("H15") = ShipCity & " " &
ShipState & ", " & ShipZip
Worksheets("GEMFEDCCOrderForm").Range("H16") = "(" & ShipTele1 & ") " &
ShipTele2 & "-" & ShipTele3
Worksheets("GEMFEDCCOrderForm").Range("H17") = ShipMail1

End If
End Sub

This is part of a command button on a user form. All the variables point
to textboxes on the form. Any other info needed just let me know.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default Subscipt out of range
well: first thing

you do not reference a workbook..., there for you are working with the
activeworkbook.

the ACTIVEWORKBOOK must contain a worksheet called:
GEMFetc

Next:
There's no need to use intermediate variables
you can assign directyl from the control

Activeworkbook.Worksheets("Gemetc").Range("d11") = me.BillAgency1.Value

Next:
The way you dim your variables on 1 line:
Dim a,b,c as string
will create a as variant, b as variant and only c as string.

Next when you are working with the same sheet
it may be a good idea to either use a worksheet variable
or a with /end with or both!

dim wks as worksheet
on error resume next
set wks = activeworkbook.worksheets("GEMetc")
on error goto 0
if wks is nothing then
msgbox "OOPS, pls activate the correct workbook"
exit sub
end if

with wks
.cells("d11") = me.BillAgency1
.cells("d12") = me.BillName

end with

hth

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Darrin Henry wrote :

Hello,
Any ideas why this code gives a subscript out of range error, on the
first range, D11?

Private Sub DoneAddresses_Click()
Dim BillAgency, BillToName, Bill1, Bill2, BillCity, BillState,
BillZip, Phone1, Phone2, Phone3, Email1 As String
BillAgency = BillAgency1
BillToName = BillName
Bill1 = BillingAddress1
Bill2 = BillingAddress2
BillCity = City
BillState = State
BillZip = ZipCode
Phone1 = Tel1
Phone2 = Tel2
Phone3 = Tel3
Email1 = EmailAddy

Worksheets("GEMFEDCCOrderForm").Range("D11") = BillAgency1
Worksheets("GEMFEDCCOrderForm").Range("D12") = BillName
Worksheets("GEMFEDCCOrderForm").Range("D13") = BillingAddress1
Worksheets("GEMFEDCCOrderForm").Range("D14") = BillingAddress2
Worksheets("GEMFEDCCOrderForm").Range("D15") = BillCity & " " &
BillState & ", " & BillZip
Worksheets("GEMFEDCCOrderForm").Range("D16") = "(" & Phone1 & ") " &
Phone2 & "-" & Phone3
Worksheets("GEMFEDCCOrderForm").Range("D17") = Email1

If BillShipSame.Value = False Then
Dim ShipAgency, ShipToName, Ship1, Ship2, ShipCity, ShipState,
ShipZip, ShipTele1, ShipTele2, ShipTele3, ShipMail As String
ShipAgency = ShipAgency1
ShipToName = ShipName
Ship1 = ShipAddy1
Ship2 = ShipAddy2
ShipCity = City1
ShipState = State1
ShipZip = ZipCode1
ShipTele1 = ShipTel1
ShipTel2 = ShipTel2
ShipTel3 = ShipTel3
ShipEmail1 = ShipMail

Worksheets("GEMFEDCCOrderForm").Range("H11") = ShipAgency
Worksheets("GEMFEDCCOrderForm").Range("H12") = ShipToName
Worksheets("GEMFEDCCOrderForm").Range("H13") = Ship1
Worksheets("GEMFEDCCOrderForm").Range("H14") = Ship2
Worksheets("GEMFEDCCOrderForm").Range("H15") = ShipCity & " " &
ShipState & ", " & ShipZip
Worksheets("GEMFEDCCOrderForm").Range("H16") = "(" & ShipTele1 & ") "
& ShipTele2 & "-" & ShipTele3
Worksheets("GEMFEDCCOrderForm").Range("H17") = ShipMail1

End If
End Sub

This is part of a command button on a user form. All the variables
point to textboxes on the form. Any other info needed just let me
know. Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 983
Default Subscipt out of range
Now that is a thourough reply. The only thing I would add is to make sure
that the sheet is visible and unprotected...

"keepITcool" wrote:

well: first thing

you do not reference a workbook..., there for you are working with the
activeworkbook.

the ACTIVEWORKBOOK must contain a worksheet called:
GEMFetc

Next:
There's no need to use intermediate variables
you can assign directyl from the control

Activeworkbook.Worksheets("Gemetc").Range("d11") = me.BillAgency1.Value

Next:
The way you dim your variables on 1 line:
Dim a,b,c as string
will create a as variant, b as variant and only c as string.

Next when you are working with the same sheet
it may be a good idea to either use a worksheet variable
or a with /end with or both!

dim wks as worksheet
on error resume next
set wks = activeworkbook.worksheets("GEMetc")
on error goto 0
if wks is nothing then
msgbox "OOPS, pls activate the correct workbook"
exit sub
end if

with wks
.cells("d11") = me.BillAgency1
.cells("d12") = me.BillName

end with

hth

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Darrin Henry wrote :

Hello,
Any ideas why this code gives a subscript out of range error, on the
first range, D11?

Private Sub DoneAddresses_Click()
Dim BillAgency, BillToName, Bill1, Bill2, BillCity, BillState,
BillZip, Phone1, Phone2, Phone3, Email1 As String
BillAgency = BillAgency1
BillToName = BillName
Bill1 = BillingAddress1
Bill2 = BillingAddress2
BillCity = City
BillState = State
BillZip = ZipCode
Phone1 = Tel1
Phone2 = Tel2
Phone3 = Tel3
Email1 = EmailAddy

Worksheets("GEMFEDCCOrderForm").Range("D11") = BillAgency1
Worksheets("GEMFEDCCOrderForm").Range("D12") = BillName
Worksheets("GEMFEDCCOrderForm").Range("D13") = BillingAddress1
Worksheets("GEMFEDCCOrderForm").Range("D14") = BillingAddress2
Worksheets("GEMFEDCCOrderForm").Range("D15") = BillCity & " " &
BillState & ", " & BillZip
Worksheets("GEMFEDCCOrderForm").Range("D16") = "(" & Phone1 & ") " &
Phone2 & "-" & Phone3
Worksheets("GEMFEDCCOrderForm").Range("D17") = Email1

If BillShipSame.Value = False Then
Dim ShipAgency, ShipToName, Ship1, Ship2, ShipCity, ShipState,
ShipZip, ShipTele1, ShipTele2, ShipTele3, ShipMail As String
ShipAgency = ShipAgency1
ShipToName = ShipName
Ship1 = ShipAddy1
Ship2 = ShipAddy2
ShipCity = City1
ShipState = State1
ShipZip = ZipCode1
ShipTele1 = ShipTel1
ShipTel2 = ShipTel2
ShipTel3 = ShipTel3
ShipEmail1 = ShipMail

Worksheets("GEMFEDCCOrderForm").Range("H11") = ShipAgency
Worksheets("GEMFEDCCOrderForm").Range("H12") = ShipToName
Worksheets("GEMFEDCCOrderForm").Range("H13") = Ship1
Worksheets("GEMFEDCCOrderForm").Range("H14") = Ship2
Worksheets("GEMFEDCCOrderForm").Range("H15") = ShipCity & " " &
ShipState & ", " & ShipZip
Worksheets("GEMFEDCCOrderForm").Range("H16") = "(" & ShipTele1 & ") "
& ShipTele2 & "-" & ShipTele3
Worksheets("GEMFEDCCOrderForm").Range("H17") = ShipMail1

End If
End Sub

This is part of a command button on a user form. All the variables
point to textboxes on the form. Any other info needed just let me
know. Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I enter formula sum(range+range)*0.15 sumif(range=3) tkw Excel Discussion (Misc queries) 2 October 1st 09 09:17 PM
Excel Addin:Setting the range to the Excel.Range object range prop Rp007 Excel Worksheet Functions 5 November 24th 06 04:30 PM
Range.Find returns cell outside of range when range set to single cell Frank Jones Excel Programming 12 June 10th 04 04:22 AM
Subscipt out of Range Jahson Excel Programming 5 February 9th 04 06:22 PM
how to? set my range= my UDF argument (range vs. value in range) [advanced?] Keith R[_3_] Excel Programming 2 August 11th 03 05:55 PM


All times are GMT +1. The time now is 05:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement