ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Subscipt out of range (https://www.excelbanter.com/excel-programming/327396-subscipt-out-range.html)

Darrin Henry[_2_]
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 ***

Trevor Shuttleworth
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 ***



keepITcool
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 ***

Jim Thomlinson[_3_]
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 ***


All times are GMT +1. The time now is 07:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com