L
lostinformulas
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
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