0

I have found a interesting error. I have to work with a Sheet made for a third-party program. Some cells look to be damaged. They are filled with # symbol. Excel shows negative numbers in the formula bar window, and They should be dates.

In VBA, I can´t neither see it´s content. A simple statement: Range("cell adress").value causes the overflow error 6. The same happens when I try to see it´s type.

As I have to read all the Sheet, cell by cell, the only solution I have imagined is manage this error. Some thing like this: On error resume next - if err > 0 ... etc.

Is it possible manage these cells without turn off the run time error?

Improve this question
1
  • 1904 date system maybe? support.microsoft.com/kb/180162 or could you convert your cell format to number? Commented Nov 10, 2013 at 21:40

1 Answer 1

Reset to default
1

As Sam Ward alluded to, the reason for the ###'s in a cell that "should be a date" but shows a negative number is that you are using the 1900 date system, and have a negative value, which Excel cannot show.

In addition, if you are getting an overflow error in VBA when referencing the value property of that cell, the value in that cell is likely less than -657434. (Which equates to 1 Jan 100 which is the lower end of the date range that VBA can handle).

If the above surmises are the case, you can recover the value in that cell, using VBA, by accessing the value2 property.

Range("cell address").value2

It is certainly not clear why you should have a date formatted cell containing that kind of value, so your worksheet may have other problems.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.