[Solved] Forcing a calculation

[BrianDP1977]

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.

 

[Scott Huish]

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]

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:

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]

You don't need to select ranges in order to work with them, that could be shortened to this:

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

 

[BrianDP1977]

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.