Converting a Cell Formula into VBA

Question

I am looking to convert a cell formula into VBA code so users will still be able to edit a cell without removing the formula. The formula i have got so far is below but it prevents the options to add a new value into the cell. I have tried a couple of things like mirroring the cell in VBA but i am very new to this!

=E9-SUMIF('Charge Sheet'!I:I,B9,'Charge Sheet'!H:H)

Sheet 1 (Charge Sheet) -

F) Cost Centre - pulled from sheet 3

G) Job Code

H) Quantity

I) Equipment - pulled from Name in Sheet 2

J) Cost - Pulled from Unit Price (£) in Sheet 2 and multiplied by the Quantity

K) Support Staff - Updated using VBA to display the user that made the order when the other fields are filled

L) Date Issued - Updated using VBA to display the date when the order was made when the other fields are filled

Sheet 2 (Stock Levels) -

A) Item ID

B) Name

C) Unit Price (£)

D) Quantity in Stock - the cell i want to be updated when a certain item is ordered in Sheet 1

E) Re-order Level - If the Quantity in Stock falls beneath this stock level then an automatic email is sent to request re-order

Thanks

Gary's Student Gary's Student · Accepted Answer · 2014-04-11T12:58:27

If you already have the formula, then you can just evaluate it in VBA

Sub dural()
    Dim s As String, x As Variant
    s = "E9-SUMIF('Charge Sheet'!I:I,B9,'Charge Sheet'!H:H)"
    x = Evaluate(s)
End Sub

This assumes that the proper sheet is already active.

Converting a Cell Formula into VBA

1 Answers