Home |
Search |
Today's Posts |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You copied your code into a module that had:
Option Explicit At the top. This forces you to declare each of your variables. I think I'd something more like: Option Explicit Sub FormatMIdates() Dim Cell As Range Dim miDate As String Dim pYear As String Dim pMonth As String Dim pDay As String Dim NewDate As Date For Each Cell In Selection.Cells miDate = Cell.Value miDate = Right(String(8, "0") & miDate, 8) If CLng(miDate) 0 Then pYear = Left(miDate, 4) pMonth = Mid(miDate, 5, 2) pDay = Right(miDate, 2) NewDate = DateSerial(pYear, pMonth, pDay) Cell.Value = NewDate End If Next Cell 'Selection.NumberFormat = "@" Selection.NumberFormat = "mm/dd/yyyy" End Sub By using dateserial(), you won't be affected by the way excel/windows sees dates (mdy or dmy or...). wrote: Hi Im trying to get the following code to run from a custom add-in button. The code works fine when its in the Workbook code area, however, when i put it into a module in order to package it as an add-in i get the error "Variable not defined" with cell highlighted. Any ideas? Thanks a lot for any suggestions. Paul Sub FormatMIdates() Selection.NumberFormat = "@" For Each cell In Selection midate = cell.Value If midate < "" Then pYear = Left(midate, 4) pmonth = Mid(midate, 5, 2) pday = Right(midate, 2) newDate = pday + "/" + pmonth + "/" + pYear cell.Value = newDate End If Next End Sub -- Dave Peterson |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |