Formatting automatically filled cells
 

Hi everybody!

My problem is like this :

There is an Excel 2007 under Windows 7 (Version Home Premium, if it can be somehow helpful) file, including a worksheet with a region consisting of two adjacent columns. The left column A is supposed to contain some date values in European format (f.ex. "31.12.2006"), the right one (column B) contains some positive integers in standard formats.
Further, there is another cell (say 'AB1') with a formula "=MAX(B:B)", and one more cell (say 'AC1') with formula "=INDEX(A:A;MATCH(AB1;B:B;0))".
As long as I fill the data in the region 'A:B' manually, the both 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 the corresponding date reads 11.01.2005, the value of the AB1 is 39000, and 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 with two fields - for the date and for the integer, they appear properly in their designed positions in the array 'A:B'. The AB1 displays correctly the value 39000 (which is currently the real MAX(B:B)), however the AC1 reads now "11.11.2005" - though I need 38667 at this place.

What could cause this phenomen?

Any kindly advice would be highly appreciated.

Formatting automatically filled cells
 

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

Thank you, Ben!

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

Many kindly regards.

Formatting automatically filled cells
 

Glad to hear it works, and happy to help.

Ben

Hi,

Is it fine for you to attach a sample.

Here are two suggestions that I have to make.

Are you making sure that the data type of the dates returned from your user form is right, i.e. If you are getting the date in a text box by default it becomes a string. You need to convert that string value to an integer before you put it back onto the sheet. Assuming that you are not doing this, you are actually getting a text value (11.11.2005) returned to the cell not the integer value 38667 which is why you are getting the text version of the value in AC1.

If the above is taken care of and you still face the issue.
To quickly fix the issue inside the VBA code for user form change the format of the cell that evaluates the value (AC1).

If you aren't sure of anything above, please let me know. Please provide a sample for me to rework and provide you with the correct results.

Thanks,
Prashant

Hi There,

Apologies didn't note that Ben had already replied. The issue indeed was with the data type.

Nevertheless, happy to see your issue is resolved.