Macro storing as a date?
 

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

Macro storing as a date?
 

On Saturday, March 9, 2013 7:34:36 PM UTC-8, Wes Finch wrote:
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

Macro storing as a date?
 

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

Macro storing as a date?
 

On Sunday, March 10, 2013 6:38:43 AM UTC-7, Wes Finch wrote:
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

Macro storing as a date?
 

On Saturday, March 9, 2013 11:50:20 PM UTC-5, Howard wrote:
On Saturday, March 9, 2013 7:34:36 PM UTC-8, Wes Finch wrote:

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

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

Wes

Macro storing as a date?
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Macro storing as a date?
 

On Sunday, March 10, 2013 1:52:55 PM UTC-5, Claus Busch wrote:
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

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

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

Macro storing as a date?
 

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

Macro storing as a date?
 

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

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

Macro storing as a date?
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Macro storing as a date?
 

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

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

Macro storing as a date?
 

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