Changing a formula to value only
 

I have a spreadsheet that shows stockholding figures on a day to day basis. I
have a row with a date in each column for each day of the week, and in the
respective columns beneath each date i have formulae which look to a
different spreadsheet (with a date stamp), to update the information in a
number of cells in the column which the date matches. At the moment i have to
manually highlight the updated cells and convert the formulae to values, so
they don't reset to zero the following day because the date doesn't match.
Is there a way to run a macro, which will look at a range of cells in one
row, and if it comes across a cell in that range which has a figure greater
than zero, will select a set number of cells above this figure to convert the
formulae to values ?
Any help would be appreciated

Changing a formula to value only
 

If Range("A1").HasFormula = True and Range("A1").Value 0 Then
Range("A1").Value = Range("A1").Value
End If

"gcouch" wrote:

I have a spreadsheet that shows stockholding figures on a day to day basis. I
have a row with a date in each column for each day of the week, and in the
respective columns beneath each date i have formulae which look to a
different spreadsheet (with a date stamp), to update the information in a
number of cells in the column which the date matches. At the moment i have to
manually highlight the updated cells and convert the formulae to values, so
they don't reset to zero the following day because the date doesn't match.
Is there a way to run a macro, which will look at a range of cells in one
row, and if it comes across a cell in that range which has a figure greater
than zero, will select a set number of cells above this figure to convert the
formulae to values ?
Any help would be appreciated

Changing a formula to value only
 

This would check a range of cells and set values.
Change the sheet and range reference to your requirement.

Sub valequval()
Dim c As Range
Set myRng = Worksheets(1).Range("A1:D50") 'Adjust to actual
For Each c In myRng
If c.HasFormula = True And c.Value 0 Then
c.Value = c.Value
End If
Next
End Sub

"gcouch" wrote:

I have a spreadsheet that shows stockholding figures on a day to day basis. I
have a row with a date in each column for each day of the week, and in the
respective columns beneath each date i have formulae which look to a
different spreadsheet (with a date stamp), to update the information in a
number of cells in the column which the date matches. At the moment i have to
manually highlight the updated cells and convert the formulae to values, so
they don't reset to zero the following day because the date doesn't match.
Is there a way to run a macro, which will look at a range of cells in one
row, and if it comes across a cell in that range which has a figure greater
than zero, will select a set number of cells above this figure to convert the
formulae to values ?
Any help would be appreciated