Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
here is a typo. The formula should be: =--(Countif($A$2:$A$12001;A2)1) I re-downloaded you revised workbook and it works as you say. Using the Remove Duplicates feature agrees with the formula results in the test I did. About six tests. And I too never got more than 4 dupes. This is acceptable performance as far as I am concerned. I have studied the code for Titles and Descriptions but I cannot figure out what to change in the Description code to get 2 second performance like Titles code does. If I set the calc to manual, it seems to just repeat the first 20 entries until it reaches 2000 and quite fast. With cacl at automatic the code grinds along for about 4 minutes but I the entries are good, no dupes. Howard |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 24 Mar 2014 09:43:56 -0700 (PDT) schrieb L. Howard: I have studied the code for Titles and Descriptions but I cannot figure out what to change in the Description code to get 2 second performance like Titles code does. have another look for the workbook. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
have another look for the workbook. Regards Claus B. -- Well, that certainly looks like a winner. Code run time and duplicates are very workable. I don't know you do it!! A ton of thanks. Howard |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Monday, March 24, 2014 12:33:42 PM UTC-7, L. Howard wrote:
have another look for the workbook. Regards Claus B. -- Well, that certainly looks like a winner. Code run time and duplicates are very workable. I don't know you do it!! A ton of thanks. Howard Two bugs have popped up The first is with the Description Builder sheet out put to column A. Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row. Here are a few easier to fine examples. In A2 in the formula bar arrow to the right until you find "...select Matteresses.Most of our...". Should be a space between "...Matteresses. Most of our..." In A14 find arrow over to "...outlets.Much of our..." In A22 "...completion.A huge selection..." I cannot find in the data where or what would make that happen or with the code, whichever one it is that fails to put the space in. The other glitch has occurred with the code for CL PVA's sheet where it moves the top row to the bottom and then shifts all upward. TopRow = Rows(FirstRow) this line of code errors out. Here is a link if needed and you have the time to take a look. https://www.dropbox.com/s/ebd1f1ao4u... p%20Box.xlsm Thanks. Howard |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard: The first is with the Description Builder sheet out put to column A. Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row. I changed the code and RTrim the values TopRow = Rows(FirstRow) this line of code errors out. have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm" In Module2 is the code "Move". It is easier and shorter. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Professional |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Friday, March 28, 2014 1:24:32 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard: The first is with the Description Builder sheet out put to column A. Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row. I changed the code and RTrim the values TopRow = Rows(FirstRow) this line of code errors out. have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm" In Module2 is the code "Move". It is easier and shorter. Regards Claus B. The Description Builder is looking real fine. The Move code in Module 2 errors out on this line: Rows("3:" & LRow).Cut The code makes no mention to a sheet name, should I incorporate it in the code in Module 1? When the line errors out and you mouse over the line it refers to row 21 which looks correct on the CL PVA's sheet as it is the last row. Scratching my head on this. Howard |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Whoa! Hold everything! I saved and closed the workbook and then reopened it and everything seems to be working fine. Before I had a couple errors both in Titles code, clear line. and Description, clear line. The Move code was erring before, but now it seems to be just fine. Don't know what was going on. I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet. It might just be the late hour here clouding my mind... Howard |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard: I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet. sorry, my bad. I didn't refer correctly. Change the "Move" code to: Sub Move() Dim LRow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Sheets("CL PVA's") LRow = .Cells(Rows.Count, 1).End(xlUp).Row .Rows("3:" & LRow).Cut .Rows("2:" & LRow - 1).Insert Shift:=xlDown End With Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Professional |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi again,
Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard: I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet. if the macro is bound to the button on the sheet the sheet must be the active sheet to press the button. And code without refering always works on the active sheet. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Professional |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Friday, March 28, 2014 3:10:44 AM UTC-7, Claus Busch wrote:
Hi again, Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard: I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet. if the macro is bound to the button on the sheet the sheet must be the active sheet to press the button. And code without refering always works on the active sheet. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Professional Okay, Got it. Sure do thank you for the little clean ups of the code. Howard |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Claus,
A revisit to Description Builder if and when you have time please. First, all the code work you have offered works fine and does what was expected. The problem I'm looking at now may not have a practical fix. Duplicate returns were an issue to begin with, but after some usage that seems to be a non issue now. What is at issue is "identical like phrase elements" in close proximity to each other in the Column A list. The link should open to "Description Builder" sheet and that is the only sheet at issue. To illustrate what the problem is copy any one of the phrase elements in the orange range, and do a "Find All" on column A. With the box expanded you can see the cells that hold the chosen phrase element. Usually there are around 95 to 130 cells listed. The problem is that they are often only 15 to 30 rows apart. Crazy as it may seem, that causes a pretty serious problem/penalty further down the road with the use of the long 5 and 6 phrase row entries. So the question is from a practical stand point, can the code or the on sheet methods, be change to broaden the proximity of these phrase elements? I'm calling a 'phrase element' any of the text in the orange and other colored ranges like it. And these are the ones that wind up too close to each other. The long phrases are the five and six short phrases combined in a column A row entry. So if you would copy and find all for the entry of D6 "Come Visit Us!" that would be what I'm talking about. The first two cells with "Come Visit Us!" are in A13 and A26. That's too close. https://www.dropbox.com/s/9x9ma0o2yz...01.0%20DB.xlsm Thanks. Howard |
#52
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 9 Apr 2014 03:43:16 -0700 (PDT) schrieb L. Howard: A revisit to Description Builder if and when you have time please. can you further explain what should be done in this sheet? You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in D:N) and these phrases should be copied in AE:AO. So each column should only have 20 phrases. But in the header is written 200 and in the column are 2000. So what is right? And out of 120 phrases you can't make 2000 values in column A. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#53
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 9 Apr 2014 13:27:35 +0200 schrieb Claus Busch: can you further explain what should be done in this sheet? You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in D:N) and these phrases should be copied in AE:AO. So each column should only have 20 phrases. But in the header is written 200 and in the column are 2000. in one loop you only get 20 phrases in columns AE:AO. So you have to loop 100 times and get some duplicates. What about the time the macro needs for running? You could do more loops, write it all in one column and then create with Scripting.Dictionary unique values. I will check this later when I have time. But I don't know if that will reduce the duplicates. So you loop 100 times every value from D:N will occure 100 times. With more loops and creating unique values the occurence will not be reduced but there will be no same complete phrases. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#54
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
can you further explain what should be done in this sheet? You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in D:N) and these phrases should be copied in AE:AO. So each column should only have 20 phrases. But in the header is written 200 and in the column are 2000. So what is right? And out of 120 phrases you can't make 2000 values in column A. Regards Claus B. The Headers are incorrect, I should have cleaned that up before sending. Sorry, that is bad info. Howard |
#55
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
in one loop you only get 20 phrases in columns AE:AO. So you have to
loop 100 times and get some duplicates. What about the time the macro needs for running? You could do more loops, write it all in one column and then create with Scripting.Dictionary unique values. I will check this later when I have time. But I don't know if that will reduce the duplicates. So you loop 100 times every value from D:N will occure 100 times. With more loops and creating unique values the occurence will not be reduced but there will be no same complete phrases. Regards Claus B. The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A. Not sure if it is relevant, but the problem does not happen on the "Titles Builder" sheet where it uses the same data to generate 12,000 rows. But Titles only uses a single phrase per row in its column A. The code to transfer phrases to column A have some differences of course, transferring one single phrase for Titles vs. five or six for Descriptions, but I was wondering if that could be part of the solution. The Scripting Dictionary would be worth trying. If that or any other ideas fall short then that may just have to be the brutal reality of it all. What you've done so far is pretty darned remarkable to me, and trying to hit a moving target with code has to have its limits. Howard |
#56
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard: The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A. there is no chance to fit this behaviour. The dictance between those phrases should be greater if all colored fields in D:N were filled. Now you have only 220 phrases to create the descriptions. If all fields are filled you would have 720. Four colors have no values and yellow has only 100 instead of 120 Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#57
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Wednesday, April 9, 2014 7:07:07 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard: The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A. there is no chance to fit this behaviour. The dictance between those phrases should be greater if all colored fields in D:N were filled. Now you have only 220 phrases to create the descriptions. If all fields are filled you would have 720. Four colors have no values and yellow has only 100 instead of 120 Regards Claus B. Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point. Regards, Howard |
#58
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 9 Apr 2014 07:33:18 -0700 (PDT) schrieb L. Howard: Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point. to copy the values to A try following macro instead of CopyToA2_2_Descript: Sub CopyToDiscript() Dim i As Long, j As Long Dim myArr As Variant Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Sheets("Description Builder") For i = 2 To 1902 Step 100 For j = 31 To 41 Step 2 If WorksheetFunction.CountA(.Range(.Cells(i, j), _ .Cells(i + 99, j))) = 100 Then myArr = .Range(.Cells(i, j), .Cells(i + 99, j)) .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _ .Resize(100) = myArr End If Next Next myArr = .Range("A2:A2001") End With Sheets("Publish Data").Range("E2").Resize(rowsize:=UBound(myArr)) = myArr Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting blanks as spaces | Charts and Charting in Excel | |||
Issue removing leading and lagging spaces | Excel Programming | |||
How to count blanks and spaces that look like blanks | Excel Programming | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |