Run-Time error '9' Subscript out of range
I tried to look everywhere for this solution but coulnd't find it.
I am trying to build a userform to input a new row (first row after headers).
The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.
When I run the scrip it gets stuck at this line: Set ssheet = ThisWorkbook.Sheets("Sheet1")
Please find below my code:
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Sheet1")
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
ssheet.Cells(nr, 1) = Me.tbNAME
ssheet.Cells(nr, 2) = Me.cmbStatus
ssheet.Cells(nr, 3) = Me.cmbFunds
ssheet.Cells(nr, 4) = Me.cmbDD
ssheet.Cells(nr, 7) = Me.cmbDistributor
ssheet.Cells(nr, 8) = Me.tbComments
End Sub
Private Sub UserForm_Initialize()
Me.tbDate = Date
For Each blah In [StatusList]
Me.cmbStatus.AddItem blah
Next blah
For Each blah In [FundsList]
Me.cmbFunds.AddItem blah
Next blah
For Each blah In [DDList]
Me.cmbDD.AddItem blah
Next blah
For Each blah In [DistributorList]
Me.cmbDistributor.AddItem blah
Next blah
End Sub
It would be great if you could help me to sort this out, seems so easy to add 6 fields for the rows but then it gets stuck.
Thank you!,
Alex
excel vba excel-vba compiler-errors subscript
edited Nov 21 '18 at 16:19
SJR
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
add a comment |
I tried to look everywhere for this solution but coulnd't find it.
I am trying to build a userform to input a new row (first row after headers).
The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.
When I run the scrip it gets stuck at this line: Set ssheet = ThisWorkbook.Sheets("Sheet1")
Please find below my code:
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Sheet1")
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
ssheet.Cells(nr, 1) = Me.tbNAME
ssheet.Cells(nr, 2) = Me.cmbStatus
ssheet.Cells(nr, 3) = Me.cmbFunds
ssheet.Cells(nr, 4) = Me.cmbDD
ssheet.Cells(nr, 7) = Me.cmbDistributor
ssheet.Cells(nr, 8) = Me.tbComments
End Sub
Private Sub UserForm_Initialize()
Me.tbDate = Date
For Each blah In [StatusList]
Me.cmbStatus.AddItem blah
Next blah
For Each blah In [FundsList]
Me.cmbFunds.AddItem blah
Next blah
For Each blah In [DDList]
Me.cmbDD.AddItem blah
Next blah
For Each blah In [DistributorList]
Me.cmbDistributor.AddItem blah
Next blah
End Sub
It would be great if you could help me to sort this out, seems so easy to add 6 fields for the rows but then it gets stuck.
Thank you!,
Alex
excel vba excel-vba compiler-errors subscript
edited Nov 21 '18 at 16:19
SJR
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
If it's called "Master Log" it's not called "Sheet1"!Set ssheet = ThisWorkbook.Sheets("Master Log")The sheet code name is different from the tab name.
– SJR
Nov 21 '18 at 16:20
add a comment |
I tried to look everywhere for this solution but coulnd't find it.
I am trying to build a userform to input a new row (first row after headers).
The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.
When I run the scrip it gets stuck at this line: Set ssheet = ThisWorkbook.Sheets("Sheet1")
Please find below my code:
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Sheet1")
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
ssheet.Cells(nr, 1) = Me.tbNAME
ssheet.Cells(nr, 2) = Me.cmbStatus
ssheet.Cells(nr, 3) = Me.cmbFunds
ssheet.Cells(nr, 4) = Me.cmbDD
ssheet.Cells(nr, 7) = Me.cmbDistributor
ssheet.Cells(nr, 8) = Me.tbComments
End Sub
Private Sub UserForm_Initialize()
Me.tbDate = Date
For Each blah In [StatusList]
Me.cmbStatus.AddItem blah
Next blah
For Each blah In [FundsList]
Me.cmbFunds.AddItem blah
Next blah
For Each blah In [DDList]
Me.cmbDD.AddItem blah
Next blah
For Each blah In [DistributorList]
Me.cmbDistributor.AddItem blah
Next blah
End Sub
It would be great if you could help me to sort this out, seems so easy to add 6 fields for the rows but then it gets stuck.
Thank you!,
Alex
excel vba excel-vba compiler-errors subscript
edited Nov 21 '18 at 16:19
SJR
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
I tried to look everywhere for this solution but coulnd't find it.
I am trying to build a userform to input a new row (first row after headers).
The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.
When I run the scrip it gets stuck at this line: Set ssheet = ThisWorkbook.Sheets("Sheet1")
Please find below my code:
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Sheet1")
nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
ssheet.Cells(nr, 1) = Me.tbNAME
ssheet.Cells(nr, 2) = Me.cmbStatus
ssheet.Cells(nr, 3) = Me.cmbFunds
ssheet.Cells(nr, 4) = Me.cmbDD
ssheet.Cells(nr, 7) = Me.cmbDistributor
ssheet.Cells(nr, 8) = Me.tbComments
End Sub
Private Sub UserForm_Initialize()
Me.tbDate = Date
For Each blah In [StatusList]
Me.cmbStatus.AddItem blah
Next blah
For Each blah In [FundsList]
Me.cmbFunds.AddItem blah
Next blah
For Each blah In [DDList]
Me.cmbDD.AddItem blah
Next blah
For Each blah In [DistributorList]
Me.cmbDistributor.AddItem blah
Next blah
End Sub
It would be great if you could help me to sort this out, seems so easy to add 6 fields for the rows but then it gets stuck.
Thank you!,
Alex
excel vba excel-vba compiler-errors subscript
excel vba excel-vba compiler-errors subscript
edited Nov 21 '18 at 16:19
SJR
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
edited Nov 21 '18 at 16:19
SJR
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
edited Nov 21 '18 at 16:19
SJR
13.2k31219
edited Nov 21 '18 at 16:19
SJR
13.2k31219
edited Nov 21 '18 at 16:19
SJR
13.2k31219
13.2k31219
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
asked Nov 21 '18 at 16:12
AlessandroAlessandro
85
85
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
If it's called "Master Log" it's not called "Sheet1"!Set ssheet = ThisWorkbook.Sheets("Master Log")The sheet code name is different from the tab name.
– SJR
Nov 21 '18 at 16:20
add a comment |
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
If it's called "Master Log" it's not called "Sheet1"!Set ssheet = ThisWorkbook.Sheets("Master Log")The sheet code name is different from the tab name.
– SJR
Nov 21 '18 at 16:20
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
If it's called "Master Log" it's not called "Sheet1"!
Set ssheet = ThisWorkbook.Sheets("Master Log") The sheet code name is different from the tab name.– SJR
Nov 21 '18 at 16:20
If it's called "Master Log" it's not called "Sheet1"!
Set ssheet = ThisWorkbook.Sheets("Master Log") The sheet code name is different from the tab name.– SJR
Nov 21 '18 at 16:20
add a comment |
1 Answer
1
active
oldest
votes
When using the line:
Set ssheet = ThisWorkbook.Sheets("Sheet1")
Make sure "Sheet1" is actually the name of your sheet on your Workbook
Per your comment you can use:
Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")
And for the following line:
nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416193%2frun-time-error-9-subscript-out-of-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
When using the line:
Set ssheet = ThisWorkbook.Sheets("Sheet1")
Make sure "Sheet1" is actually the name of your sheet on your Workbook
Per your comment you can use:
Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")
And for the following line:
nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
add a comment |
When using the line:
Set ssheet = ThisWorkbook.Sheets("Sheet1")
Make sure "Sheet1" is actually the name of your sheet on your Workbook
Per your comment you can use:
Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")
And for the following line:
nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
add a comment |
When using the line:
Set ssheet = ThisWorkbook.Sheets("Sheet1")
Make sure "Sheet1" is actually the name of your sheet on your Workbook
Per your comment you can use:
Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")
And for the following line:
nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
When using the line:
Set ssheet = ThisWorkbook.Sheets("Sheet1")
Make sure "Sheet1" is actually the name of your sheet on your Workbook
Per your comment you can use:
Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")
And for the following line:
nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
edited Nov 21 '18 at 16:23
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
answered Nov 21 '18 at 16:19
KubieKubie
1,3671619
1,3671619
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
add a comment |
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
Thank you, I changed the name of my Master Log (Sheet1) into Sheet1. now the debugger says that the error is in the following line: nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1
– Alessandro
Nov 21 '18 at 16:21
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
@Alessandro added code above for the next error
– Kubie
Nov 21 '18 at 16:22
1
1
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
@SJR nice catch
– Kubie
Nov 21 '18 at 16:23
1
1
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
solved with the xlup and not x1up sorry guys so stupid! thank you so much for your help!!!!
– Alessandro
Nov 21 '18 at 16:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416193%2frun-time-error-9-subscript-out-of-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
ThisWorkbook (the one containing the code) doesn't have a "Sheet1"?
– SJR
Nov 21 '18 at 16:14
Yes it does have a Sheet1 also called Master Log, I tried both solutions still nothing unfortunately :(
– Alessandro
Nov 21 '18 at 16:19
If it's called "Master Log" it's not called "Sheet1"!
Set ssheet = ThisWorkbook.Sheets("Master Log")The sheet code name is different from the tab name.– SJR
Nov 21 '18 at 16:20