vba copy formula to multiple cells
I know the ways of using ".PasteSpecial xlPasteFormulas" or "range.AutoFill", but I try to find a way to use the array variable.
I want to copy the formulas in range C4:D4 to the range C7:D11 which has multiple range.
C4 = A4+B4
D4 = Average(A4:C4)
So I made a vba script like this.
Sub test()
Dim v
v = Range("C4:D4").FormulaR1C1
Range("C7:D11").FormulaR1C1 = v
End Sub
After running the vba, the formulas in c7 and d7 were like this, as I expected.
c7 = a7 + b7
d7 = average(A7:C7)
but the other cells' formulas were strange
c8 = a9 + b9
d8 = average(A9:C9)
c9 = a11 + b11
d9 = average(A11:C11)
and so on.....
My questions are:
1. Why is this happening?
2. Any suggestion about the way of copying some formulas to multiple range by the way of using the array variable?
Thank you in advance.
arrays excel formula
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
add a comment |
I know the ways of using ".PasteSpecial xlPasteFormulas" or "range.AutoFill", but I try to find a way to use the array variable.
I want to copy the formulas in range C4:D4 to the range C7:D11 which has multiple range.
C4 = A4+B4
D4 = Average(A4:C4)
So I made a vba script like this.
Sub test()
Dim v
v = Range("C4:D4").FormulaR1C1
Range("C7:D11").FormulaR1C1 = v
End Sub
After running the vba, the formulas in c7 and d7 were like this, as I expected.
c7 = a7 + b7
d7 = average(A7:C7)
but the other cells' formulas were strange
c8 = a9 + b9
d8 = average(A9:C9)
c9 = a11 + b11
d9 = average(A11:C11)
and so on.....
My questions are:
1. Why is this happening?
2. Any suggestion about the way of copying some formulas to multiple range by the way of using the array variable?
Thank you in advance.
arrays excel formula
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
add a comment |
I know the ways of using ".PasteSpecial xlPasteFormulas" or "range.AutoFill", but I try to find a way to use the array variable.
I want to copy the formulas in range C4:D4 to the range C7:D11 which has multiple range.
C4 = A4+B4
D4 = Average(A4:C4)
So I made a vba script like this.
Sub test()
Dim v
v = Range("C4:D4").FormulaR1C1
Range("C7:D11").FormulaR1C1 = v
End Sub
After running the vba, the formulas in c7 and d7 were like this, as I expected.
c7 = a7 + b7
d7 = average(A7:C7)
but the other cells' formulas were strange
c8 = a9 + b9
d8 = average(A9:C9)
c9 = a11 + b11
d9 = average(A11:C11)
and so on.....
My questions are:
1. Why is this happening?
2. Any suggestion about the way of copying some formulas to multiple range by the way of using the array variable?
Thank you in advance.
arrays excel formula
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
I know the ways of using ".PasteSpecial xlPasteFormulas" or "range.AutoFill", but I try to find a way to use the array variable.
I want to copy the formulas in range C4:D4 to the range C7:D11 which has multiple range.
C4 = A4+B4
D4 = Average(A4:C4)
So I made a vba script like this.
Sub test()
Dim v
v = Range("C4:D4").FormulaR1C1
Range("C7:D11").FormulaR1C1 = v
End Sub
After running the vba, the formulas in c7 and d7 were like this, as I expected.
c7 = a7 + b7
d7 = average(A7:C7)
but the other cells' formulas were strange
c8 = a9 + b9
d8 = average(A9:C9)
c9 = a11 + b11
d9 = average(A11:C11)
and so on.....
My questions are:
1. Why is this happening?
2. Any suggestion about the way of copying some formulas to multiple range by the way of using the array variable?
Thank you in advance.
arrays excel formula
arrays excel formula
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
asked Jan 1 at 3:41
Kevin KimKevin Kim
667
667
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in
Setting the formula of a multiple-cell range fills all cells in the
range with the formula.
the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.
So the only way seems to be doing this in two parts:
Sub test()
Dim v As Variant
v = Range("C4").FormulaR1C1
Range("C7:C11").FormulaR1C1 = v
v = Range("D4").FormulaR1C1
Range("D7:D11").FormulaR1C1 = v
End Sub
This should still be faster than using copy/paste via clipboard or setting the formulas using loops.
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
add a comment |
It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.
I think you can fix it by doing this:
for x = 7 To 11
Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x
If this doesn't work you can try writing the formulas in the vba code.
example:
for x = 7 To 11
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x
answered Jan 1 at 21:59
JaneJane
321211
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%2f53992878%2fvba-copy-formula-to-multiple-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in
Setting the formula of a multiple-cell range fills all cells in the
range with the formula.
the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.
So the only way seems to be doing this in two parts:
Sub test()
Dim v As Variant
v = Range("C4").FormulaR1C1
Range("C7:C11").FormulaR1C1 = v
v = Range("D4").FormulaR1C1
Range("D7:D11").FormulaR1C1 = v
End Sub
This should still be faster than using copy/paste via clipboard or setting the formulas using loops.
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
add a comment |
Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in
Setting the formula of a multiple-cell range fills all cells in the
range with the formula.
the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.
So the only way seems to be doing this in two parts:
Sub test()
Dim v As Variant
v = Range("C4").FormulaR1C1
Range("C7:C11").FormulaR1C1 = v
v = Range("D4").FormulaR1C1
Range("D7:D11").FormulaR1C1 = v
End Sub
This should still be faster than using copy/paste via clipboard or setting the formulas using loops.
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
add a comment |
Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in
Setting the formula of a multiple-cell range fills all cells in the
range with the formula.
the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.
So the only way seems to be doing this in two parts:
Sub test()
Dim v As Variant
v = Range("C4").FormulaR1C1
Range("C7:C11").FormulaR1C1 = v
v = Range("D4").FormulaR1C1
Range("D7:D11").FormulaR1C1 = v
End Sub
This should still be faster than using copy/paste via clipboard or setting the formulas using loops.
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in
Setting the formula of a multiple-cell range fills all cells in the
range with the formula.
the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.
So the only way seems to be doing this in two parts:
Sub test()
Dim v As Variant
v = Range("C4").FormulaR1C1
Range("C7:C11").FormulaR1C1 = v
v = Range("D4").FormulaR1C1
Range("D7:D11").FormulaR1C1 = v
End Sub
This should still be faster than using copy/paste via clipboard or setting the formulas using loops.
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
edited Jan 1 at 8:03
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
answered Jan 1 at 7:05
Axel RichterAxel Richter
25.7k21936
25.7k21936
add a comment |
add a comment |
It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.
I think you can fix it by doing this:
for x = 7 To 11
Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x
If this doesn't work you can try writing the formulas in the vba code.
example:
for x = 7 To 11
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x
answered Jan 1 at 21:59
JaneJane
321211
add a comment |
It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.
I think you can fix it by doing this:
for x = 7 To 11
Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x
If this doesn't work you can try writing the formulas in the vba code.
example:
for x = 7 To 11
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x
answered Jan 1 at 21:59
JaneJane
321211
add a comment |
It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.
I think you can fix it by doing this:
for x = 7 To 11
Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x
If this doesn't work you can try writing the formulas in the vba code.
example:
for x = 7 To 11
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x
answered Jan 1 at 21:59
JaneJane
321211
It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.
I think you can fix it by doing this:
for x = 7 To 11
Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x
If this doesn't work you can try writing the formulas in the vba code.
example:
for x = 7 To 11
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x
answered Jan 1 at 21:59
JaneJane
321211
answered Jan 1 at 21:59
JaneJane
321211
answered Jan 1 at 21:59
JaneJane
321211
answered Jan 1 at 21:59
JaneJane
321211
321211
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.
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%2f53992878%2fvba-copy-formula-to-multiple-cells%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
