Macro storing as a date?

  • Thread starter Thread starter Wes Finch
  • Start date Start date
W

Wes Finch

Hi,
I need some help with this macro. When I run it I get the yellow explanation mark on some cells in column D which I formatted as text. It says a datestring is represented with only two digits for the year. This is not dateinformation but pure text. How to I tell Excel 2003 that column D is not a date?
Thanks,
Wes

Basically you enter numbers into column A and run the macro. This highest number is recorded in B and the lowest in C with a concatenation in D of B/C. Enter new values in A and run it again etc.

Sub HiLo()
Dim LastRow As Long, i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
' if cells A < B (or B empty) then B = A (highest value)
If .Range("A" & i) < .Range("B" & i) Or IsEmpty(.Range("B" & i)) Then
.Range("B" & i) = .Range("A" & i).Value
End If
' if cells A > B then B = A (lowest value)
If .Range("A" & i) > .Range("C" & i) Then
.Range("C" & i) = .Range("A" & i).Value
End If
'concatenate cells B & C into D
.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)
Next
End With
End Sub
 
Hi,

I need some help with this macro. When I run it I get the yellow explanation mark on some cells in column D which I formatted as text. It says a date string is represented with only two digits for the year. This is not date information but pure text. How to I tell Excel 2003 that column D is not a date?

Thanks,

Wes



Basically you enter numbers into column A and run the macro. This highest number is recorded in B and the lowest in C with a concatenation in D of B/C. Enter new values in A and run it again etc.



Sub HiLo()

Dim LastRow As Long, i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow

' if cells A < B (or B empty) then B = A (highest value)

If .Range("A" & i) < .Range("B" & i) Or IsEmpty(.Range("B" & i)) Then

.Range("B" & i) = .Range("A" & i).Value

End If

' if cells A > B then B = A (lowest value)

If .Range("A" & i) > .Range("C" & i) Then

.Range("C" & i) = .Range("A" & i).Value

End If

'concatenate cells B & C into D

.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)

Next

End With

End Sub
Click to expand...


Give this a try.

..Range("D" & i) = "'" & .Range("B" & i) & "/" & .Range("C" & i)

Regards,
Howard
 
It work great!
Why does adding a null to the start of the string correct this?
Thanks Howard
 
It work great!

Why does adding a null to the start of the string correct this?

Thanks Howard
Click to expand...


By concatenating a number with a null string, the number converts to text.
And once it is text Excel stops trying to "help" you when you don't want or need it.

My guess is that the "auto-assumption" to convert certain values to dates came first and then came the "cure" from the deep dark programming rooms in Redmond Washington.

Regards,
Howard
 
Give this a try.



.Range("D" & i) = "'" & .Range("B" & i) & "/" & .Range("C" & i)



Regards,

Howard
Click to expand...

Sorry Howard I did not test it hard enough. In some instances it is still reverting to a date.

Wes
 
Hi Wes,

Am Sun, 10 Mar 2013 11:42:53 -0700 (PDT) schrieb Wes Finch:
I did not test it hard enough. In some instances it is still reverting to a date.
Click to expand...

try:
..Range("D" & i).NumberFormat = "@"
..Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)


Regards
Claus Busch
 
Hi Wes,



Am Sun, 10 Mar 2013 11:42:53 -0700 (PDT) schrieb Wes Finch:






try:

.Range("D" & i).NumberFormat = "@"

.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Thanks Claus,
But no that does not work either.
Wes
 
Thanks Claus,

But no that does not work either.

Wes
Click to expand...

Hi Wes,

Are you still having a problem with this? If so, give us a few samples of the numbers that are giving you trouble. I tried Claus' idea, worked great for me.

regards,

Dave
 
Hi Wes,
Am Sun, 10 Mar 2013 11:42:53 -0700 (PDT) schrieb Wes Finch:


try:
.Range("D" & i).NumberFormat = "@"
.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)


Regards
Claus Busch
Click to expand...

I find that this works better after the value has been entered...

.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)
.Range("D" & i).NumberFormat = "@"

...but using the apostrophe as suggested by Howard obviates having to
specify NumberFormat.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Am Mon, 18 Mar 2013 00:09:48 -0400 schrieb GS:
I find that this works better after the value has been entered...

.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)
.Range("D" & i).NumberFormat = "@"
Click to expand...

the formatting as text first should avoid changing the entered value to
a date. If the entered value already changed to a date, a later
formatting as text gives a wrong result.


Regards
Claus Busch
 
Hi Garry,
Am Mon, 18 Mar 2013 00:09:48 -0400 schrieb GS:


the formatting as text first should avoid changing the entered value
to a date. If the entered value already changed to a date, a later
formatting as text gives a wrong result.


Regards
Claus Busch
Click to expand...

Thanks, Claus! I'll take a closer look at this as I was probably
thinking something else...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top