Excel VBA 1

[Lhunt206]

I am fairly new to VBA and using code in Excel. I have achieved quite a bit but I am stuck on many different areas that I need to clean up in order to make this work.

Goal: I need to send an excel file to a txt file for a testing procedure.

My first issue I need to resolve is the decimal is showing in my text file. Some of the figures in my excel spreadsheet have decimals. The catch to this is some of them can only be dollar amounts (even though there might be cents) and some can have both - no rounding and no decimal allowed in txt file.

If someone can provide me with what will be helpful for me to attach to this I will.

 

[mintjulep]

What is the mathematical function envisioned to remove decimals without rounding?

 

[SkipVought]

Hi, and welcome to Tek-Tips. You'll find a wealth of information from just searching and browsing this forum and others here at TT.

You're going to need to create another sheet, so as not to destroy the data you have on this sheet, that truncates your decimal data. You can use the INT() function. You know how many rows and columns you need. You might use a formula like this, assuming that your table is in Sheet1 beginning at A1...
[tt]
=IF(ISNUMBER(Sheet1!A1),INT(Sheet1!A1),Sheet1!A1)
[/tt]

Copy and then Paste across & down on your new sheet

This does not address any field delimiters you might need on a delimited text file or space padding that you might need on a fixed width text file when you SaveAs to some sort of text file.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Lhunt206]

That formula worked great for the cells that I only needed dollar amounts. For the cells I needed to keep the cents and not round, it took that amount and rounded it.

 

[SkipVought]

Great!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}