VBA Macro shortcut to "paste special formula"

[aditya8090]

Hi all! I have been trying to write VBA macro to simulate the keystroke (Ctrl + Alt + v + f) to copy and paste formula from an excel cell. I'm a beginner, and so far I've been able to develop the functionality of copying the formula and pasting it relationally. However, the code breaks if it is pasted to a column having more or less elements than the column it was copied for. I've attached a graphical representation for the problem too. Please help me fix this. Thanks, in advance.

Code:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Graphical representation (the formula is just summing up the elements - 16 is the sum of 2 eight times):
A B C D
[TABLE="width: 336"]
<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8
^^ Code breaks above

[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

My question would be:
What Range do you want to sum and where do you want the results entered.

If your going to use Vba why copy the formula?

Vba can just put the results of the formula in a Range

Like this script sums up the Range("A1:A10")
And puts results in Range("B1")

Sub My_Formula()
'Modified 7/15/2019 3:19:44 PM  EDT
Dim ans As Long
ans = Application.WorksheetFunction.Sum(Range("A1:A10"))
Range("B1").Value = ans
End Sub

 

[aditya8090]

Thanks for replying! I am working on a worksheet with a lot of for formulas and It would definitely be easier for me to just copy-paste the formula from the row beside instead of creating custom macros for the different formulas

 

[mikerickson]

Wouldn't this do what you want?

destinationRange.FormulaR1C1= sourceRange.FormulaR1C1

 

[aditya8090]

Hey! Thanks for replying. Can you assist with me how I'm supposed to incorporate it into the code. As of now, this is what I'm doing, and this doesn't seem to work. (I apologize if this is a stupid question, I've just started picking up macros)

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).destinationRange.FormulaR1C1 = SourceRange.FormulaR1C1.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[My Aswer Is This]

So you have code that does not work and you want us to incorporate our code into your code?
And you have not shown us your formula. That would be hard for me to do.

Hey! Thanks for replying. Can you assist with me how I'm supposed to incorporate it into the code. As of now, this is what I'm doing, and this doesn't seem to work. (I apologize if this is a stupid question, I've just started picking up macros)

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, 0).destinationRange.FormulaR1C1 = SourceRange.FormulaR1C1.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[aditya8090]

So you have code that does not work and you want us to incorporate our code into your code?
And you have not shown us your formula. That would be hard for me to do.

Thank for replying! My aim is to copy-paste any arbitrary formula through a simplified key binding, let us call it ctrl + c and ctrl + q. At present, if we want to copy-paste a special formula, we do ctrl + c and then ctrl + alt + v + f. The additional keys is what I want to remove. Talking about the formula, as I mentioned before, it can be any arbitrary formula. The macro doesn't need to worry about that. But for demonstrative purposes we can do sum right now. The main purpose of the macro is to simply copy-paste the special formula, the formula here could be anything (ranging from as simple as a+b to some hardcore finance formula). The macro shouldn't be affected by the kid of formula used. My code, as of now, breaks when I copy-paste a formula for a list having more or less items than the one copied from. For eg, if we have a formula calculating maybe the sum of a list of 10 numbers, if I copy that to a list of 11 numbers or 9 numbers, the macro breaks. Again, the formula used here as an example (of sum) isn't important, the code breaking is.

 

[mikerickson]

The difficulty of your scheme is "where"

Currently,
Select source Range
Ctl+C
Select destination Range
Ctl+q

how do you want the user to designate the source and destination ranges.

 

[aditya8090]

The difficulty of your scheme is "where"

Currently,
Select source Range
Ctl+C
Select destination Range
Ctl+q

how do you want the user to designate the source and destination ranges.

Let us focus more on the pasting part for now. We can do Ctrl + c like we usually do. For clarification, if there is a formula in, say, A10 which the user wishes to copy to B10, he simply goes to A10, enters ctrl + c, goes to B10, enters ctrl + q, and the formula has been pasted relationally.

 

[mikerickson]

Something like

With Selection
    .Offset(0,1).FormualR1C1 = .FormulaR1C1
End With