Good morning everybody -
I am still quite a novice when it comes to VB and writing codes, so
any help I can get would be greatly appreciated. I am currently
working on a code that takes data from a xls file that I download and
convert it to a text file. I found an older posting with an example
that I modified a bit to meet my format requirements. The formatting
is working fine, but there is some manual cleanup that has to be done
prior to running the macro that I would like to add to the macro.
What I am trying to add to my current macro (in this order):
1. Sort the file by column 20 ascending (column T)
2. In cell AG2 (column 33), enter formula "=if(T2=E2, "OK","")
3. Copy formula from AG2 down column AG to last record
4. Sort the file by column 33 (column AG)
5. Create a new worksheet named "Not Imported"
6. Back in Sheet 1, cut any record where the AG cell is null and paste
into Not Imported tab
7. Back in sheet 1, continue with the code I have (below):
Public Sub CvtTotext()
LastRow = Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , ,
xlByRows, xlPrevious).Row
Filename = InputBox(" Enter Text File Name ", "Text File")
FilePath = "g:\Treas\Erick\Price File Test\" & Filename & ".txt"
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(FilePath, True)
For Row = 3 To LastRow
s1 = Cells(Row, 32).Value
s2 = Cells(Row, 3).Value
s3 = Format(Cells(Row, 20).Value, "mmddyyyy")
s4 = Format(Cells(Row, 21).Value * 100000000, "000000000000#")
s5 = " "
s6 = "0000000000000"
s7 = " "
s8 = " "
s9 = " "
s10 = "0000000000"
s11 = Format(Cells(Row, 22).Value, "mmddyyyy")
s12 = "BNYM"
s13 = "0000000000000"
s14 = " "
a.WriteLine s1 & s2 & s3 & s4 & s5 & s6 & s7 & s8 & s9 & s10 &
s11 & s12 & s13 & s14
Next
a.Close
MsgBox FilePath & " Created."
End Sub
If there are existing examples that do something close to what I am
trying to do, I would be more than happy to try to rip them apart
myself, I know everyone is quite busy. Any help I can get would be
greatly appreciated! Thanks!
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
