[Solved] Forcing a calculation

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
Anyone know of a way to use code to populate a range of cells (A1:C3) with an equation that references the same number of cells (A10:C12), then deletes the equations while maintaining the values in each cell produced by the equations before they were deleted.

The equation (“=source cell”) needs to be a consecutive reference (like when you drag it). For example, the equations for A1:D3 before they get deleted from the cells would be =A10, =B10, =C10, =A11, =B11, =C11, etc for cells A1, B1, C1, A2, B2, C2, etc respectively. I need the cells to actually calculate the value through the populated equations before they get deleted. It can’t just be a copy and paste thing.

My idea was to create some kind of loop that goes to each cell, inserts the equation, stores the resulting value in a variable, deletes the contents from the cell (i.e. the equation), then places the stored value from the variable into the cell. However, I don’t know how to code this or even if my logic is correct.

The event that will trigger this code (if I can fid something that works) is going to be a button click.

Thanks for the help.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Those equations would just be duplicating the values from A10:C12 into A1:C3, why can't it be a copy and paste thing?
 

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
Okay, disregard. I got one problem figured out so now it can be a cut and paste. I think I have this figured out. Here’s what I’m going to use:

Code:
Sub Refresh()
    Range("D55:S58").Select
    Selection.Copy

    Range("D5:S8").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
End Sub

Thanks for the help.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You don't need to select ranges in order to work with them, that could be shortened to this:

Code:
Sub Refresh()
Range("D55:S58").Copy
Range("D5:S8").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
 

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
Thank you very much. The way I've been trying to figure out the code is to open VBA, record a macro, note the code that appears as I do stuff, then cut and paste what I need for certain apps. It's probably time for me to go buy a book or something. However, thanks to people like yourself, this website is actually more valuable I think.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,060
Members
412,763
Latest member
sienweiw
Top