VBA Macro shortcut to "paste special formula"

aditya8090

New Member
Joined
Jul 15, 2019
Messages
6
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
2
311
2311
2311
2311
2311
2311
2311
2311
16
2481
1
1
1
8
^^ Code breaks above

<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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")
Code:
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

New Member
Joined
Jul 15, 2019
Messages
6
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,614
Wouldn't this do what you want?

Code:
destinationRange.FormulaR1C1= sourceRange.FormulaR1C1
 
Last edited:

aditya8090

New Member
Joined
Jul 15, 2019
Messages
6
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 15, 2019
Messages
6
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,614
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

New Member
Joined
Jul 15, 2019
Messages
6
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,614
Something like


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

Watch MrExcel Video

Forum statistics

Threads
1,101,906
Messages
5,483,654
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top