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

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