Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste code for a #REF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste code for a #REF
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste code | Excel Programming | |||
copy and paste using vb code | Excel Discussion (Misc queries) | |||
Got the Copy/Cut Code But what is the Paste Code | Excel Programming | |||
Help-Copy&Paste code | Excel Programming | |||
Need Help with Code - Copy & Paste | Excel Programming |