Using Index-Match in VBA
Am trying to insert a Index-Match function in my VBA macro
It looks like this:
Columns("C:C").Forumla = "=INDEX('[blabla.xlsm]Sheet1'!$F$4:$F$102,MATCH(B1,'[blabla.xlsm]Sheet1'!$G$4:$G$102,0))"
However I keep getting "Run-time error 438: Object doesn't support this property or method"
Any tips, suggestions would be greatly appreciated.
microsoft-excel vba
asked Feb 1 at 17:15
RiyerRiyer
155
add a comment |
Am trying to insert a Index-Match function in my VBA macro
It looks like this:
Columns("C:C").Forumla = "=INDEX('[blabla.xlsm]Sheet1'!$F$4:$F$102,MATCH(B1,'[blabla.xlsm]Sheet1'!$G$4:$G$102,0))"
However I keep getting "Run-time error 438: Object doesn't support this property or method"
Any tips, suggestions would be greatly appreciated.
microsoft-excel vba
asked Feb 1 at 17:15
RiyerRiyer
155
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
1
Forumlaisn't a member of theRangeclass. DoesFormulawork?
– Mathieu Guindon
Feb 1 at 17:24
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37
add a comment |
Am trying to insert a Index-Match function in my VBA macro
It looks like this:
Columns("C:C").Forumla = "=INDEX('[blabla.xlsm]Sheet1'!$F$4:$F$102,MATCH(B1,'[blabla.xlsm]Sheet1'!$G$4:$G$102,0))"
However I keep getting "Run-time error 438: Object doesn't support this property or method"
Any tips, suggestions would be greatly appreciated.
microsoft-excel vba
asked Feb 1 at 17:15
RiyerRiyer
155
Am trying to insert a Index-Match function in my VBA macro
It looks like this:
Columns("C:C").Forumla = "=INDEX('[blabla.xlsm]Sheet1'!$F$4:$F$102,MATCH(B1,'[blabla.xlsm]Sheet1'!$G$4:$G$102,0))"
However I keep getting "Run-time error 438: Object doesn't support this property or method"
Any tips, suggestions would be greatly appreciated.
microsoft-excel vba
microsoft-excel vba
asked Feb 1 at 17:15
RiyerRiyer
155
asked Feb 1 at 17:15
RiyerRiyer
155
asked Feb 1 at 17:15
RiyerRiyer
155
asked Feb 1 at 17:15
RiyerRiyer
155
asked Feb 1 at 17:15
RiyerRiyer
155
155
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
1
Forumlaisn't a member of theRangeclass. DoesFormulawork?
– Mathieu Guindon
Feb 1 at 17:24
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37
add a comment |
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
1
Forumlaisn't a member of theRangeclass. DoesFormulawork?
– Mathieu Guindon
Feb 1 at 17:24
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
1
1
Forumla isn't a member of the Range class. Does Formula work?– Mathieu Guindon
Feb 1 at 17:24
Forumla isn't a member of the Range class. Does Formula work?– Mathieu Guindon
Feb 1 at 17:24
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37
add a comment |
1 Answer
1
active
oldest
votes
Declare a Range variable, and assign it to Columns("C:C") e.g. Set myRange = ActiveSheet.Columns("C:C") (if that's in a standard module) or Set myRange = Me.Columns("C:C") (if that's in a worksheet module).
Dim myRange As Range
Set myRange = Columns("C:C")
myRange.Formula = "..."
Then when you type the dot in myRange., you'll get a dropdown listing the available members, parameter-quickinfo tooltips, and compile-time validation. The reason your code was allowed to compile and run (regardless of whether Option Explicit is specified or not) is because Columns returns a Range object, but doesn't have any parameters - the ("C:C") arguments are in fact provided to the Range.[_Default] property, which returns a Variant. Any member calls chained to it, are therefore only resolved at run-time, i.e. any typo won't prevent compilation, and will throw error 438 at run-time.
By declaring a Range variable, you're "type-casting" that Variant into a Range, and from there the compiler is able to validate what members exist, and throw compile-time errors given a typo.
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1401072%2fusing-index-match-in-vba%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
Declare a Range variable, and assign it to Columns("C:C") e.g. Set myRange = ActiveSheet.Columns("C:C") (if that's in a standard module) or Set myRange = Me.Columns("C:C") (if that's in a worksheet module).
Dim myRange As Range
Set myRange = Columns("C:C")
myRange.Formula = "..."
Then when you type the dot in myRange., you'll get a dropdown listing the available members, parameter-quickinfo tooltips, and compile-time validation. The reason your code was allowed to compile and run (regardless of whether Option Explicit is specified or not) is because Columns returns a Range object, but doesn't have any parameters - the ("C:C") arguments are in fact provided to the Range.[_Default] property, which returns a Variant. Any member calls chained to it, are therefore only resolved at run-time, i.e. any typo won't prevent compilation, and will throw error 438 at run-time.
By declaring a Range variable, you're "type-casting" that Variant into a Range, and from there the compiler is able to validate what members exist, and throw compile-time errors given a typo.
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
add a comment |
Declare a Range variable, and assign it to Columns("C:C") e.g. Set myRange = ActiveSheet.Columns("C:C") (if that's in a standard module) or Set myRange = Me.Columns("C:C") (if that's in a worksheet module).
Dim myRange As Range
Set myRange = Columns("C:C")
myRange.Formula = "..."
Then when you type the dot in myRange., you'll get a dropdown listing the available members, parameter-quickinfo tooltips, and compile-time validation. The reason your code was allowed to compile and run (regardless of whether Option Explicit is specified or not) is because Columns returns a Range object, but doesn't have any parameters - the ("C:C") arguments are in fact provided to the Range.[_Default] property, which returns a Variant. Any member calls chained to it, are therefore only resolved at run-time, i.e. any typo won't prevent compilation, and will throw error 438 at run-time.
By declaring a Range variable, you're "type-casting" that Variant into a Range, and from there the compiler is able to validate what members exist, and throw compile-time errors given a typo.
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
add a comment |
Declare a Range variable, and assign it to Columns("C:C") e.g. Set myRange = ActiveSheet.Columns("C:C") (if that's in a standard module) or Set myRange = Me.Columns("C:C") (if that's in a worksheet module).
Dim myRange As Range
Set myRange = Columns("C:C")
myRange.Formula = "..."
Then when you type the dot in myRange., you'll get a dropdown listing the available members, parameter-quickinfo tooltips, and compile-time validation. The reason your code was allowed to compile and run (regardless of whether Option Explicit is specified or not) is because Columns returns a Range object, but doesn't have any parameters - the ("C:C") arguments are in fact provided to the Range.[_Default] property, which returns a Variant. Any member calls chained to it, are therefore only resolved at run-time, i.e. any typo won't prevent compilation, and will throw error 438 at run-time.
By declaring a Range variable, you're "type-casting" that Variant into a Range, and from there the compiler is able to validate what members exist, and throw compile-time errors given a typo.
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
Declare a Range variable, and assign it to Columns("C:C") e.g. Set myRange = ActiveSheet.Columns("C:C") (if that's in a standard module) or Set myRange = Me.Columns("C:C") (if that's in a worksheet module).
Dim myRange As Range
Set myRange = Columns("C:C")
myRange.Formula = "..."
Then when you type the dot in myRange., you'll get a dropdown listing the available members, parameter-quickinfo tooltips, and compile-time validation. The reason your code was allowed to compile and run (regardless of whether Option Explicit is specified or not) is because Columns returns a Range object, but doesn't have any parameters - the ("C:C") arguments are in fact provided to the Range.[_Default] property, which returns a Variant. Any member calls chained to it, are therefore only resolved at run-time, i.e. any typo won't prevent compilation, and will throw error 438 at run-time.
By declaring a Range variable, you're "type-casting" that Variant into a Range, and from there the compiler is able to validate what members exist, and throw compile-time errors given a typo.
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
edited Feb 1 at 17:39
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
answered Feb 1 at 17:33
Mathieu GuindonMathieu Guindon
634311
634311
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
add a comment |
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
Legend. It worked. Cheers m8.
– Riyer
Feb 1 at 17:40
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
( @Riyer - If this worked, and solves the issue for you, you can flag it as The Answer by clicking the check mark left of the post. This gives extra credit to the author, and removes your question from the Unanswered queue.)
– BruceWayne
Feb 1 at 17:49
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
Got it. Thanks Bruce. And super thanks Mathieu.
– Riyer
Feb 1 at 17:50
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1401072%2fusing-index-match-in-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
Apologies - should have added a bit more detail. I have a file with data, and I've inserted a column in between the 2 columns of data that the file comes with. I want to place the index-match formula in the newly created column, and then copy paste the index-match column and 1 out of the initial 2 columns into a different sheet. Hence the use of "column()".
– Riyer
Feb 1 at 17:17
1
Forumlaisn't a member of theRangeclass. DoesFormulawork?– Mathieu Guindon
Feb 1 at 17:24
.formula works for a TEXT() function, but not for the Index() function that is referencing other sheets
– Riyer
Feb 1 at 17:33
I presume it's a different error though - 1004 by any chance? Try taking the formula into an actual cell - if Excel can't evaluate it, you can't have VBA code that attempts to force that invalid formula into a cell.
– Mathieu Guindon
Feb 1 at 17:37