Over-Eager code is pasting too many times

The code is running on the activeworkbook, where data is in
rows A:G. An identifier for the provisional start of each
record is in col A (defined as "A" & Cell.Row). User can
identify records to be copied using A, B , C etc in cols I to J
in Cell.Row
So, having found a record ("A" & Cell.Row) I now check to
see if the range(("H" & Cell.Row, "J" & Cell.Row) contains
any user tags, and if so, then copy the record to the destination
Workbooks("Sorted_Tagged " & x(4) & ".xls"). _
       Worksheets(TagCell.Value) for each tag, present.

Here's current code:

For Each TagCell In .Range("H" & Cell.Row, "J" & Cell.Row) _
          .SpecialCells(xlConstants)
   If Not IsEmpty(TagCell) Then
      .Range("A" & StartCopyRow, "J" & EndCopyRow).Copy _
           Destination:=Workbooks("Sorted_Tagged " _
           & x(4) & ".xls").Worksheets(TagCell.Value) _
          .Range("B65536").End(xlUp).Offset(2, -1)
      If Not IsEmpty(.Range("F" & StartCopyRow).End(xlDown) _
             .Offset(-1, -5)) Then
         .Range("F" & StartCopyRow).End(xlDown) _
                .Offset(-1, -5).Copy _
                 Destination:=Workbooks _
                 ("Sorted_Tagged " & x(4) & ".xls"). _
                 Worksheets(TagCell.Value) _
                .Range("A65536").End(xlUp).Offset(0, 10)
      End If
   End If
Next

All is fine except that if user has tagged all 3 cols against a
record, then I'm getting 3 copies of the record pasting into each
of the 3 destination sheets(g).

Would be very grateful for help in explaining this, please.


Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003


0
sg_booth (15)
9/9/2003 7:16:18 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
386 Views

Similar Articles

[PageSpeed] 21

If I understand you correctly, if Hx:Jx all have tags, then the 
For...Net loop will loop three times. To get it to stop after 
finding the first, put

    Exit For

before the second End If.



In article <uaU8RcwdDHA.2328@TK2MSFTNGP12.phx.gbl>,
 "Stuart" <sg_booth@hotmail.com> wrote:

> The code is running on the activeworkbook, where data is in
> rows A:G. An identifier for the provisional start of each
> record is in col A (defined as "A" & Cell.Row). User can
> identify records to be copied using A, B , C etc in cols I to J
> in Cell.Row
> So, having found a record ("A" & Cell.Row) I now check to
> see if the range(("H" & Cell.Row, "J" & Cell.Row) contains
> any user tags, and if so, then copy the record to the destination
> Workbooks("Sorted_Tagged " & x(4) & ".xls"). _
>        Worksheets(TagCell.Value) for each tag, present.
> 
> Here's current code:
> 
> For Each TagCell In .Range("H" & Cell.Row, "J" & Cell.Row) _
>           .SpecialCells(xlConstants)
>    If Not IsEmpty(TagCell) Then
>       .Range("A" & StartCopyRow, "J" & EndCopyRow).Copy _
>            Destination:=Workbooks("Sorted_Tagged " _
>            & x(4) & ".xls").Worksheets(TagCell.Value) _
>           .Range("B65536").End(xlUp).Offset(2, -1)
>       If Not IsEmpty(.Range("F" & StartCopyRow).End(xlDown) _
>              .Offset(-1, -5)) Then
>          .Range("F" & StartCopyRow).End(xlDown) _
>                 .Offset(-1, -5).Copy _
>                  Destination:=Workbooks _
>                  ("Sorted_Tagged " & x(4) & ".xls"). _
>                  Worksheets(TagCell.Value) _
>                 .Range("A65536").End(xlUp).Offset(0, 10)
>       End If
>    End If
> Next
> 
> All is fine except that if user has tagged all 3 cols against a
> record, then I'm getting 3 copies of the record pasting into each
> of the 3 destination sheets(g).
> 
> Would be very grateful for help in explaining this, please.
> 
> 
> Regards.
> 
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003
> 
>
0
jemcgimpsey (6723)
9/9/2003 8:18:15 PM
Reply:

Similar Artilces:

"Time oldest message submitted" column in Exchange Queue
What does this column represent? For example: I have some items in queue under the column "time oldest message submitted" dated 9/23/06 and then when you double click on the queue and click find now the date of the earliest message it is 9/26/06. Where does the date in the "time oldest message submitted" come from? I thought maybe it was the first time an email for that domain appeared in the queue but that doesn't seem right. One more thing that I should probably mention is that the date discrepency is only happening with items that are for domains that appear to be...

Slow reaction when inserting new lines or copy & paste
Hi to everyone: When inserting new lines to a worksheet the reaction of Excel is very slow. The same is with operations including copy & paste. We have these complaints since we moved from Excel 97 to Excel XP. We run Office XP under NT4. Unfortunately we were not able to isolate some common factors. Has anyone an idea? Input is highly appreciated. Kind regards Try cleaning your windows temp folder (it may not help, but it won't hurt--close excel first). You may want to take a look at these two sites (Charles Williams and David McRitchie): http://www.decisionmodels.com http...

Paste link 2003 excel charts into word 2007
When we try to copy & paste link charts made in excel 2003 into Word 2007 the whole left side is cutoff. This is when using the paste special paste link option & selecting Microsoft Office Excel object. When just accepting the paste default it does not cutoff but is distorted. When using the paste special paste then selecting Microsoft Office Excel object it does not cut it off but doesn't appear to be linked. I thought the default paste is paste/link now? Why doesn't these options work the same? Converting the 2003 excel spreadsheet did not help. Ultimately, I’d just l...

HOW ?Excel chart auto insert /populate a code based on date
I have a excel spreadsheet that has a front sheet with a date that I type in .. this then is populate across the other work sheets within the spreadsheet, such that formula ='Front Sheet'!D6+3 being worksheets Monday , Tuesday etc hence the first date on 'Front Sheet' and the add (number) for the week days. NOW I would lie to put into another cell a code tha always starts PF- and the rest of it PF-121201 being the day, month and then numbers01 and upwards to about 15 across the worksheets. Now I would like to get the numerical part 121201to be partly derived from the...

why does publisher change the font when i paste from word?
I have a text box in a Word document containing text in Arial 26pt. I copy the box and paste it into a Publisher document but now the text is Garamond 26pt. Why does it do this and can I make it paste literally what I copied? It would only take a minute to highlight the text and change it to Arial. It could be the copying of the box, what happens if you copy only the text? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "IanMM" <IanMM@discussions.microsoft.com> wrote in message news:76FCC2EF-47C9-4290-95A9-61EDF912...

number to time format when digit length varies
I am struggling to convert following "number" format cells as 24 hrs "time" format (hh:mm) 516 2306 637 202 2353 300 3 6 It should then appear as: 05:16 23:06 06:37 02:02 23:53 03:00 00:03 00:06 Any help will be a great relief. Thanks/regards, You can't use format to do this, you would need a formula (or a macro) and help cells, then you can copy and paste as special over the old values, with your value in A1 =(INT(A1/100)+MOD(A1,100)/60)/24 format as hh:mm -- Regards, Peo Sjoblom (No private emails please) "UM Win" <noname@UMwin.com>...

mark active cell and return to paste
after macro is run ....i want to run another macro starting in the exact cell that the previous code ended in. mark active cell Range("R4").Select Selection.Copy return to "active cell" ActiveSheet.Paste No need to use select, it is just slowing down the code: Range("R4").Copy ActiveCell or the more descriptive one: Range("R4").Copy Destination:=ActiveCell Regards, Per "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> skrev i meddelelsen news:ade54b7a-3d1d-48ca-bbb6-8c0d730ad380@e7g2000yqf.googlegrou...

Use a global.mpt from the past
I saved a global.mpt file from a prior life and now want to use it in my current situation. I also have a global (+ non-cached enterprise) file here too. How can I use my prior global.mpt file in this environment? MS Project 2007 on XP SP2. Thanks. Copy any Views, Tables etc from your Global to a blank project then save it. Close Project Locate your personal Global.mpt file on your PC. Rename it to Global Old.mpt Copy your old file there. Start Project and see what happens. Open blank project from above and copy any Views etc you still want to your new Global. -- Rod...

Lookup and compare to (today) + time and get result
Hi I am trying to set up a spreadsheet to track expired documents or track when training needs to occur. I'd like to get the output of the name of the person and the item that has expired. I'd also like to get the total number of expired documents and number of expired documents by type. Is this possible? +-------------------------------------------------------------------+ |Filename: xlhelp.zip | |Download: http://www.excelforum.com/attachment.php?postid=3509 | +-------------------------------------------------------------------...

Update Table via Code
MS Access 2003 There is a form with and unbound combo box that has a Master Field that links to a subform. I also added another in the main form combo box to show me a list of customers to choose from. The subform has an unbound combo box that links to the main form which will automatic poplulate whenever an item is selected from the main form. Is there a way I can create a code to add new records to an existing table by grabbing the data from the subform list and a customer name from the main form. Thank you for your help! Assuming you know how to retrieve all the valu...

Pivot Table Error-"Microsoft cannont make changes because there are too many..."
I've encountered the following error when using the pivot table. Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the Pivot Table, or to the page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut menu. In the layout tab of the pivot wizard I tried to select 9 rows, 2 columns, and 3 data. The first excel document that I pivoted had approximately 8800 lines (records) and the pivot table worked. I then tried to do the same layout criteria for another e...

Outlook
I have an old PST file of emails from a former email account (old employer) I want to read some of these old emails again, but can't find a way to open the pst in my new email account. Is there a way to retrieve them? Data Management says I do not have Access Rights when I try to add the pst file to my current Outlook. -- Thanks, Tipster Where is the PST located? Is it on a CD? Copy it to the local PC and remove the read only attribute. "Tipster" <Tipster@discussions.microsoft.com> wrote in message news:2F926D38-E529-4A5A-8AD8-3C5D55608FE6@microsoft.com.....

Loading time for Outlook
Hi, I'm using Outlook Express 6, whenever I clcik my outlook express shortcuts on the desktop, it took ages to actually load the page. Any solution? thanks Hope this helps When I worked supporting Outlook Express 5 & 6 this was a common issue. It is usually the "Protected Storage System Provider" registry key and associated files. This can also caused by a corrupted "Imagehlp.dll" file but this is more rare. 99% of the time is was caused from the Pstore files, see link below. This article applies to OLEX 5 but it always did the trick in WinXP with OLEX 6 or ...

How do i create a pie chart that shows % of time on tasks?
I need to create a pie chart that shows what percent of time my employees are working on each of 5-6 efforts. Does anyone have a good template for that? Thanks! ...

Excel won't allow me to paste certain Turkish characters.
I need to copy some text with Turkish characters from a Word document and paste into Excel. Excel does not recognise the Turkish charcters and they appear as "?". you will either need o load the Turkish fonts or use Print screen or some other method to copy as picture the section you want to paste. "neil" wrote: > I need to copy some text with Turkish characters from a Word document and > paste into Excel. Excel does not recognise the Turkish charcters and they > appear as "?". ...

Too many different cell formats #2
I am working with a large spreadsheet (with many worksheets) that will not allow me to change/add any more cell formats and limits my additional data entry. The message pops up "Too many different cell formats". Per Microsoft's support page 213904, they instruct you to reduce the number of formats to standard formats, etc. I've tried to do that but any change backwards to standard formatting is evidently the same as new change. I'm stuck. There must be a trick to quickly change each worksheet back to a standard format. Any help would be appreciated. Excel 2003o...

compact database at run time
hi How can i compact my access database programmatically .. In MSDN they gave a code like #import "C:\PROGRAM FILES\COMMON FILES\System\ado\Msado15.DLL" #import "C:\PROGRAM FILES\COMMON FILES\System\ado\MSJRO.DLL" no_namespace try { IJetEnginePtr jet(__uuidof(JetEngine)); jet->CompactDatabase( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\nwind2.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\abbc.mdb;" \ "Jet OLEDB:Engine Type=4"); } catch(_com_error &e) { ::MessageBox(NULL, (LPCTSTR)e.De...

Format mask for phone and Zip code
Hi, I want to know if somebody know if existing a ISV or something else to format Zip code and phone? like: xxx-xxx-xxxx Thanks, Sylvie ...

Can the last online backup time be seen in E2K as in E2K3?
Hello, In ESM on E2K3, the time of the last online backup is reported in ESM - is the same info available in E2K or must I check NTBackup's logs? Thanks, - Alan. This is only available in 2003 in which the IMailStoreDB extended the property to include the LastFullBackupTime property. Glen has a neat script that will query this property. You can also create a script to query and email event 221 as well. If you are interested in this, let me know and I could put something together for you. http://gsexdev.blogspot.com/2004/12/finding-when-exchange-store-last.html James Chong MCSE M+, ...

Restrict view to Salary information in Pay Code lookup
Hi, We would like people to enter timesheets on other peoples behalf (Proxy functionality). We’ve determined that users need to assign delegates in PDK because Business Portal does not have a proxy function. The problem using PDK is that a user can see sensitive salary information in the Pay Code lookup window. Is there a way to restrict their ability to see only the pay code and not the salary in the lookup window or is it possible to add the ability for a user act as a proxy for other users in Business Portal? -- Mike G ...

Unicode, CRichEditCtrl, SetWindowText and Paste
I have a CRichEditCtrl in a Unicode application that will not display Khmer when I use SetWindowText but will display it when the same text is pasted from a Word document. Many other languages (e.g. Japanese, Chinese, French, Arabic) will display properly with either method of getting the text to the control. I thought that the control might not have the correct font loaded when I call SetWindowText. (Perhaps Cut&Paste somehow selected the font for me. Yes, I am ignoring the fact that the other exotic languages worked with SetWindowText.) Therefore I created a font (Cre...

Case Modified Date/Time update on changes
Having the ModifiedOn date and time update on changes to related notes, activities or even just the OnSave event would be much more useful when reporting on neglected cases. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Com...

Report based on query based on many tables
Hello all. I've tried for a while to get this to work, but I haven't been able to find a solution yet: These are my tables: Classes, Jobs, Personnel, Crew, TrainingHistory. Classes is a list of classes offered, ClassName being the primary key. Jobs contains JobOrderNumber(PK), StartDate, and EndDate. Personnel contains Name(PK), and Department. Crew is the junction table between Jobs and Personnel and has Name, Position, and JobOrderNumber, where Name and JON form the PK. TrainingHistory is the junction table between Classes and Personnel and contains Name, ClassName, and DateTake...

Past 30 montha
I have many entries of data in an Excel spreadsheet. I add to it every day and would like to create a chart. Is there a way that my chart can update every time I add a new entry? Thank You -- NotGood@All Hi, You can create a named range, see here for more details http://peltiertech.com/Excel/Charts/Dynamics.html Or you could make your range a List, Table in xl2007. Charts based on these auto expand. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "NotGood@All" <NotGoodAll@discussions.microsoft.com> wrote in message news:913859C6-B722-4AB7...

Complicated Time Formula
Hello, This is a bicycle ride log, and I am having a really hard time with some time formulas. I'm trying to calculate three metrics (denoted by ?) based on the three data points that my bike speedometer provides (please see table below). Miles 27 +Ride Time ? +Rest Time ? =Total Time 2:15:49 Total Time Avg Speed ? Ride Time Avg Speed 12.4 Can anyone help? I sort of calculated the total time with =C8/(HOUR(C12)+MINUTE(C12)/60+(IF(SECOND(C12)>30,1/60,0))), but I didn't handle the seconds very w...