Copy paste code for a #REF
Hi,
Try this with the worksheet changed to suit. Note that changing a formula to
UCASE has the effect of converting it to a value
Sub sonic()
Dim ws As Worksheet
Set ws = Sheets("Sheet1") 'Change to suit
lastrow = ws.Cells(Cells.Rows.Count, "H").End(xlUp).Row
Set MyRange = ws.Range("H1:H" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) Then
c.Value = UCase(c.Value)
End If
Next
End Sub
Mike
"LiAD" wrote:
Hi,
I am looking for a workbook code that runs when the file is opened. In col
H I have a formula (=INDIRECT(G8)) which searches data from another series of
folders. If the source folder is open it returns either a value or 0. If
the relevant source file is closed it returns a #REF.
What I would like is a code that looks through all the cells in Col H, if it
finds a numerical value or 0 it does a copy/paste values on the cells, if it
finds a #REF it does nothing.
The file is a update folder so the REFs will be in the future and the values
in the current/past which i need to keep to stop updates and allow the values
to be viewed irrespective of which folders are opened/closed.
Is this possible?
Thanks
LiAD
|