Something odd - formula turns into value

uk_dave&gill

Board Regular
Joined
May 17, 2007
Messages
79
I seem to be having strange things going on with Excel at present. First it was formulas that didn't appear anywhere (even in the formula bar) until they had done their calculations, now I've got a new one....

I just entered a fairly simply formula

=COUNTIF(U9:U500,"a")

which worked well and gave the result of 138, as expected. But then I clicked into the cell I'd just entered the formula in, and, instead of showing the formula, it showed "138"...

So, somehow it's working as a "once only" calculation?

Can anyone help?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
If you right click the sheet name tab and choose "View Code", do you see anything?
 
Upvote 0
That's interesting! I expected to answer "no", but I have this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, cl As Range
'Change "a:a" to your Range
Set myRng = Intersect(Target, Range("h:i"))
If myRng Is Nothing Then Exit Sub
With Application
.ScreenUpdating = False: .EnableEvents = False
End With
For Each cl In myRng
cl.Value = StrConv(cl.Value, vbProperCase)
Next
With Application
.ScreenUpdating = True: .EnableEvents = True
End With
Set myRng = Nothing
End Sub


I noticed that I had the effect that's puzzling me on columns H and I, and also notice that they are in the code above! Now I'm puzzling as to why the code is there in the first place!! I can't remember if it's pertinent to this workbook, or if I created this one from a copy of another! Also not sure what the code does!

My head is starting to hurt....
 
Upvote 0
Well, if you don't think it is relevant to this workbook/worksheet, then you might consider deleting it (or at least commenting it out) because that is what is cauing your problem.
 
Upvote 0
Well, if you don't think it is relevant to this workbook/worksheet, then you might consider deleting it (or at least commenting it out) because that is what is cauing your problem.

I'll copy the workbook and try deleting it to see what happens! If Vog sees this thread, then he might know what it does, as I am in no doubt whatsoever that I didn't write it, and I've asked him for help on code in the past, so it's probably his!
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top