Formatting automatically filled cells

[Locateur]

Hi everybody!

My problem is like this :

There is an Excel 2007 under Windows 7 (Version Home Premium, if i
can be somehow helpful) file, including a worksheet with a regio
consisting of two adjacent columns. The left column A is supposed t
contain some date values in European format (f.ex. "31.12.2006"), th
right one (column B) contains some positive integers in standar
formats.
Further, there is another cell (say 'AB1') with a formul
"=MAX(B:B)", and one more cell (say 'AC1') with formul
"=INDEX(A:A;MATCH(AB1;B:B;0))".
As long as I fill the data in the region 'A:B' manually, the bot
AB1 and AC1 display obediently what I hope to find there. For instance
if the highest integer from the column 'B:B' is 39000 while th
corresponding date reads 11.01.2005, the value of the AB1 is 39000, an
the AC1 shows 38667 (the date value from "11.11.2005").

If only I try to enter the same data pair using some user forms wit
two fields - for the date and for the integer, they appear properly i
their designed positions in the array 'A:B'. The AB1 displays correctl
the value 39000 (which is currently the real MAX(B:B)), however the AC
reads now "11.11.2005" - though I need 38667 at this place.

What could cause this phenomen?

Any kindly advice would be highly appreciated

 

[Ben McClave]

Hello,

It sounds to me like the userform is returning a string rather than a date.If you Dim a Date field for your userform, it may help. The code would read something like this:

Private Sub CommandButton1_Click()
Dim dDate As Date
dDate = TextBox1.Value
Sheet1.Range("A50000").End(xlUp).Offset(1, 0).Value = dDate
End Sub

Alternatively, you could look at using a function like "CDate" in VBA to coerce the number into a date format. Or maybe using the "DateValue" and "IsText" functions in Excel to convert the string version of the date to a numeric value. For example, Cell AC1 might be changed to:

=IF(ISTEXT(INDEX(A:A,MATCH(AB1,B:B,0))), DATEVALUE(INDEX(A:A,MATCH(AB1,B:B,0))), INDEX(A:A,MATCH(AB1,B:B,0)))

Good Luck,

Ben

 

[Locateur]

Thank you, Ben!

The reason was indeed as you supposed : a string as the return valu
from the user form...
No sooner I modified my code - it was already doing !

Many kindly regards

 

[Ben McClave]

Glad to hear it works, and happy to help.

Ben