| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want a leading zero on a number
I have the following program that takes a date input from a combo box in a
userform and processes it. Public Sub TribeNameServDate() Unload frmFacil frmTribeNameSMCY.Show Application.ScreenUpdating = False Unload frmTribeNameSMCY ws.Range("A1").Value = sTribeNameUI & " Turnaround Report" ws.Range("D3").Value = sServMonthUI iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 If iPayrollMonth 12 Then iPayrollMonth = iPayrollMonth - 12 End If '#### How to force this to show a leadning zero for 1 digit nos.? ### sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month to text month ws.Range("C3").Value = sPayrollMonth If iPayrollMonth < 6 Then sSFY = Right(sCYUI, 2) Else sSFY = Right(sCYUI + 1, 2) End If ws.Range("J3:K3").Select Selection.NumberFormat = "@" ws.Range("J3") = Right(sCYUI, 2) ws.Range("K3") = sSFY Application.ScreenUpdating = True ws.Range("A1").Select End Sub Later, the variable iPayrollMonth will be used to name the file (see below), and I want it always as a 2 digit number, like 04 instead of 4. How do I do that? Public Sub FileNameandSave() Dim sYear As String Dim sFilename As String ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _ & " " & sTribeNameUI & " TR" End Sub Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want a leading zero on a number
Hi Salgud,
I'm assuming D3 is where you are putting your date from the userform, if so then you can change the way the date is displayed by added this line: ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" After: ws.Range("D3").Value = sServMonthUI So that it looks like: ws.Range("D3").Value = sServMonthUI ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 Hope that helps "salgud" wrote: I have the following program that takes a date input from a combo box in a userform and processes it. Public Sub TribeNameServDate() Unload frmFacil frmTribeNameSMCY.Show Application.ScreenUpdating = False Unload frmTribeNameSMCY ws.Range("A1").Value = sTribeNameUI & " Turnaround Report" ws.Range("D3").Value = sServMonthUI iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 If iPayrollMonth 12 Then iPayrollMonth = iPayrollMonth - 12 End If '#### How to force this to show a leadning zero for 1 digit nos.? ### sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month to text month ws.Range("C3").Value = sPayrollMonth If iPayrollMonth < 6 Then sSFY = Right(sCYUI, 2) Else sSFY = Right(sCYUI + 1, 2) End If ws.Range("J3:K3").Select Selection.NumberFormat = "@" ws.Range("J3") = Right(sCYUI, 2) ws.Range("K3") = sSFY Application.ScreenUpdating = True ws.Range("A1").Select End Sub Later, the variable iPayrollMonth will be used to name the file (see below), and I want it always as a 2 digit number, like 04 instead of 4. How do I do that? Public Sub FileNameandSave() Dim sYear As String Dim sFilename As String ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _ & " " & sTribeNameUI & " TR" End Sub Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want a leading zero on a number
On Wed, 1 Apr 2009 14:56:11 -0700, Derek Bliss wrote:
Hi Salgud, I'm assuming D3 is where you are putting your date from the userform, if so then you can change the way the date is displayed by added this line: ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" After: ws.Range("D3").Value = sServMonthUI So that it looks like: ws.Range("D3").Value = sServMonthUI ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 Hope that helps "salgud" wrote: I have the following program that takes a date input from a combo box in a userform and processes it. Public Sub TribeNameServDate() Unload frmFacil frmTribeNameSMCY.Show Application.ScreenUpdating = False Unload frmTribeNameSMCY ws.Range("A1").Value = sTribeNameUI & " Turnaround Report" ws.Range("D3").Value = sServMonthUI iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 If iPayrollMonth 12 Then iPayrollMonth = iPayrollMonth - 12 End If '#### How to force this to show a leadning zero for 1 digit nos.? ### sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month to text month ws.Range("C3").Value = sPayrollMonth If iPayrollMonth < 6 Then sSFY = Right(sCYUI, 2) Else sSFY = Right(sCYUI + 1, 2) End If ws.Range("J3:K3").Select Selection.NumberFormat = "@" ws.Range("J3") = Right(sCYUI, 2) ws.Range("K3") = sSFY Application.ScreenUpdating = True ws.Range("A1").Select End Sub Later, the variable iPayrollMonth will be used to name the file (see below), and I want it always as a 2 digit number, like 04 instead of 4. How do I do that? Public Sub FileNameandSave() Dim sYear As String Dim sFilename As String ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _ & " " & sTribeNameUI & " TR" End Sub Thanks! Thaks for the reply. I'm getting a type mismatch error on the line marked below: ws.Range("D3").Value = sServMonthUI ws.Range("D3").Select ActiveCell.Formula = "=text(A1,""mm-dd-yyy"")" iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))<---TYPE MISMATCH iPayrollMonth = iServMonth + 1 Did I miss something? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want a leading zero on a number
D3 is already in the format ("mm-dd-yy") and that is the cause of error.
Either you can change it to iServMonth = month(ws.Range("d3")) OR (as you have mentioned in your code) iServMonth = Month(month(ws.Range("d3") & " 1,2009")) The first one should be enough, right??? If this post helps click Yes --------------- Jacob Skaria "salgud" wrote: On Wed, 1 Apr 2009 14:56:11 -0700, Derek Bliss wrote: Hi Salgud, I'm assuming D3 is where you are putting your date from the userform, if so then you can change the way the date is displayed by added this line: ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" After: ws.Range("D3").Value = sServMonthUI So that it looks like: ws.Range("D3").Value = sServMonthUI ws.Range("D3").select ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")" iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 Hope that helps "salgud" wrote: I have the following program that takes a date input from a combo box in a userform and processes it. Public Sub TribeNameServDate() Unload frmFacil frmTribeNameSMCY.Show Application.ScreenUpdating = False Unload frmTribeNameSMCY ws.Range("A1").Value = sTribeNameUI & " Turnaround Report" ws.Range("D3").Value = sServMonthUI iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009")) iPayrollMonth = iServMonth + 1 If iPayrollMonth 12 Then iPayrollMonth = iPayrollMonth - 12 End If '#### How to force this to show a leadning zero for 1 digit nos.? ### sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month to text month ws.Range("C3").Value = sPayrollMonth If iPayrollMonth < 6 Then sSFY = Right(sCYUI, 2) Else sSFY = Right(sCYUI + 1, 2) End If ws.Range("J3:K3").Select Selection.NumberFormat = "@" ws.Range("J3") = Right(sCYUI, 2) ws.Range("K3") = sSFY Application.ScreenUpdating = True ws.Range("A1").Select End Sub Later, the variable iPayrollMonth will be used to name the file (see below), and I want it always as a 2 digit number, like 04 instead of 4. How do I do that? Public Sub FileNameandSave() Dim sYear As String Dim sFilename As String ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _ & " " & sTribeNameUI & " TR" End Sub Thanks! Thaks for the reply. I'm getting a type mismatch error on the line marked below: ws.Range("D3").Value = sServMonthUI ws.Range("D3").Select ActiveCell.Formula = "=text(A1,""mm-dd-yyy"")" iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))<---TYPE MISMATCH iPayrollMonth = iServMonth + 1 Did I miss something? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want a leading zero on a number
On Wed, 1 Apr 2009 21:14:01 -0700, Jacob Skaria wrote:
D3 is already in the format ("mm-dd-yy") and that is the cause of error. Either you can change it to iServMonth = month(ws.Range("d3")) Not sure what I would change to this. OR (as you have mentioned in your code) iServMonth = Month(month(ws.Range("d3") & " 1,2009")) Already have this in there, but it's not working. The first one should be enough, right??? The first what should be enough? Sorry, am not clear what you're suggesting. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Want a leading zero on a number
On Thu, 2 Apr 2009 09:30:47 -0600, salgud wrote:
On Wed, 1 Apr 2009 21:14:01 -0700, Jacob Skaria wrote: D3 is already in the format ("mm-dd-yy") and that is the cause of error. Either you can change it to iServMonth = month(ws.Range("d3")) Not sure what I would change to this. OR (as you have mentioned in your code) iServMonth = Month(month(ws.Range("d3") & " 1,2009")) Already have this in there, but it's not working. The first one should be enough, right??? The first what should be enough? Sorry, am not clear what you're suggesting. I tried changing iServMonth = Month(month(ws.Range("d3") & " 1,2009")) to iServMonth = month(ws.Range("d3")) Still getting a type mismatch error |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need 7 digit number with differnt number of leading zeros | Excel Discussion (Misc queries) | |||
| I want to add a plus sign behind a number and keep the leading zer | Excel Worksheet Functions | |||
| number format with leading 0 | Excel Discussion (Misc queries) | |||
| How to add leading 0 to four digit number? | Excel Worksheet Functions | |||
| Number to text with leading zero | Excel Programming | |||
Linear Mode
