One of the most difficult things to do is to guess the level of expertise of
the person asking the question.
Sometimes, by observing improper use of terms, we can get an idea.
But if the person describes the question in an intelligent manner, then
we have to make some assumptions about even what is meant by
"novice"!
for instance, How familiar are you with the VBA editor and debugger?
Did you know that what "module" you place the macro in makes a difference
as to where it can be used?
For instance, if you put it in the "sheet" module, then it is only available to
the
single sheet. But if you put it in a "standard" module, it is available to all
sheets.
In sheet modules (and the one called Thisworkbook) you can create macro
"events". by selecting the left-hand pull-down in the editor window
(it usually says "(general)") you select "worksheet" and the right-hand
pull-down
lists varios "event" macros, like activate/deactivate, change,
selectionchange...
these macros whenever the named action takes place (very useful !!!)
Then as to the debugger:
You can set breakpoints by selecting the area to the left of the edit panel
(the border between the macro and the project explorer)
then, if you have the debug toolbar on, you can execute the macro and it will
stop at the breakpoint. then you can set "watches" to see the values of
variables
at this point in the macro, then using the toolbar, you can step through the
macro one line at a time and watch how the variables change and the macro
performs.... a very, very, very, VERY useful tool.
again... since we have no way of determining each contributor's level of
expertise, we have difficulty knowing what level of explanation is required.
If we provide too much detail, we're viewed as being condescending.
too little and we confuse the people we're trying to help.
then, you get people like me that ramble on, and on, and ..., oh well, you get
the idea...
glad things worked out...
Paul
________________________________
From: Erick C <boricua2...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, May 25, 2010 12:42:24 PM
Subject: Re: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste
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
--
----------------------------------------------------------------------------------
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
