Mixing
Calling worksheet in the form using VBA
I need to check if the value can be found in the lookupvalues sheet. I used userform to create a compare tool and need to check if the data is in the LookUpValues tab. The Form and LookupValues are in the same workbook. When I run my code, I got an error "Subscript is out of range". Below code is for getting the length of the Column A in the LookUpValues.
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row
Can anyone help me identify the issue in my code. How will I call the worksheet in the userform?
The image below shows the structure with userform and the LookUpValues.
Thank you in advance.
excel vba excel-vba
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
add a comment |
I need to check if the value can be found in the lookupvalues sheet. I used userform to create a compare tool and need to check if the data is in the LookUpValues tab. The Form and LookupValues are in the same workbook. When I run my code, I got an error "Subscript is out of range". Below code is for getting the length of the Column A in the LookUpValues.
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row
Can anyone help me identify the issue in my code. How will I call the worksheet in the userform?
The image below shows the structure with userform and the LookUpValues.
Thank you in advance.
excel vba excel-vba
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also islRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?
– Zack E
Jan 2 at 18:20
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
1
Fully qualify your Range reference. Something like:lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).rowCode would be cleaner if you add your Worksheet reference to a variable.
– Ryan Wildry
Jan 2 at 18:32
One way to start on the UserForm issue is calling an event like aButton_Click()that will allow you to run the code to look at the information. All thelRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowdoes is get the last row of column A.
– Zack E
Jan 2 at 18:34
add a comment |
I need to check if the value can be found in the lookupvalues sheet. I used userform to create a compare tool and need to check if the data is in the LookUpValues tab. The Form and LookupValues are in the same workbook. When I run my code, I got an error "Subscript is out of range". Below code is for getting the length of the Column A in the LookUpValues.
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row
Can anyone help me identify the issue in my code. How will I call the worksheet in the userform?
The image below shows the structure with userform and the LookUpValues.
Thank you in advance.
excel vba excel-vba
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
I need to check if the value can be found in the lookupvalues sheet. I used userform to create a compare tool and need to check if the data is in the LookUpValues tab. The Form and LookupValues are in the same workbook. When I run my code, I got an error "Subscript is out of range". Below code is for getting the length of the Column A in the LookUpValues.
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row
Can anyone help me identify the issue in my code. How will I call the worksheet in the userform?
The image below shows the structure with userform and the LookUpValues.
Thank you in advance.
excel vba excel-vba
excel vba excel-vba
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
asked Jan 2 at 18:12
pinkpantherpinkpanther
556
556
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also islRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?
– Zack E
Jan 2 at 18:20
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
1
Fully qualify your Range reference. Something like:lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).rowCode would be cleaner if you add your Worksheet reference to a variable.
– Ryan Wildry
Jan 2 at 18:32
One way to start on the UserForm issue is calling an event like aButton_Click()that will allow you to run the code to look at the information. All thelRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowdoes is get the last row of column A.
– Zack E
Jan 2 at 18:34
add a comment |
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also islRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?
– Zack E
Jan 2 at 18:20
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
1
Fully qualify your Range reference. Something like:lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).rowCode would be cleaner if you add your Worksheet reference to a variable.
– Ryan Wildry
Jan 2 at 18:32
One way to start on the UserForm issue is calling an event like aButton_Click()that will allow you to run the code to look at the information. All thelRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowdoes is get the last row of column A.
– Zack E
Jan 2 at 18:34
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also is
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?– Zack E
Jan 2 at 18:20
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also is
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?– Zack E
Jan 2 at 18:20
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
1
1
Fully qualify your Range reference. Something like:
lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).row Code would be cleaner if you add your Worksheet reference to a variable.– Ryan Wildry
Jan 2 at 18:32
Fully qualify your Range reference. Something like:
lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).row Code would be cleaner if you add your Worksheet reference to a variable.– Ryan Wildry
Jan 2 at 18:32
One way to start on the UserForm issue is calling an event like a
Button_Click() that will allow you to run the code to look at the information. All the lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row does is get the last row of column A.– Zack E
Jan 2 at 18:34
One way to start on the UserForm issue is calling an event like a
Button_Click() that will allow you to run the code to look at the information. All the lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).row does is get the last row of column A.– Zack E
Jan 2 at 18:34
add a comment |
0
active
oldest
votes
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%2f54011182%2fcalling-worksheet-in-the-form-using-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54011182%2fcalling-worksheet-in-the-form-using-vba%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
Is there a command button or some other event on the userform that will run the code to look at the information on the LookUpValues Sheet? Also is
lRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowthe only piece of code you have written ?– Zack E
Jan 2 at 18:20
No other event on the userform to look at the LookUpValues sheet. Can you give me an idea on how will I do that?
– pinkpanther
Jan 2 at 18:23
Posting a single line of code is probably not going to cut it.
– SJR
Jan 2 at 18:29
1
Fully qualify your Range reference. Something like:
lRowA = ThisWorkBook.Sheets("LookUpValues").Range("A" & ThisWorkBook.Sheets("LookUpValues").Rows.count).End(xlUp).rowCode would be cleaner if you add your Worksheet reference to a variable.– Ryan Wildry
Jan 2 at 18:32
One way to start on the UserForm issue is calling an event like a
Button_Click()that will allow you to run the code to look at the information. All thelRowA = Sheets("LookUpValues").Range("A" & .Rows.count).End(xlUp).rowdoes is get the last row of column A.– Zack E
Jan 2 at 18:34