Turning raw data into formulas

[CMoody]

We rececently added an SAP expense forecasting tool, which runs using Excel. While the tool is still being developed to forecast, I am using SAP & hard-keying in data to create a actuals+forecast view. Trouble is, when I "refresh" the SAP app. all of the data I hard-keyed in changes (this is desireable on a monthly basis, but I want to preserve future months forecast). In order to preserve the future month's data, I need to make all of the hard-keyed #'s equal formulas...(SAP will leave those #'s alone).

Is there a shortcut to take a data set and transform it to have a equal sign in front of it, without manually inserting it?

Your help will save me hours of inserting equal signs before my forecast that I want to preserve! Thanks!

 

[VoG]

Hello and welcome to MrExcel.

Try selecting the range and running this macro:

Sub test()
Dim c As Range
For Each c In Selection
    If Not c.HasFormula Then c.Formula = "=" & c.Value
Next c
End Sub

 

[gauntletxg]

Perhaps something like this. Select your range and run the subroutine

Sub AddEqual()
Dim c As Range

For Each c In Selection
    If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then c.Formula = "=" & c.Value
Next
End Sub

 

[CMoody]

Thanks for the quick response. Unfortunately, I do not know how to run a macro. I am looking through Excel Help and following the "how to run a macro" directions. Is there a quick way to do this?

 

[gauntletxg]

From Excel do Alt + F11. Find your workbook on the left-hand side, highlight it, then right click > insert module. In the module paste the code. Go to your worksheet and select your range, go back to the module, click inside the subroutine, and hit F5.

 

[CMoody]

IT worked! YES! YES! YES!

Thank you!!