Remove Text from Named Cell Value

grady121

Active Member
Joined
May 27, 2005
Messages
378
I use a worksheet to record my weekly Solar Panel readings. I input the reading onto the worksheet using a Userforms Textbox to add the value to a Named cell "NewReading".

When inputted, the “NewReading” value is simply checked against the named cell above, named "OldReading", to ensure it is a greater value, with a simply error message if not.
e.g. If Range("NewReading").Value <= Range("OldReading").Value Then GoTo TooSmall

If I’m not able to input the reading on the correct day, (Holidays etc) I indicate the reading was “Estimated” by adding an “ E” at the end. This is added to the reading by ticking a Checkbox on the same Userform.
E.g. Range("NewReading") = Range("NewReading").Value & " E"

The trouble I have now is that when trying to add further readings, below the Estimated reading, it show an error because the OldReading.Value now has the “ E” on the end and won’t allow me to proceed.

Can anyone offer a way of getting the NewReading to be checked against the OldReading, without firing the Error message?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

davesweep

Well-known Member
Joined
Apr 30, 2007
Messages
510
Hi,

You could exclude the E...

Code:
Application.WorksheetFunction.Substitute(Range("OldReading").Value,"E","")
 

grady121

Active Member
Joined
May 27, 2005
Messages
378
Hi Dave,
Thanks for your reply.
I can use your suggestion to get the code to work, but the downside is that the original reading loses it's original "E", which I would like to keep.

Hope I'm not asking too much but is there a way to test if the last Character in "OldReading" = "E" ?
If true - create a new value to use in the existing NewReading v OldReading test, else, use the original Named Ranges, so that the old value is retained?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

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
Top