Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Open command fails and blows up all cell formulas | Excel Programming | |||
Shell Command Fails (sometimes) | Excel Programming | |||
Formula fails if cells text format | Excel Discussion (Misc queries) | |||
MoveAfterReturn command fails in Worksheet module | Excel Programming | |||
ADO Open command fails on multiple SQL functions | Excel Programming |