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

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

Perfect

thanks

"Mike H" wrote:

> 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