Hi Dave,
Thanks for the code. Where should I put your code in my code? Should I put
it right below my code? I have tried to put it as a separate code, and it
didn't work. I got an error "Variable not defined".
I need help with one of the line item below:
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
Where you see "Application.Substitute(xxx,"0","")", that is where I need
help with.
Thanks. Below is my entire code.
Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File, if
one exists
Dim CRYear As Integer 'Year
Dim FName As String Portion of CSV File Name
Dim InitName As String 'Placeholder to create additional
CSV files
Dim i As Integer 'Use to create additional CSV files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence to
create additional CSV files
With Sheets(Sheets3)
CRYear = Year(.Cells(2))
End With
With Sheets(Sheets2)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\" & CRYear
ActiveWorkbook.Save 'Save the input file before creating
a CSV file.
'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name, 5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name, 16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit
'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', additional CSV files are created automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
End If
If i 2 Then
MsgBox "Sorry! There are already " & i & "
files created " & _
"in the directory C:\'. " & Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , , _
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" & Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"
'If 'No', delete the CSV sheet that created in the input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the directory "
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If
'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub
"Dave Peterson" wrote:
Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim cCtr As Long
Dim myStr As String
'select the range to fix first
Set myRng = Selection
For Each myCell In myRng.Cells
myStr = myCell.Value
Do
If Left(myStr, 1) = "0" Then
myStr = Mid(myStr, 2)
Else
'get out
Exit Do
End If
Loop
myCell.Value = myStr
Next myCell
End Sub
AccessHelp wrote:
Gary,
After I sent you a message last evening, I realized that
"Application.Substitute(xxx,"0","")" would not work because it will remove
all the zeros. For example, if I have "00H0", the result is "H". The
correct result should be "H0", not "H".
Please help. Thanks.
"Gary Keramidas" wrote:
chip:
if i have this in a cell 000AAA, your code blanks the cell
if i substitute "" for vbnullchar, it works,
--
Gary
"Chip Pearson" wrote in message
...
In Excel 2000 and later, use Replace. E.g.,
Sub AAA()
Dim S As String
S = Range("B2").Text
S = Replace(S, "0", vbNullChar)
Range("B2").Value = S
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"AccessHelp" wrote in message
...
Hi Gary,
Thanks for the formula. Unfortunately, the formula is for Visual Basic in
Excel, not for the Excel itself. I don't think Visual Basic has a formula
for "Substitute". I am sorry I should have indicated in my message that it
is for Visual Basic.
Thanks again.
"Gary Keramidas" wrote:
try this if the cell in B2 had your example
=SUBSTITUTE(B2,"0","")
--
Gary
"AccessHelp" wrote in message
...
Hello,
I have a field with values "AAAA", "0AAA", "00AA", and "000A". I want to
remove leading zeros on the ones with leading zeros. For example, "0AAA"
will turn into "AAA".
Can someone help me with the formula? I have tried using "Val" and "Str",
and they don't seem to work.
Thanks.
--
Dave Peterson