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
