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

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

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("A150") '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