Change a formula in vba code, How do you

Hi,

I have a spreadsheet that adds each days purchases and each days sales et sums them, until the inventory is posted.

The formula in each cell going down the sheet is like:=bi9+bf10, with bi9 having the current sum and bf10 having the current days sales or purchases. Lets say the next days formula is in cell bf11. I need to change the formula in bf11 to =bi11.

Note: the store can post the inventory every day if they wanted to, so I have no way of knowing what line the current inventory is posted on, nor what day of the month it corresponds to.

Below is the code for the Deli department inventory sheet with the lines in yellow that need to change the formula. All these lines of code do at the moment is erase the formula because of course, the current days sales or purchases will not be posted until tomorrow.

Pertenent code is between Line3 and Line4

sorry am novice, not to good at doing remarks

Thanks Chrstdvd

Private Sub CMDFININSH_Click()
If FRMMARK.Caption = "POST INVENTORY" Then
POSTINVENTORY
Unload FRMMARK
Exit Sub
End If
Dim D As Integer
Unload FRMMARK
Date = Sheet2.LBLDATE
D = Format(Date, "d")

Workbooks.Open FileName:="C:\Slip In\Thisstore\REDBOOK.XLS"

Windows("SLIP_IN.XLS").Activate
Range("T1").Select
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("A4").Select
ActiveCell.Offset(D, 6).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False



Windows("SLIP_IN.XLS").Activate
Range("T2").Select
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("L4").Select
ActiveCell.Offset(D, 6).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows("SLIP_IN.XLS").Activate
Range("T3").Select
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("W4").Select
ActiveCell.Offset(D, 6).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("SLIP_IN.xls").Activate
Range("T1:T6") = ""
Range("A1").Select

End Sub
Private Sub INVENTORYENTER()

WHAT_DEPARTMENT

Unload FRMMARK

Load FRMMARK
FRMMARK.CB1.AddItem "MERCH"
FRMMARK.CB1.AddItem "CIGS"
FRMMARK.CB1.AddItem "BEER"
FRMMARK.CB1.AddItem "DELI"
FRMMARK.CB1.AddItem "LOTRY"
FRMMARK.CB1.AddItem "PHONE"
FRMMARK.Caption = "POST INVENTORY"
FRMMARK.Show
End Sub
Private Sub POSTINVENTORY()
Dim D As Integer
Dim datadate As Date
datadate = Sheet2.LBLDATE
D = Format(datadate, "d") - 1
If D = 0 Then
'Call LAST_MONTH
datadate = datadate - 1
D = Format(datadate, "d") - 1
Windows("SLIP_IN.XLS").Activate
Range("T4").Select
If Range("T4") = "" Then
Exit Sub
End If
Selection.Copy
Call LAST_MONTH
Unload FRMMARK
Exit Sub
GoTo LINE20
End If
Workbooks.Open FileName:="C:\Slip In\thisstore\REDBOOK.XLS"
LINE20:
Windows("SLIP_IN.XLS").Activate
Range("T1").Select
If Range("T1") = "" Then
GoTo LINE1
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("A4").Select
ActiveCell.Offset(D, 9).Select 'MERCH
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value
ActiveCell.Offset(1, -8).Value = ActiveCell.Value + ActiveCell.Offset(1, -8).Value
LINE1:
Windows("SLIP_IN.XLS").Activate
Range("T2").Select
If Range("T2") = "" Then
GoTo LINE2
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("L4").Select
ActiveCell.Offset(D, 9).Select 'CIGS
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value
ActiveCell.Offset(1, -8).Value = ActiveCell.Value + ActiveCell.Offset(1, -8).Value
LINE2:
Windows("SLIP_IN.XLS").Activate
Range("T3").Select
If Range("T3") = "" Then
GoTo LINE3
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("W4").Select
ActiveCell.Offset(D, 9).Select 'BEER
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value
ActiveCell.Offset(1, -8).Value = ActiveCell.Value + ActiveCell.Offset(1, -8).Value
LINE3:
Windows("SLIP_IN.XLS").Activate
Range("T4").Select
If Range("T4") = "" Then
GoTo LINE4
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("BD4").Select
ActiveCell.Offset(D, 7).Select 'DELI
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ENDINGINVENTORY = ActiveCell.Value
x = 1
For x = 1 To D - 1
ActiveCell.Offset(-1, 0).Select
If Not ActiveCell.Value = "" Then
GoTo LINE10
End If
Next
LINE10:

GOODSAVAILABLE = 0
GOODSAVAILABLE = GOODSAVAILABLE + ActiveCell.Value
Range("BD4").Select
ActiveCell.Offset(D, 6).Select
ActiveCell.Value = GOODSAVAILABLE + ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(O, 1).Select
'ENDING INVENTORY FORWARD
ActiveCell.Offset(1, -6).Value = ActiveCell.Value
'FIGURE COST OF GOODS SOLD
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Value
'RESET SALES
ActiveCell.Offset(1, 2).Value = ActiveCell.Offset(1, -4).Value

'FIGURE PROFIT
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value - ActiveCell.Offset(0, 1).Value
'RESET PURCHASES
ActiveCell.Offset(1, -2).Value = ActiveCell.Offset(1, -5).Value
Range("BK36") = ENDINGINVENTORY
'PRINT
Range("BD1:BO36").Select
ActiveWindow.Selection.PrintOut Copies:=3
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("SLIP_IN.xls").Activate
Range("T1:T6") = 0
Range("A1").Select
Exit Sub
LINE4:
Windows("SLIP_IN.XLS").Activate
Range("T5").Select
If Range("T5") = "" Then
GoTo LINE5
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("AS4").Select
ActiveCell.Offset(D, 9).Select 'LOTTERY
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(1, -9).Value
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, -8).Value = ActiveCell.Value
ActiveCell.Offset(1, -7).Value = ActiveCell.Value
LINE5:
Windows("SLIP_IN.XLS").Activate
Range("T6").Select
If Range("T6") = "" Then
GoTo LINE6
End If
Selection.Copy
Windows("REDBOOK.XLS").Activate
Range("AH4").Select
ActiveCell.Offset(D, 9).Select 'PHONE CRDS
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value
ActiveCell.Offset(1, -8).Value = ActiveCell.Value + ActiveCell.Offset(1, -8).Value
LINE6:
Windows("REDBOOK.XLS").Activate
Range("A1").Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1

ActiveWorkbook.Save
ActiveWorkbook.Close

Windows("SLIP_IN.xls").Activate
Range("T1:T6") = ""
Range("A1").Select

End Sub