Drag down auto fill for a user defined function VBA
-1
I have the following function:
Function myFunc() As String
Application.Volatile
Dim i As Long, lTotal As Long
For i = 3 To 10
If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
Cells(i, 4).Value And _
Range("I3").Value >= Cells(i, 3).Value Then
lTotal = Cells(i, 5).Value + lTotal
End If
myFunc = lTotal
Next i
End Function
I want this function to be autofill when I drag it down the column. How can I do that?
[Edit]
Attached is the image.
Consider the Dasboard table as a discrete table where manually entries are posted. Consider log table as a continues table where record of every hour for each date is kept. The entries from Dashboard table get posted to the log table.
I am using this function in the output column in the log table. I want to be able to drag it down to fill the log table.
excel vba excel-formula
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
|
show 2 more comments
-1
I have the following function:
Function myFunc() As String
Application.Volatile
Dim i As Long, lTotal As Long
For i = 3 To 10
If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
Cells(i, 4).Value And _
Range("I3").Value >= Cells(i, 3).Value Then
lTotal = Cells(i, 5).Value + lTotal
End If
myFunc = lTotal
Next i
End Function
I want this function to be autofill when I drag it down the column. How can I do that?
[Edit]
Attached is the image.
Consider the Dasboard table as a discrete table where manually entries are posted. Consider log table as a continues table where record of every hour for each date is kept. The entries from Dashboard table get posted to the log table.
I am using this function in the output column in the log table. I want to be able to drag it down to fill the log table.
excel vba excel-formula
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01
|
show 2 more comments
-1
-1
-1
I have the following function:
Function myFunc() As String
Application.Volatile
Dim i As Long, lTotal As Long
For i = 3 To 10
If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
Cells(i, 4).Value And _
Range("I3").Value >= Cells(i, 3).Value Then
lTotal = Cells(i, 5).Value + lTotal
End If
myFunc = lTotal
Next i
End Function
I want this function to be autofill when I drag it down the column. How can I do that?
[Edit]
Attached is the image.
Consider the Dasboard table as a discrete table where manually entries are posted. Consider log table as a continues table where record of every hour for each date is kept. The entries from Dashboard table get posted to the log table.
I am using this function in the output column in the log table. I want to be able to drag it down to fill the log table.
excel vba excel-formula
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
I have the following function:
Function myFunc() As String
Application.Volatile
Dim i As Long, lTotal As Long
For i = 3 To 10
If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
Cells(i, 4).Value And _
Range("I3").Value >= Cells(i, 3).Value Then
lTotal = Cells(i, 5).Value + lTotal
End If
myFunc = lTotal
Next i
End Function
I want this function to be autofill when I drag it down the column. How can I do that?
[Edit]
Attached is the image.
Consider the Dasboard table as a discrete table where manually entries are posted. Consider log table as a continues table where record of every hour for each date is kept. The entries from Dashboard table get posted to the log table.
I am using this function in the output column in the log table. I want to be able to drag it down to fill the log table.
excel vba excel-formula
excel vba excel-formula
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
edited Nov 14 '18 at 10:43
Pᴇʜ
21.8k42750
21.8k42750
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
asked Nov 14 '18 at 8:24
Osama MalikOsama Malik
135
135
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01
|
show 2 more comments
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS
=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)
Put the formula in J3 and pull it down.
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
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%2f53295794%2fdrag-down-auto-fill-for-a-user-defined-function-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
0
Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS
=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)
Put the formula in J3 and pull it down.
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
add a comment |
0
Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS
=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)
Put the formula in J3 and pull it down.
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
add a comment |
0
0
0
Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS
=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)
Put the formula in J3 and pull it down.
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
Why to use a UDF? Don't re-invent the wheel. You can just do that with SUMIFS
=SUMIFS($E$3:$E$10,$B$3:$B$10,"=" & H3,$C$3:$C$10,"<=" & I3,$D$3:$D$10,">" & I3)
Put the formula in J3 and pull it down.
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
edited Nov 14 '18 at 9:43
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
answered Nov 14 '18 at 9:38
PᴇʜPᴇʜ
21.8k42750
21.8k42750
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
add a comment |
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
That can not be used as in that case we have to write 8 different criterias for Sumif in J3 for every entry in the dashboard table. And this will not be possible if I go for more entries.
– Osama Malik
Nov 14 '18 at 9:45
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
@OsamaMalik Well it does exactly what your code does now. I don't see a difference. And I don't see why you would need 8 different criteria. Just pull the formula down it worked in my testing.
– Pᴇʜ
Nov 14 '18 at 10:01
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
It seems to be working yes. Let me apply it to original data. If it works you just saved my day ^_^. Ill mark tick in a minute
– Osama Malik
Nov 14 '18 at 10:02
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%2f53295794%2fdrag-down-auto-fill-for-a-user-defined-function-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
What do you mean by autofill? Should it start with the same row as the one you want to fill? Should it always loop over next 7 rows? Maybe it fills cells but with incorrect values? Describe what is your goal
– barbsan
Nov 14 '18 at 8:51
An UDF only returns something to the cell it is called from. Use a sub if impacting on more than calling cell.
– QHarr
Nov 14 '18 at 8:53
Hard-coding input cells is a bad idea if you want the reference to change as you drag it down. Can you tell us what you would like this function to do and why does it return a string if it tries to add a few numbers? Cheers
– Michal Rosa
Nov 14 '18 at 8:55
barbsan Yes it should start with the same row as I want to fill. Yes the loop is fixed. I have edited the question to be more exact.
– Osama Malik
Nov 14 '18 at 8:57
QHarr cant use a sub as in that case I cant drag it down. then I have to manually make a macro for every row in the column.
– Osama Malik
Nov 14 '18 at 9:01