Trying to use. Find in VBA, getting error saying the object doest support the property or moethod
1
Here's the problem function. I've written lots of functions similar to this without issue and I've no idea what the problem is this time.
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Find("EQUIPMENT",
LookIn:=xlValues, MatchCase:=True)
MsgBox (equipment)
End Sub
excel vba excel-vba
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
add a comment |
1
Here's the problem function. I've written lots of functions similar to this without issue and I've no idea what the problem is this time.
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Find("EQUIPMENT",
LookIn:=xlValues, MatchCase:=True)
MsgBox (equipment)
End Sub
excel vba excel-vba
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
5
Find()is a method of theRangeobject. Not theSheetobject. Instead:Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)
– JNevill
Nov 26 '18 at 18:36
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
1
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
5
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variableequipment. A one liner way to do this isIf Not equipment is Nothing Then MsgBox (equipment)
– urdearboy
Nov 26 '18 at 19:02
add a comment |
1
1
1
Here's the problem function. I've written lots of functions similar to this without issue and I've no idea what the problem is this time.
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Find("EQUIPMENT",
LookIn:=xlValues, MatchCase:=True)
MsgBox (equipment)
End Sub
excel vba excel-vba
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
Here's the problem function. I've written lots of functions similar to this without issue and I've no idea what the problem is this time.
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Find("EQUIPMENT",
LookIn:=xlValues, MatchCase:=True)
MsgBox (equipment)
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:16
Pᴇʜ
22.8k62850
22.8k62850
asked Nov 26 '18 at 18:33
TomTom
224
asked Nov 26 '18 at 18:33
TomTom
224
asked Nov 26 '18 at 18:33
TomTom
224
224
5
Find()is a method of theRangeobject. Not theSheetobject. Instead:Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)
– JNevill
Nov 26 '18 at 18:36
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
1
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
5
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variableequipment. A one liner way to do this isIf Not equipment is Nothing Then MsgBox (equipment)
– urdearboy
Nov 26 '18 at 19:02
add a comment |
5
Find()is a method of theRangeobject. Not theSheetobject. Instead:Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)
– JNevill
Nov 26 '18 at 18:36
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
1
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
5
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variableequipment. A one liner way to do this isIf Not equipment is Nothing Then MsgBox (equipment)
– urdearboy
Nov 26 '18 at 19:02
5
5
Find() is a method of the Range object. Not the Sheet object. Instead: Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)– JNevill
Nov 26 '18 at 18:36
Find() is a method of the Range object. Not the Sheet object. Instead: Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)– JNevill
Nov 26 '18 at 18:36
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
1
1
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
5
5
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variable
equipment. A one liner way to do this is If Not equipment is Nothing Then MsgBox (equipment)– urdearboy
Nov 26 '18 at 19:02
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variable
equipment. A one liner way to do this is If Not equipment is Nothing Then MsgBox (equipment)– urdearboy
Nov 26 '18 at 19:02
add a comment |
1 Answer
1
active
oldest
votes
1
As mentioned in the comments, Find() is a method of the Range object, not of the Worksheets object.
This is a way to run your code without an error:
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Cells.Find("EQUIPMENT", _
LookIn:=xlValues, MatchCase:=True)
If Not equipment Is Nothing Then
MsgBox equipment.Address
Else
MsgBox "MISSING"
End If
End Sub
See the .Cells between Sheets() and .Find.
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
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%2f53487084%2ftrying-to-use-find-in-vba-getting-error-saying-the-object-doest-support-the-pr%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
1
As mentioned in the comments, Find() is a method of the Range object, not of the Worksheets object.
This is a way to run your code without an error:
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Cells.Find("EQUIPMENT", _
LookIn:=xlValues, MatchCase:=True)
If Not equipment Is Nothing Then
MsgBox equipment.Address
Else
MsgBox "MISSING"
End If
End Sub
See the .Cells between Sheets() and .Find.
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
add a comment |
1
As mentioned in the comments, Find() is a method of the Range object, not of the Worksheets object.
This is a way to run your code without an error:
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Cells.Find("EQUIPMENT", _
LookIn:=xlValues, MatchCase:=True)
If Not equipment Is Nothing Then
MsgBox equipment.Address
Else
MsgBox "MISSING"
End If
End Sub
See the .Cells between Sheets() and .Find.
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
add a comment |
1
1
1
As mentioned in the comments, Find() is a method of the Range object, not of the Worksheets object.
This is a way to run your code without an error:
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Cells.Find("EQUIPMENT", _
LookIn:=xlValues, MatchCase:=True)
If Not equipment Is Nothing Then
MsgBox equipment.Address
Else
MsgBox "MISSING"
End If
End Sub
See the .Cells between Sheets() and .Find.
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
As mentioned in the comments, Find() is a method of the Range object, not of the Worksheets object.
This is a way to run your code without an error:
Sub FindEquipCost()
Dim equipment As Range
Set equipment = Sheets("Sheet1").Cells.Find("EQUIPMENT", _
LookIn:=xlValues, MatchCase:=True)
If Not equipment Is Nothing Then
MsgBox equipment.Address
Else
MsgBox "MISSING"
End If
End Sub
See the .Cells between Sheets() and .Find.
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
answered Nov 27 '18 at 21:45
VityataVityata
31.9k72352
31.9k72352
add a comment |
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%2f53487084%2ftrying-to-use-find-in-vba-getting-error-saying-the-object-doest-support-the-pr%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
5
Find()is a method of theRangeobject. Not theSheetobject. Instead:Sheets("Sheet1").Range.Find("EQUIPMENT", LookIn:=xlValues, MatchCase:=True)– JNevill
Nov 26 '18 at 18:36
Needed to add range, thanks
– Tom
Nov 26 '18 at 18:39
1
You may also be missing a line continuation character "_" if the 'set equipment' line goes over two lines.
– Freeflow
Nov 26 '18 at 18:40
5
Unless you are 100% sure your value to be found always exists, you may want to code for the possibility of your value not being found (which will yeild an error when you try to use your variable
equipment. A one liner way to do this isIf Not equipment is Nothing Then MsgBox (equipment)– urdearboy
Nov 26 '18 at 19:02