VBA Function query using IF formulaWhat's the difference between a method and a function?var functionName = function() vs function functionName() Set a default parameter value for a JavaScript functionWhat does the exclamation mark do before the function?Excel formula error on VBAVBA Execute excel formula with cell reference in many different cellsRun excel formula through VBAManaging typical excel formula errors in vba functionsVBA copy range of values not destroying formulaspercentile formula error in VBA
Causal Diagrams using Wolfram?
Regression terminology, predictor vs IV vs?
Why aren't rockets built with truss structures inside their fuel & oxidizer tanks to increase structural strength?
How can I find an old paper when the usual methods fail?
Are those flyers about apartment purchase a scam?
In which case does the Security misconfiguration vulnerability apply to?
How was the murder committed?
Telephone number in spoken words
Why aren't rainbows blurred-out into nothing after they are produced?
Luggage Storage at Szechenyi Baths
Why aren’t there water shutoff valves for each room?
How can God warn people of the upcoming rapture without disrupting society?
Will using a resistor in series with a LED to control its voltage increase the total energy expenditure?
What would it take to get a message to another star?
Are employers legally allowed to pay employees in goods and services equal to or greater than the minimum wage?
Link for download latest Edubuntu
How should I write this passage to make it the most readable?
Go to last file in vim
Does fossil fuels use since 1990 account for half of all the fossil fuels used in history?
Pokemon Go: Gym Badge Over-completed?
How can I communicate my issues with a potential date's pushy behavior?
Why won't the Republicans use a superdelegate system like the DNC in their nomination process?
Why is there a large performance impact when looping over an array over 240 elements?
What is a "soap"?
VBA Function query using IF formula
What's the difference between a method and a function?var functionName = function() vs function functionName() Set a default parameter value for a JavaScript functionWhat does the exclamation mark do before the function?Excel formula error on VBAVBA Execute excel formula with cell reference in many different cellsRun excel formula through VBAManaging typical excel formula errors in vba functionsVBA copy range of values not destroying formulaspercentile formula error in VBA
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm trying to create a VBA function which generates quite a complex formula if there's a particular value in Cell G25. Can anyone help please?
My formula is:
=IF(G25="46mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)),IF(G25="67mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$K$5>=I25)/(Matrix!$C$5:$K$5>=I25))*COLUMN(Matrix!$C$5:$K$5),1)),IF(G25="90mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)))))+AL25
I'm currently using the above formula in Cell AG25 but it's generating a #VALUE! error where there's no content in cell G25.
Ideally this function will apply to any cells in range g25:g56 dependant on the value in range a25:a56
vba function if-statement formula
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
add a comment |
I'm trying to create a VBA function which generates quite a complex formula if there's a particular value in Cell G25. Can anyone help please?
My formula is:
=IF(G25="46mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)),IF(G25="67mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$K$5>=I25)/(Matrix!$C$5:$K$5>=I25))*COLUMN(Matrix!$C$5:$K$5),1)),IF(G25="90mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)))))+AL25
I'm currently using the above formula in Cell AG25 but it's generating a #VALUE! error where there's no content in cell G25.
Ideally this function will apply to any cells in range g25:g56 dependant on the value in range a25:a56
vba function if-statement formula
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Do 46mm and 90mm do the same thing? I would recommend changing theIFpart to where the it is actually needed, so in this case, it looks like you'd need,....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))...within the AGGREGATE.
– Nathan_Sav
Mar 27 at 11:00
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
For a matrix, you can useINDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false))so if I wanted to know how much customer1 pays for a drill, I could sayINDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false))with customers being in column A and the header for products in row 1
– Nathan_Sav
Mar 27 at 11:07
add a comment |
I'm trying to create a VBA function which generates quite a complex formula if there's a particular value in Cell G25. Can anyone help please?
My formula is:
=IF(G25="46mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)),IF(G25="67mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$K$5>=I25)/(Matrix!$C$5:$K$5>=I25))*COLUMN(Matrix!$C$5:$K$5),1)),IF(G25="90mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)))))+AL25
I'm currently using the above formula in Cell AG25 but it's generating a #VALUE! error where there's no content in cell G25.
Ideally this function will apply to any cells in range g25:g56 dependant on the value in range a25:a56
vba function if-statement formula
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
I'm trying to create a VBA function which generates quite a complex formula if there's a particular value in Cell G25. Can anyone help please?
My formula is:
=IF(G25="46mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)),IF(G25="67mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$K$5>=I25)/(Matrix!$C$5:$K$5>=I25))*COLUMN(Matrix!$C$5:$K$5),1)),IF(G25="90mm",INDEX(Matrix!A:K,AGGREGATE(15,3,((Matrix!$B$6:$B$13>=M25)/(Matrix!$B$6:$B$13>=M25))*ROW(Matrix!$B$6:$B$13),1),AGGREGATE(15,3,((Matrix!$C$5:$G$5>=I25)/(Matrix!$C$5:$G$5>=I25))*COLUMN(Matrix!$C$5:$G$5),1)))))+AL25
I'm currently using the above formula in Cell AG25 but it's generating a #VALUE! error where there's no content in cell G25.
Ideally this function will apply to any cells in range g25:g56 dependant on the value in range a25:a56
vba function if-statement formula
vba function if-statement formula
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
asked Mar 27 at 10:51
KeelySGKeelySG
81 bronze badge
81 bronze badge
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Do 46mm and 90mm do the same thing? I would recommend changing theIFpart to where the it is actually needed, so in this case, it looks like you'd need,....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))...within the AGGREGATE.
– Nathan_Sav
Mar 27 at 11:00
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
For a matrix, you can useINDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false))so if I wanted to know how much customer1 pays for a drill, I could sayINDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false))with customers being in column A and the header for products in row 1
– Nathan_Sav
Mar 27 at 11:07
add a comment |
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Do 46mm and 90mm do the same thing? I would recommend changing theIFpart to where the it is actually needed, so in this case, it looks like you'd need,....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))...within the AGGREGATE.
– Nathan_Sav
Mar 27 at 11:00
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
For a matrix, you can useINDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false))so if I wanted to know how much customer1 pays for a drill, I could sayINDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false))with customers being in column A and the header for products in row 1
– Nathan_Sav
Mar 27 at 11:07
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Do 46mm and 90mm do the same thing? I would recommend changing the
IF part to where the it is actually needed, so in this case, it looks like you'd need, ....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))... within the AGGREGATE.– Nathan_Sav
Mar 27 at 11:00
Do 46mm and 90mm do the same thing? I would recommend changing the
IF part to where the it is actually needed, so in this case, it looks like you'd need, ....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))... within the AGGREGATE.– Nathan_Sav
Mar 27 at 11:00
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
For a matrix, you can use
INDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false)) so if I wanted to know how much customer1 pays for a drill, I could say INDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false)) with customers being in column A and the header for products in row 1– Nathan_Sav
Mar 27 at 11:07
For a matrix, you can use
INDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false)) so if I wanted to know how much customer1 pays for a drill, I could say INDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false)) with customers being in column A and the header for products in row 1– Nathan_Sav
Mar 27 at 11:07
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%2f55375430%2fvba-function-query-using-if-formula%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
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
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%2f55375430%2fvba-function-query-using-if-formula%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
Does it work when there is data in G25
– Nathan_Sav
Mar 27 at 10:56
Do 46mm and 90mm do the same thing? I would recommend changing the
IFpart to where the it is actually needed, so in this case, it looks like you'd need,....IF(OR(G25="46mm",G25="90mm"),(Matrix!$C$5:$G$5>=I25),(Matrix!$C$5:$K$5>=I25))...within the AGGREGATE.– Nathan_Sav
Mar 27 at 11:00
The formula works as a formula, but generates a #value! error of there's no data in the G25:g56 range.
– KeelySG
Mar 27 at 11:02
It's basically a price matrix for something that is 46mm and 90mm, so it's looking up two different prices
– KeelySG
Mar 27 at 11:03
For a matrix, you can use
INDEX(matrix,MATCH(yValue,column1,false),MATCH(xHeader,column1,false))so if I wanted to know how much customer1 pays for a drill, I could sayINDEX(A1:X1000,MATCH("Customer1",a1:a100,false),MATCH("Drill",a1:x1,false))with customers being in column A and the header for products in row 1– Nathan_Sav
Mar 27 at 11:07