disapearing formula XL2000 windows XP

A

~Alan

I am using the below  formula that returns the correct value but then the formula disapears,
goes away, never to be used again.
I know it must be a simple solution that I am over looking.

=MID($BZ$2,COLUMN()-32-8,1)
 
K

Ken Wright

OK, you are probably going to have to explain a bit more. Firstly, the -32-8 is
redundant and can be replaced with 40, and I am assuming you don't use this on
any column prior to Column AO as it would error out. That having been said,
there is no reason why a formula would just disappear, unless you perhaps had
some event macro that converted to values for some reason. I am assuming you
mean that in the formula bar you no longer see the formula, so what EXACTLY
happens, step by step as you do this?
 
A

~Alan

thank you i solved it
 

~Alan wrote: I am using the below  formula that returns the correct value but then the formula disapears,
goes away, never to be used again.
I know it must be a simple solution that I am over looking.

=MID($BZ$2,COLUMN()-32-8,1)
 
A

~Alan

I am going to try to explain and yes it starts at AO
I have a form that has 57 columns in ten of those columns is a row of cells that I need to put in one character only
to the left of the form I have a list of cells that I use to fill in the form with. most of them are ( = cell) or a small macro I find it time consuming to place a character in a cell and then hit the tab key so I have been using =MID($BZ$2,COLUMN()-40,1)  now I can get it to give me the characters that I need but then the formula just goes away as in not there I thought it was because I used the left click on another cell instead of hitting the enter key but the problem has returned
Could it be this sheet code
'Private Sub Worksheet_Change(ByVal Target As Range)
 '   'If Target.Cells.Count > 1 Then Exit Sub
  'If Intersect(Target, Me.Range("A:ca")) Is Nothing Then Exit Sub
   'On Error GoTo CleanUp:
    'With Target
     '   If .Value <> "" Then"
            'Application.EnableEvents = False
      '      .Value = UCase(.Value)
       ' End If
     'End With
'CleanUp:
 'Application.EnableEvents = True
 'End Sub
 

Ken Wright wrote: OK, you are probably going to have to explain a bit more.  Firstly, the -32-8 is
redundant and can be replaced with 40, and I am assuming you don't use this on
any column prior to Column AO as it would error out.  That having been said,
there is no reason why a formula would just disappear, unless you perhaps had
some event macro that converted to values for some reason. I am assuming you
mean that in the formula bar you no longer see the formula, so what EXACTLY
happens, step by step as you do this?
 
K

Ken Wright

Hmmm, not if exactly as shown because it has all been commented out, but yes
something like that that runs through your sheets and turns all formulas to
values is likley to be the culprit. That piece of code is designed to look at
whatever you have entered and then convert it to uppercase, but it doesn't trap
for whether or not there is a formula in the cell, and converts everything to a
value regardless. There is also an error in the line with a superfluous set of
quotes after the word Then.

Replacing it with the following should work though:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:CA")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" And .HasFormula = False Then
Application.EnableEvents = False
.Value = UCase(.Value)
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



~Alan said:
I am going to try to explain and yes it starts at AO
I have a form that has 57 columns in ten of those columns is a row of cells
Click to expand...
that I need to put in one character only
to the left of the form I have a list of cells that I use to fill in the form
Click to expand...
with. most of them are ( = cell) or a small macro I find it time consuming to
place a character in a cell and then hit the tab key so I have been using
=MID($BZ$2,COLUMN()-40,1) now I can get it to give me the characters that I
need but then the formula just goes away as in not there I thought it was
because I used the left click on another cell instead of hitting the enter key
but the problem has returned
 
Top