Macro storing as a date?

[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

 

[Howard]

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

Give this a try.

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

Regards,
Howard

 

[Wes Finch]

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

 

[Howard]

It work great!

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

Thanks Howard

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

 

[Wes Finch]

Give this a try.



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



Regards,

Howard

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

Wes

 

[Claus Busch]

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.

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


Regards
Claus Busch

 

[Wes Finch]

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

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

 

[Dave Unger]

Thanks Claus,

But no that does not work either.

Wes

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

 

[GS]

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

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

 

[Claus Busch]

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 = "@"

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

 

[GS]

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

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

 

[GS]

Found it! I was in an entirely different context. My apologies...

--
Garry

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