Copy paste code for a #REF

[LiAD]

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

 

[Mike H]

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]

Perfect

thanks

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