Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disapearing formulas
I have a worksheet that I have been working on for about a week. It near completion and now i have had several times that my formulas are disapearing. I wonder if it could have any thing to do with the VB code that I added I have two different sets of codes on different worksheets. I one I think might be doing it is the Upper Case code. I think i only need one part of it and maybe they are causing problems. I deleted the first code and it seems to work. If someone could look at it and let me know if they think this could be causing it. The second code is on a spread worksheet inside of the same workbook. UPPERCASE UPON ENTER: Sub Uppercase() For Each x In Range("a17:av82") x.Value = UCase(x.Value) Next End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column 50 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub Absolute Reference VB Code: Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelative) End If Next End Sub -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=552507 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disapearing formulas
Hi LostInFormulas,
Try replacing Sub Uppercase() For Each x In Range("a17:av82") x.Value = UCase(x.Value) Next End Sub with '============= Public Sub Uppercase() Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE Application.EnableEvents = False On Error Resume Next Set Rng = SH.Range("A17:AV82"). _ SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo XIT If Not Rng Is Nothing Then For Each rCell In Rng.Cells With rCell .Value = UCase(.Text) End With Next rCell End If XIT: Application.EnableEvents = True End Sub '<<============= --- Regards, Norman "lostinformulas" <lostinformulas.29gv7c_1150413603.0828@excelforu m-nospam.com wrote in message news:lostinformulas.29gv7c_1150413603.0828@excelfo rum-nospam.com... I have a worksheet that I have been working on for about a week. It near completion and now i have had several times that my formulas are disapearing. I wonder if it could have any thing to do with the VB code that I added I have two different sets of codes on different worksheets. I one I think might be doing it is the Upper Case code. I think i only need one part of it and maybe they are causing problems. I deleted the first code and it seems to work. If someone could look at it and let me know if they think this could be causing it. The second code is on a spread worksheet inside of the same workbook. UPPERCASE UPON ENTER: Sub Uppercase() For Each x In Range("a17:av82") x.Value = UCase(x.Value) Next End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column 50 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub Absolute Reference VB Code: Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlRelative) End If Next End Sub -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=552507 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disapearing Excel 2003 files on the newtork | Excel Discussion (Misc queries) | |||
text disapearing in a text box | Charts and Charting in Excel | |||
office 2007 glitch disapearing text | Excel Discussion (Misc queries) | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Saved Excel sheet - information disapearing | Excel Discussion (Misc queries) |