How can I retain data from a formula?

[PaddyG]

I need a solution so that each day I can get a value from a single cell but it appears in a new column each day.

eg, A1 contains a numerical value (based on a formula)
Each day there is a new column, say, Monday = B1, Tuesday = C1 etc.
On Monday B1 takes the value of A1 and 'freezes' the value in B1
On Tuesday C1 takes the value of A1 and 'freezes' the value in C1, but the value in B1 remains as it was when it was originally frozen.

Any help on this would be greatly appreciated!!

 

[VoG]

That should be possible. Row 1 is using B6's formula. What cells would rows 2, 3 etc. be referencing?

 

[PaddyG]

Hi,

It would be using B7, B8, B9. E5, E6, E7 (and probably more when I get the hang of it).

Thanks,

 

[VoG]

Try

Sub test()
Dim LC As Long, Add
Dim i As Integer
Add = Array("B6", "B7", "B8", "B9", "E5", "E6", "E7")
For i = 1 To UBound(Add) + 1
    LC = Cells(i, Columns.Count).End(xlToLeft).Column
    With Cells(i, LC)
        .Offset(, 1).Formula = Range(Add(i - 1)).Formula
        .Value = .Value
    End With
Next i
End Sub

 

[PaddyG]

Getting some strange results here, so I'm trying to put all things together as follows:

Cells I am taking the value (from a formula) from are E8, E9, E10, E11, E12, E13, E14, E15, E16. And I'm trying to put the value into I8-I16.

I've changed the array so it shows the E8 - 16 but it only puts the value into cells I8 and I9 but these are the values shown in I15 and I16.

I feel I'm doing something a bit daft here.....help!! ;~)

 

[PaddyG]

Hi sorry to pester - I wondered if you could help any more with this? Been tormenting myself with this for the last week ;~)

 

[PaddyG]

sorted!!! Thank you for all your help - brilliant!!