Issue with blanks and spaces

Claus Busch · Mar 24, 2014

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.

L. Howard · Mar 24, 2014

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

L. Howard · Mar 28, 2014

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/ebd1f1ao4ul3pnn/Client Data Work Book _1 rev 3.1 Drop Box.xlsm

Thanks.
Howard

Claus Busch · Mar 28, 2014

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=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
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.

L. Howard · Mar 28, 2014

Hi Howard,

Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard:

I changed the code and RTrim the values

have a look:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

L. Howard · Mar 28, 2014

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

Claus Busch · Mar 28, 2014

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.

Claus Busch · Mar 28, 2014

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.

L. Howard · Mar 28, 2014

Hi again,

Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:

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

L. Howard · Apr 9, 2014

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 youcan 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!" thatwould 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/9x9ma0o2yz5ee2b/Location Builder rev 1.0 DB.xlsm

Thanks.
Howard

Claus Busch · Apr 9, 2014

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.

Claus Busch · Apr 9, 2014

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.

L. Howard · Apr 9, 2014

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

L. Howard · Apr 9, 2014

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

Claus Busch · Apr 9, 2014

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.

L. Howard · Apr 9, 2014

Hi Howard,

Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard:

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

Claus Busch · Apr 9, 2014

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.