Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 166
Default VB Format command fails


Hi,

Anyone know what's wrong here?

Cell(1,1) contains "00/11/1975" as text.

Dim v as Date
v = Format(.Cells(1, 1), "mmm yyyy")

If v is dimmed as a Variant the format is unchanged
and in the above example returns 'type mismatch'.

Have spend a long time trying all sorts of variations
without success.

Thanks - Kirk
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default VB Format command fails

Hi Kirk,

Format function reutrns a string value not a date value and hense cannot be
assigned to a date variable.

00/11/75 is not a valid date and you can't even use DateValue to return a
date from it.

You can use Val but it will return zero.

What are you trying to achieve? There may be another way.

--
Regards,

OssieMac


"kirkm" wrote:


Hi,

Anyone know what's wrong here?

Cell(1,1) contains "00/11/1975" as text.

Dim v as Date
v = Format(.Cells(1, 1), "mmm yyyy")

If v is dimmed as a Variant the format is unchanged
and in the above example returns 'type mismatch'.

Have spend a long time trying all sorts of variations
without success.

Thanks - Kirk

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default VB Format command fails


Have you qualified .Cells(....etc?, why not supply a bit more of your
code, which version of Excel are you using?, you say the value is
enetered as text have you added a ' for a text entry or is the cell
formatted as text?

kirkm;327946 Wrote:
Hi,

Anyone know what's wrong here?

Cell(1,1) contains "00/11/1975" as text.

Dim v as Date
v = Format(.Cells(1, 1), "mmm yyyy")

If v is dimmed as a Variant the format is unchanged
and in the above example returns 'type mismatch'.

Have spend a long time trying all sorts of variations
without success.

Thanks - Kirk



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91634

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 166
Default VB Format command fails

On Thu, 30 Apr 2009 00:13:01 -0700, OssieMac
wrote:

Hi Ossie

Format function reutrns a string value not a date value and hense cannot be
assigned to a date variable.


Aha... OK

00/11/75 is not a valid date and you can't even use DateValue to return a
date from it.

You can use Val but it will return zero.

What are you trying to achieve? There may be another way.


Change '00/11/75' into "Nov 1975".

I normally go with some pretty basic stuff but was sure VB would
know "11" was November and 1975 was... 1975... if I could
figure out how! I have used Format with "mm ddd yyyy" but
this was to output a string (as you say).

Thanks - Kirk


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 166
Default VB Format command fails

On Thu, 30 Apr 2009 08:17:40 +0100, Simon Lloyd
wrote:

Hi Simon

Have you qualified .Cells(....etc?,


Yes

why not supply a bit more of your
code,


Fair enough, but it was just that line - or method failing. Ossie has
explained Format won't return a date.

which version of Excel are you using?, you say the value is
enetered as text have you added a ' for a text entry or is the cell
formatted as text?


2002 and the Cell is formatted as text. I found anything else caused
no end of trouble with auto-converting to ... something quite
different e.g. date to a number. Very frustrating!!

Cheers - Kirk




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 449
Default VB Format command fails

Hi Kirk

The others explained about types. Problem also is that it won't convert a
month-like text without a day to a valid date, therefore the type mismatch.
So let's avoid that one:

Sub test()
Dim S As String
Dim D As Date
'S = Cells(1, 1).Value 'or
S = "00/11/1975"
S = Replace(S, "00/", "01/")
D = DateValue(S)
MsgBox Format(D, "dddd dd.mm.yy") & vbNewLine & _
Format(D, "mmm yyyy")
End Sub

HTH. Best wishes Harald

"kirkm" wrote in message ...

Hi,

Anyone know what's wrong here?

Cell(1,1) contains "00/11/1975" as text.

Dim v as Date
v = Format(.Cells(1, 1), "mmm yyyy")

If v is dimmed as a Variant the format is unchanged
and in the above example returns 'type mismatch'.

Have spend a long time trying all sorts of variations
without success.

Thanks - Kirk


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default VB Format command fails

Hi Kirk,

I still don't know exactly what you want to do with the mmm yyyy format. Do
you simply want it to display like that on the worksheet? I have a similar
thing that I use with AutoFilter so that it will display the month and year
in a column against another column that contains actual dates so that I can
select the month in Autofilter without having to use a custom filter.
However, the date behind the cell is actually 1 Nov 1975 for all of the
November dates.

If various dates are in column A then Custom Format column B to "mmm yyyy"
Now if date is in cell A2 then in B2 insert:-
=DATEVALUE(TEXT(A2,"mmm yyyy"))

In VBA code it would be as follows:-

Sub test()
With Sheets("Sheet1")
.Columns(2).NumberFormat = "mmm yyyy"
.Cells(2, 2) = DateValue(Format(.Cells(2, 1), "mmm yyyy"))
End With
End Sub

Hope this helps.

--
Regards,

OssieMac


"kirkm" wrote:

On Thu, 30 Apr 2009 00:13:01 -0700, OssieMac
wrote:

Hi Ossie

Format function reutrns a string value not a date value and hense cannot be
assigned to a date variable.


Aha... OK

00/11/75 is not a valid date and you can't even use DateValue to return a
date from it.

You can use Val but it will return zero.

What are you trying to achieve? There may be another way.


Change '00/11/75' into "Nov 1975".

I normally go with some pretty basic stuff but was sure VB would
know "11" was November and 1975 was... 1975... if I could
figure out how! I have used Format with "mm ddd yyyy" but
this was to output a string (as you say).

Thanks - Kirk



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 166
Default VB Format command fails

On Thu, 30 Apr 2009 13:05:04 -0700, OssieMac
wrote:

Hi Ossie,

Thanks for that example. I see how that would work.

I still don't know exactly what you want to do with the mmm yyyy format. Do
you simply want it to display like that on the worksheet?


Yes - here's what I ended up using, although now I see Haralds example
this is laughable!

--
Sub UpDateDate()
Dim j, y, c, m, D
D = "JanFebMarAprMayJunJulAugSepOctNovDec"
With Worksheets("Sheet1")
For j = 2002 To 4000
c = .Cells(j, "B") '00/00/1965'
If c "" Then
y = Right(c, 4)
m = Val(Mid(c, 4, 2))
Select Case m
Case 1 To 12
m = (m * 3) - 2
.Cells(j, "B") = Mid(D, m, 3) & " " & y
Case 0
.Cells(j, "B") = y
Case Else
Stop
End Select
End If
Next j
End With
End Sub
--

If various dates are in column A then Custom Format column B to "mmm yyyy"
Now if date is in cell A2 then in B2 insert:-
=DATEVALUE(TEXT(A2,"mmm yyyy"))

In VBA code it would be as follows:-

Sub test()
With Sheets("Sheet1")
.Columns(2).NumberFormat = "mmm yyyy"
.Cells(2, 2) = DateValue(Format(.Cells(2, 1), "mmm yyyy"))
End With
End Sub


One for the book. I'd not seen DateValue before.

Thanks - Kirk
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 166
Default VB Format command fails

On Thu, 30 Apr 2009 19:36:25 +0200, "Harald Staff"
wrote:

Hi Kirk

The others explained about types. Problem also is that it won't convert a
month-like text without a day to a valid date, therefore the type mismatch.
So let's avoid that one:

Sub test()
Dim S As String
Dim D As Date
'S = Cells(1, 1).Value 'or
S = "00/11/1975"
S = Replace(S, "00/", "01/")
D = DateValue(S)
MsgBox Format(D, "dddd dd.mm.yy") & vbNewLine & _
Format(D, "mmm yyyy")
End Sub


Hi Harold,

Gotcha! That's exactly where I was heading....

Plus I nearly fell over when seeing the Replace command.
I've made lengthy custom routines that do that is a far less
efficient way. (But I found it's not in Access 97 so don't
feel completely stupid!). DateValue was new to me, too.

So much to remember!

Thanks - Kirk

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 449
Default VB Format command fails

"kirkm" wrote in message ...
Plus I nearly fell over when seeing the Replace command.
I've made lengthy custom routines that do that is a far less
efficient way. (But I found it's not in Access 97 so don't
feel completely stupid!). DateValue was new to me, too.


You're welcome Kirk.
Replace came with VB6 / VBA 2000. Very useful new text functions was
Replace, Split, Join and InstrRev. I use the first two all the time, like:

Sub test()
Dim Sentnc As String
Dim Words() As String
Dim i As Long
Sentnc = InputBox("Say something:")
Words = Split(Sentnc, " ")
For i = LBound(Words) To UBound(Words)
MsgBox Words(i), , "Word " & i & ":"
Next
End Sub

Best wishes Harald

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook_Open command fails and blows up all cell formulas pdberger Excel Programming 2 November 20th 08 08:48 PM
Shell Command Fails (sometimes) Daniel Excel Programming 4 January 21st 08 12:18 AM
Formula fails if cells text format Rob[_4_] Excel Discussion (Misc queries) 3 April 26th 07 03:48 AM
MoveAfterReturn command fails in Worksheet module quartz[_2_] Excel Programming 1 April 1st 05 08:49 PM
ADO Open command fails on multiple SQL functions Zachary Bass Excel Programming 1 August 12th 03 07:54 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"