Paul - I changed the part of the code that you suggested, and it is working fine. Again, I am still quite a novice with reading and writing code, so I did not recognize that your code was not looking at the columns that I had originally described. Now I know where to look.
Thank you again. On May 25, 6:58 am, Paul Schreiner <schreiner_p...@att.net> wrote: > "without making modifications"... > > Hmm... you DO realize that I don't have your file? > and therefore I had to use a data file I had for some other entire purpose? > And... the columns YOU want to compare would have no significance to me? > > You should try using the VBA debugger and step through the code and see what > it is doing. > > You just said that you're comparing columns "T" and "E". > > if you used my code "without making modifications", then the line: > > If (ActiveSheet.Range("BM" & R).Value <> ActiveSheet.Range("AE" & R).Value) > Then > > is comparing columns "BM" and "AE". > > you probably should have modified that part. > > As I said, try the debugger and step through to see what it is doing. > > You should find the problem then. > > sorry for the confusion (and sarcasm) ;) > > Paul > > ________________________________ > From: Erick C <boricua2...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Mon, May 24, 2010 3:41:51 PM > Subject: Re: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste > > Hi Paul - > > I tried the code you provided without making any modifications and I > had a few issues. > Just for a bit of background: The file I am using for testing has 925 > rows, 11 rows should be excluede since the dates in column T do not > match column E. Txt file should have 914 rows of data. > The macro created the new tab, but a majority of the data got added > into the new tab, 897 rows to be exact. > The macro was created fine, but there were only 28 records in the txt > file. The excel rows that were chosen for the txt file were also off, > since there were some rows in the txt file where column T did not > match column E. > > I hope my descriptions are a bit helpful in trying to troubleshoot. > If there is any other information that you need please let me know. > > Thanks again! > > On May 21, 11:20 am, Paul Schreiner <schreiner_p...@att.net> wrote: > > > > > did you look at my code? > > if you change > > StrDelim = "|" > > to > > StrDelim = "" > > > it should do what you want. > > if you don't NEED to copy the records where T and E are different, you can > > comment out that section. > > > Paul > > > ________________________________ > > From: Erick C <boricua2...@gmail.com> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > > Sent: Fri, May 21, 2010 11:55:43 AM > > Subject: Re: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste > > > Hi Paul - > > Thanks for the reply. Let me attempt to answer your questions and > > hopefully clarify a bit. > > 1. I am using Excel 2003 > > > 2. All of the steps prior to the code I already had were serving only > > one purpose. I want to write the text file using only rows where the > > date in column T equals the date in column E. In my mind (and > > probably my lack of experience) the only way I could think to > > accomplish this was to identify the rows where the dates did not match > > and cut and paste them out of the worksheet. There may be a much > > simpler way to do this with code, but I do not know what it would be. > > > 3. I do understand the code that I am using does not have spaces or > > delimiters between the values. I am using this text file in place of > > an import file that we used to have generated externally. The program > > that this text file is imported into is very touchy, so I needed to > > make a text file that exactly matched the format and layout of our old > > text files. These files had no spaces or delimiters, so I have to > > leave them out. > > > I hope this clears up the questions that you had. Thanks again for > > the response. > > > On May 21, 7:39 am, Paul Schreiner <schreiner_p...@att.net> wrote: > > > > I have a few questions. > > > > What version of Excel are you using? > > > > You said in step 6, you "cut" the records that AG is null > > > did you really man "cut", as in: removed from sheet1? > > > > also, you're writing a file with : > > > a.WriteLine s1 & s2 & s3 & s4 & s5 & s6 > > > but the values have no spaces or delimiters between them. > > > > I think I can do the entire process with a single macro: > > > In my test data, it processed 65,536 records of 74 columns > > > in 8 minutes, 32 seconds. > > > '------------------------------------------------------------------------- > > > Option Explicit > > > Sub CvtTotext() > > > Dim LastRow, LastCol > > > Dim R, C, RowNI > > > Dim FileName, FilePath, fs, f, StrOut, StrDelim > > > Dim TElapsed, TMin, TSec, TStart, TStop > > > '------------------------------------------ > > > TStart = Timer > > > Application.ScreenUpdating = False > > > '------------------------------------------ > > > ' Create "Not Imported" sheet > > > '------------------------------------------ > > > On Error Resume Next > > > Application.DisplayAlerts = False > > > Err.Clear > > > Sheets("Not Imported").Select > > > If (Err.Number = 0) Then Sheets("Not Imported").Delete > > > Sheets.Add After:=Sheets(Sheets.Count) > > > ActiveSheet.Name = "Not Imported" > > > On Error GoTo 0 > > > Application.DisplayAlerts = True > > > '------------------------------------------ > > > Sheets("sheet1").Select > > > LastRow = ActiveCell.SpecialCells(xlLastCell).Row > > > LastCol = ActiveCell.SpecialCells(xlLastCell).Column > > > > > > For C = 1 To LastCol > > > Sheets("Not Imported").Cells(1, C) = ActiveSheet.Cells(1, C).Value > > > Next C > > > > > > FileName = InputBox(" Enter Text File Name ", "Text File") > > > FilePath = "g:\Treas\Erick\Price File Test\" & FileName & ".txt" > > > StrDelim = "|" > > > Set fs = CreateObject("Scripting.FileSystemObject") > > > If (fs.fileexists(FilePath)) Then fs.deletefile FilePath > > > Set f = fs.CreateTextFile(FilePath, True) > > > Application.StatusBar = "Processing " & LastRow & " records)" > > > RowNI = 1 > > > For R = 2 To LastRow > > > If (R Mod 1000 = 0) Then > > > Application.StatusBar = "Processing " & R & " of " & LastRow > > > End If > > > If (ActiveSheet.Range("BM" & R).Value <> ActiveSheet.Range("AE" & > > > R).Value) Then > > > RowNI = RowNI + 1 > > > For C = 1 To LastCol > > > Sheets("Not Imported").Cells(RowNI, C) = > > > ActiveSheet.Cells(R, C).Value > > > Next C > > > Else > > > StrOut = Cells(R, 32).Value '32 > > > StrOut = StrOut & StrDelim & Cells(R, 3).Value '3 > > > StrOut = StrOut & StrDelim & Format(Cells(R, 20).Value, > > > "mmddyyyy") '20 > > > StrOut = StrOut & StrDelim & Format(Cells(R, 21).Value * > > > 100000000, "000000000000#") '21 > > > StrOut = StrOut & StrDelim & " " > > > StrOut = StrOut & StrDelim & "0000000000000" > > > StrOut = StrOut & StrDelim & " " > > > StrOut = StrOut & StrDelim & " " > > > StrOut = StrOut & StrDelim & " " > > > StrOut = StrOut & StrDelim & "0000000000" > > > StrOut = StrOut & StrDelim & Format(Cells(R, 22).Value, > > > "mmddyyyy") '22 > > > StrOut = StrOut & StrDelim & "BNYM" > > > StrOut = StrOut & StrDelim & "0000000000000" > > > StrOut = StrOut & StrDelim & " " > > > > > > f.Writeline StrOut > > > End If > > > Next R > > > f.Close > > > '------------------------------------------ > > > Application.StatusBar = False > > > Application.ScreenUpdating = True > > > '------------------------------------------ > > > TStop = Timer > > > TElapsed = TStop - TStart > > > TMin = TElapsed \ 60 > > > TSec = TElapsed Mod 60 > > > MsgBox "Finished" & Chr(13) & TMin & " min " & TSec & " sec" > > > '------------------------------------------ > > > End Sub > > > > Paul > > > > ________________________________ > > > From: Erick C <boricua2...@gmail.com> > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > > > Sent: Thu, May 20, 2010 11:35:21 AM > > > Subject: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste > > > > 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") > > ... > > read more »- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- 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 7000 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