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 '18 at 7:14
Pᴇʜ
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed 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 '18 at 7:14
Pᴇʜ
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed 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 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 '18 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 '18 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 '18 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 '18 at 7:14
Pᴇʜ
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed 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 '18 at 7:14
Pᴇʜ
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed Joffs
74
edited Nov 13 '18 at 7:14
Pᴇʜ
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed Joffs
74
edited Nov 13 '18 at 7:14
Pᴇʜ
21.1k42750
edited Nov 13 '18 at 7:14
Pᴇʜ
21.1k42750
edited Nov 13 '18 at 7:14
Pᴇʜ
21.1k42750
21.1k42750
asked Nov 12 '18 at 17:51
Ted JoffsTed Joffs
74
asked Nov 12 '18 at 17:51
Ted JoffsTed Joffs
74
asked Nov 12 '18 at 17:51
Ted JoffsTed 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 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 '18 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 '18 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 '18 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 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.
– Comintern
Nov 12 '18 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 '18 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 '18 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 '18 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 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 18:20
Ted JoffsTed 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 '18 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 '18 at 18:20
Ted JoffsTed 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 '18 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 '18 at 18:20
Ted JoffsTed 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 '18 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 '18 at 18:20
Ted JoffsTed 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 '18 at 18:20
Ted JoffsTed Joffs
74
answered Nov 12 '18 at 18:20
Ted JoffsTed Joffs
74
answered Nov 12 '18 at 18:20
Ted JoffsTed Joffs
74
answered Nov 12 '18 at 18:20
Ted JoffsTed 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 '18 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 '18 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 '18 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 '18 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.
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 '18 at 17:55
change .Range to Range
– Gary's Student
Nov 12 '18 at 17:55
ican't beNothing- it's aLong. You need to test theFindreturn value against nothing - not the.Row.– Comintern
Nov 12 '18 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 '18 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 '18 at 17:58