Type Mismatch W/Long Data Type
0
I am having an issue with the following code:
Dim i As Long
Dim wb As Workbook, ws As Worksheet, deleteSheet As Worksheet
Dim lastRow As Long, lastCol As Long
Dim myRow
i = .Range("A:E").Find("*Data Set*").Row
If Not i Is Nothing Then
Basically this is returning a type mismatch on i and I am not sure how to resolve it. I would appreciate any help here..
excel vba excel-vba
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
|
show 1 more comment
0
I am having an issue with the following code:
Dim i As Long
Dim wb As Workbook, ws As Worksheet, deleteSheet As Worksheet
Dim lastRow As Long, lastCol As Long
Dim myRow
i = .Range("A:E").Find("*Data Set*").Row
If Not i Is Nothing Then
Basically this is returning a type mismatch on i and I am not sure how to resolve it. I would appreciate any help here..
excel vba excel-vba
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
Where is yourWithstatement? Otherwise.Range("A:E").Find("*Data Set*").Rowshould bews.Range("A:E").Find("*Data Set*").Row(or something similar)
– cybernetic.nomad
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 at 17:55
Should beibe the row number or the cell that you've found viaRange.Find? You seem to check if it is not nothing on the next line -- which is a check forobjects, notlongs.
– chillin
Nov 12 at 17:56
Also,Range.Findwill inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.
– chillin
Nov 12 at 17:58
|
show 1 more comment
0
0
0
I am having an issue with the following code:
Dim i As Long
Dim wb As Workbook, ws As Worksheet, deleteSheet As Worksheet
Dim lastRow As Long, lastCol As Long
Dim myRow
i = .Range("A:E").Find("*Data Set*").Row
If Not i Is Nothing Then
Basically this is returning a type mismatch on i and I am not sure how to resolve it. I would appreciate any help here..
excel vba excel-vba
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
I am having an issue with the following code:
Dim i As Long
Dim wb As Workbook, ws As Worksheet, deleteSheet As Worksheet
Dim lastRow As Long, lastCol As Long
Dim myRow
i = .Range("A:E").Find("*Data Set*").Row
If Not i Is Nothing Then
Basically this is returning a type mismatch on i and I am not sure how to resolve it. I would appreciate any help here..
excel vba excel-vba
excel vba excel-vba
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
edited Nov 13 at 7:14
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 12 at 17:51
Ted Joffs
74
asked Nov 12 at 17:51
Ted Joffs
74
asked Nov 12 at 17:51
Ted Joffs
74
74
Where is yourWithstatement? Otherwise.Range("A:E").Find("*Data Set*").Rowshould bews.Range("A:E").Find("*Data Set*").Row(or something similar)
– cybernetic.nomad
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 at 17:55
Should beibe the row number or the cell that you've found viaRange.Find? You seem to check if it is not nothing on the next line -- which is a check forobjects, notlongs.
– chillin
Nov 12 at 17:56
Also,Range.Findwill inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.
– chillin
Nov 12 at 17:58
|
show 1 more comment
Where is yourWithstatement? Otherwise.Range("A:E").Find("*Data Set*").Rowshould bews.Range("A:E").Find("*Data Set*").Row(or something similar)
– cybernetic.nomad
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 at 17:55
Should beibe the row number or the cell that you've found viaRange.Find? You seem to check if it is not nothing on the next line -- which is a check forobjects, notlongs.
– chillin
Nov 12 at 17:56
Also,Range.Findwill inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.
– chillin
Nov 12 at 17:58
Where is your
With statement? Otherwise .Range("A:E").Find("*Data Set*").Row should be ws.Range("A:E").Find("*Data Set*").Row (or something similar)– cybernetic.nomad
Nov 12 at 17:55
Where is your
With statement? Otherwise .Range("A:E").Find("*Data Set*").Row should be ws.Range("A:E").Find("*Data Set*").Row (or something similar)– cybernetic.nomad
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
i can't be Nothing - it's a Long. You need to test the Find return value against nothing - not the .Row.– Comintern
Nov 12 at 17:55
i can't be Nothing - it's a Long. You need to test the Find return value against nothing - not the .Row.– Comintern
Nov 12 at 17:55
Should be
i be the row number or the cell that you've found via Range.Find? You seem to check if it is not nothing on the next line -- which is a check for objects, not longs.– chillin
Nov 12 at 17:56
Should be
i be the row number or the cell that you've found via Range.Find? You seem to check if it is not nothing on the next line -- which is a check for objects, not longs.– chillin
Nov 12 at 17:56
Also,
Range.Find will inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.– chillin
Nov 12 at 17:58
Also,
Range.Find will inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.– chillin
Nov 12 at 17:58
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
Thanks all! Changed a couple lines based on the feedback and got it!
If Not .Range("A:E").Find("*Data Set*") Is Nothing Then
i = .Range("A:E").Find("*Data Set*").Row
I had a with above, so that was not the issue, actually. I ended up re-ordering things and doing the validation before setting the variable so it was not using the row value as @comintern and @chillin suggested. Appreciate the help.
answered Nov 12 at 18:20
Ted Joffs
74
This will runFind2 times (which makes it slow). Set the result ofFindto a variable and then test this variable (to runFindonly once). Eg.Set FoundAt = .Range("A:E").Find("*Data Set*")and then use it forIf Not FoundAt Is Nothing Thenandi = FoundAt.Row
– Pᴇʜ
Nov 13 at 7:17
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
});
}
});
draft saved
draft discarded
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%2f53267547%2ftype-mismatch-w-long-data-type%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
0
Thanks all! Changed a couple lines based on the feedback and got it!
If Not .Range("A:E").Find("*Data Set*") Is Nothing Then
i = .Range("A:E").Find("*Data Set*").Row
I had a with above, so that was not the issue, actually. I ended up re-ordering things and doing the validation before setting the variable so it was not using the row value as @comintern and @chillin suggested. Appreciate the help.
answered Nov 12 at 18:20
Ted Joffs
74
This will runFind2 times (which makes it slow). Set the result ofFindto a variable and then test this variable (to runFindonly once). Eg.Set FoundAt = .Range("A:E").Find("*Data Set*")and then use it forIf Not FoundAt Is Nothing Thenandi = FoundAt.Row
– Pᴇʜ
Nov 13 at 7:17
add a comment |
0
Thanks all! Changed a couple lines based on the feedback and got it!
If Not .Range("A:E").Find("*Data Set*") Is Nothing Then
i = .Range("A:E").Find("*Data Set*").Row
I had a with above, so that was not the issue, actually. I ended up re-ordering things and doing the validation before setting the variable so it was not using the row value as @comintern and @chillin suggested. Appreciate the help.
answered Nov 12 at 18:20
Ted Joffs
74
This will runFind2 times (which makes it slow). Set the result ofFindto a variable and then test this variable (to runFindonly once). Eg.Set FoundAt = .Range("A:E").Find("*Data Set*")and then use it forIf Not FoundAt Is Nothing Thenandi = FoundAt.Row
– Pᴇʜ
Nov 13 at 7:17
add a comment |
0
0
0
Thanks all! Changed a couple lines based on the feedback and got it!
If Not .Range("A:E").Find("*Data Set*") Is Nothing Then
i = .Range("A:E").Find("*Data Set*").Row
I had a with above, so that was not the issue, actually. I ended up re-ordering things and doing the validation before setting the variable so it was not using the row value as @comintern and @chillin suggested. Appreciate the help.
answered Nov 12 at 18:20
Ted Joffs
74
Thanks all! Changed a couple lines based on the feedback and got it!
If Not .Range("A:E").Find("*Data Set*") Is Nothing Then
i = .Range("A:E").Find("*Data Set*").Row
I had a with above, so that was not the issue, actually. I ended up re-ordering things and doing the validation before setting the variable so it was not using the row value as @comintern and @chillin suggested. Appreciate the help.
answered Nov 12 at 18:20
Ted Joffs
74
answered Nov 12 at 18:20
Ted Joffs
74
answered Nov 12 at 18:20
Ted Joffs
74
answered Nov 12 at 18:20
Ted Joffs
74
74
This will runFind2 times (which makes it slow). Set the result ofFindto a variable and then test this variable (to runFindonly once). Eg.Set FoundAt = .Range("A:E").Find("*Data Set*")and then use it forIf Not FoundAt Is Nothing Thenandi = FoundAt.Row
– Pᴇʜ
Nov 13 at 7:17
add a comment |
This will runFind2 times (which makes it slow). Set the result ofFindto a variable and then test this variable (to runFindonly once). Eg.Set FoundAt = .Range("A:E").Find("*Data Set*")and then use it forIf Not FoundAt Is Nothing Thenandi = FoundAt.Row
– Pᴇʜ
Nov 13 at 7:17
This will run
Find 2 times (which makes it slow). Set the result of Find to a variable and then test this variable (to run Find only once). Eg. Set FoundAt = .Range("A:E").Find("*Data Set*") and then use it for If Not FoundAt Is Nothing Then and i = FoundAt.Row– Pᴇʜ
Nov 13 at 7:17
This will run
Find 2 times (which makes it slow). Set the result of Find to a variable and then test this variable (to run Find only once). Eg. Set FoundAt = .Range("A:E").Find("*Data Set*") and then use it for If Not FoundAt Is Nothing Then and i = FoundAt.Row– Pᴇʜ
Nov 13 at 7:17
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53267547%2ftype-mismatch-w-long-data-type%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
Where is your
Withstatement? Otherwise.Range("A:E").Find("*Data Set*").Rowshould bews.Range("A:E").Find("*Data Set*").Row(or something similar)– cybernetic.nomad
Nov 12 at 17:55
change .Range to Range
– Gary's Student
Nov 12 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.– Comintern
Nov 12 at 17:55
Should be
ibe the row number or the cell that you've found viaRange.Find? You seem to check if it is not nothing on the next line -- which is a check forobjects, notlongs.– chillin
Nov 12 at 17:56
Also,
Range.Findwill inherit the settings/parameters from the last time it was used -- so things like match whole/part and case sensitive may be anything, unless you specify them explicitly.– chillin
Nov 12 at 17:58