Autofill not working for a user defined function in excel
I have written a user defined function in VBA. I have included the function under module folder in VB Editor of the excel. The function intends to calculate a value for a cell (F column in Excel worksheet) in each row by comparing the values present in other cells of that row like C1, D1 & E1. The function calculates the values for F column by performing a set of If conditions(around 20 If conditions) based on the combination of values present in C column, D column and E column.
The written function returns the calculated value when I include the function as a formula in F1 cell (i.e. =calculateValue()). The function takes no input parameters. Based on the active row, it calculates the value and returns. But the autofill statement I have written does not populate the value for other cells of F column when I double click the F1 cell (after having invoked the user defined function). It copies the same value as in F1 to the rest of other cells of F column. It seems other cells of F column are not taking their respective active row into considering when invoking the function. Can you please help me correct this function?
Set targetRange = Range(StartRange, StartRange.Offset(0, -1).End(xlDown).Offset(0, 1))
Range("F1").AutoFill Destination:=targetRange
excel vba excel-vba function
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
add a comment |
I have written a user defined function in VBA. I have included the function under module folder in VB Editor of the excel. The function intends to calculate a value for a cell (F column in Excel worksheet) in each row by comparing the values present in other cells of that row like C1, D1 & E1. The function calculates the values for F column by performing a set of If conditions(around 20 If conditions) based on the combination of values present in C column, D column and E column.
The written function returns the calculated value when I include the function as a formula in F1 cell (i.e. =calculateValue()). The function takes no input parameters. Based on the active row, it calculates the value and returns. But the autofill statement I have written does not populate the value for other cells of F column when I double click the F1 cell (after having invoked the user defined function). It copies the same value as in F1 to the rest of other cells of F column. It seems other cells of F column are not taking their respective active row into considering when invoking the function. Can you please help me correct this function?
Set targetRange = Range(StartRange, StartRange.Offset(0, -1).End(xlDown).Offset(0, 1))
Range("F1").AutoFill Destination:=targetRange
excel vba excel-vba function
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07
add a comment |
I have written a user defined function in VBA. I have included the function under module folder in VB Editor of the excel. The function intends to calculate a value for a cell (F column in Excel worksheet) in each row by comparing the values present in other cells of that row like C1, D1 & E1. The function calculates the values for F column by performing a set of If conditions(around 20 If conditions) based on the combination of values present in C column, D column and E column.
The written function returns the calculated value when I include the function as a formula in F1 cell (i.e. =calculateValue()). The function takes no input parameters. Based on the active row, it calculates the value and returns. But the autofill statement I have written does not populate the value for other cells of F column when I double click the F1 cell (after having invoked the user defined function). It copies the same value as in F1 to the rest of other cells of F column. It seems other cells of F column are not taking their respective active row into considering when invoking the function. Can you please help me correct this function?
Set targetRange = Range(StartRange, StartRange.Offset(0, -1).End(xlDown).Offset(0, 1))
Range("F1").AutoFill Destination:=targetRange
excel vba excel-vba function
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
I have written a user defined function in VBA. I have included the function under module folder in VB Editor of the excel. The function intends to calculate a value for a cell (F column in Excel worksheet) in each row by comparing the values present in other cells of that row like C1, D1 & E1. The function calculates the values for F column by performing a set of If conditions(around 20 If conditions) based on the combination of values present in C column, D column and E column.
The written function returns the calculated value when I include the function as a formula in F1 cell (i.e. =calculateValue()). The function takes no input parameters. Based on the active row, it calculates the value and returns. But the autofill statement I have written does not populate the value for other cells of F column when I double click the F1 cell (after having invoked the user defined function). It copies the same value as in F1 to the rest of other cells of F column. It seems other cells of F column are not taking their respective active row into considering when invoking the function. Can you please help me correct this function?
Set targetRange = Range(StartRange, StartRange.Offset(0, -1).End(xlDown).Offset(0, 1))
Range("F1").AutoFill Destination:=targetRange
excel vba excel-vba function
excel vba excel-vba function
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 7:21
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 11 '18 at 17:23
Raghu
111
asked Nov 11 '18 at 17:23
Raghu
111
asked Nov 11 '18 at 17:23
Raghu
111
111
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07
add a comment |
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07
add a comment |
1 Answer
1
active
oldest
votes
Your UDF would work better if you passed in as parameters the values from column C, D and E rather than reading them directly from within the UDF.
=calculateValue(C1,D1,E1)
Then your UDF does not need to know what the active row is (which BTW won't change when you programatically do the fill anyway)
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
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
});
}
});
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%2f53251279%2fautofill-not-working-for-a-user-defined-function-in-excel%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
Your UDF would work better if you passed in as parameters the values from column C, D and E rather than reading them directly from within the UDF.
=calculateValue(C1,D1,E1)
Then your UDF does not need to know what the active row is (which BTW won't change when you programatically do the fill anyway)
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
add a comment |
Your UDF would work better if you passed in as parameters the values from column C, D and E rather than reading them directly from within the UDF.
=calculateValue(C1,D1,E1)
Then your UDF does not need to know what the active row is (which BTW won't change when you programatically do the fill anyway)
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
add a comment |
Your UDF would work better if you passed in as parameters the values from column C, D and E rather than reading them directly from within the UDF.
=calculateValue(C1,D1,E1)
Then your UDF does not need to know what the active row is (which BTW won't change when you programatically do the fill anyway)
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
Your UDF would work better if you passed in as parameters the values from column C, D and E rather than reading them directly from within the UDF.
=calculateValue(C1,D1,E1)
Then your UDF does not need to know what the active row is (which BTW won't change when you programatically do the fill anyway)
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
answered Nov 12 '18 at 9:23
Charles Williams
19.5k52736
19.5k52736
add a comment |
add a comment |
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.
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%2f53251279%2fautofill-not-working-for-a-user-defined-function-in-excel%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
Use a Sub rather than a UDF()
– Gary's Student
Nov 11 '18 at 17:38
Will I be able to invoke sub just like function from a cell within excel? Would it solve the problem that I'm facing? I'm new to VBA programming. Please help
– Raghu
Nov 11 '18 at 17:45
Unless the UDF() returns an array, it can only return a single value to a single cell.
– Gary's Student
Nov 11 '18 at 17:47
support.microsoft.com/en-us/help/170787/…
– Tim Williams
Nov 11 '18 at 23:07