Posts by StudentOfData

That worked perfectly. Thank you very much!

Column A has about 1,600 rows of data. The fields are either a number (such as 45355) or #VALUE!

I'd like to create a macro that changes every #VALUE! field to the nearest number above.

For example:

Should become:

Can anyone help me with how I would create a macro to do this please?

I had already used TRIM to get rid of preceding and trailing spaces. However, when I save it to .CSV using the macro, then spaces appear in the CSV.

We figured out the previous problem and resolved it. The problem was, if there's a comma it was getting read as a decimal. But that's resolved.

Here is another problem that has appeared now. For some files, the codes in Column C look normal (no leading or trailing spaces), but when I use the macro to save to CSV and close the document, then the CSV file has a leading and trailing blank space.

For example, cell C2 has the code "1234567.123.123.XXX". When I save to CSV, then it has the code (inside quotes): " 1234567.123.123.XXX "

Do you have any idea how to remedy this?

The macro code is in the thread above.

Thank you so much for looking at my question!

Here's an issue that came up, with this process. I have been using the above Macro to save the file as .csv and close it. I recently learned that our Data Uploader that reads the .csv files, needs the data in the files to be in "Number" format. However, here is the problem. I have set the .xlsm file (before running the macro) to be in "Number" format. When I open up the .csv file that is created, it shows the data in "General" format.

Is the data in the .csv file, in the Number format and then it changes to "General" format when I open it? Or is the data getting saved into the "General" format? Is it possible to modify the Macro, so that it saves the data into Number format?

Thank you! This is a huge help!!! Very grateful.

I want to create a macro to save the file to the "July" folder in the Z drive, in a CSV format. Without changing the file name to something different.

I have the macro:

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
ChDir "Z:\July"
ActiveWorkbook.SaveAs FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
End Sub

Here's the problem. It doesn't save the file to the Z drive and the July folder. It just saves the files in the folder that the file is currently in.

I have found sites suggesting I should do the following:

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
ChDir "Z:\July"
ActiveWorkbook.SaveAs Filename:= _
"Z:\July\Ledger_Template_final.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
End Sub

This works, in that it saves the file in CSV format, to the July folder in the Z drive, with the filename "Ledger_Template_final." The problem is, I don't want the macro to specify the filename. The filename should be whatever the existing name of the file is. The reason is, the macro will be used on many different files that will be saved in the July folder. We can't save them all with the same file name.

I also tried:

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Location = "Z:\July\"
ActiveWorkbook.SaveAs Filename:= _
Location & ActiveWorkbook.Name , _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Oddly, this saves the file to the proper folder with the unchanged filename, BUT it saves it as .xlsm rather than as .csv. Very strange, given that the code specifies the file format ":=xlSCV"

Can anyone help me fix this issue please?