[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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,620
Messages
5,832,734
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top