Hi Howard,

Am Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard:

I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.

is following code that what you want?

Sub CopyToA2()
Dim i As Long, j As Long
Dim myStr As String
Dim myArr As Variant

Application.ScreenUpdating = False
For i = 2 To 2001
myStr = ""
For j = 31 To 41 Step 2
If Len(Replace(Cells(i, j), " ", "")) 0 Then
myStr = myStr & Cells(i, j)
End If
Next
Cells(i, 1) = myStr
Next
myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBoun d(myArr)) = myArr
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

Hi Howard,

Am Thu, 20 Mar 2014 15:00:24 +0100 schrieb Claus Busch:

is following code that what you want?

if the result is what you want try following code. It is a bit faster.
If not please send me the workbook with the expected result.

Sub CopyToA2_2()
Dim i As Long, j As Long
Dim myStr As String
Dim myArr As Variant

Application.ScreenUpdating = False
myArr = Range("AE2:AO2001")
For i = LBound(myArr) To UBound(myArr)
myStr = ""
For j = 1 To 11 Step 2
If Len(Replace(myArr(i, j), " ", "")) 0 Then
myStr = myStr & myArr(i, j)
End If
Next
Cells(i + 1, 1) = myStr
Next
myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBoun d(myArr)) = myArr
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

Hi Claus,

https://www.dropbox.com/s/w7swpuj776...rop%20Box.xlsm

I think we are very close.

The sheets of concern at this time are Publish Data, Title Builder and Description Builder.

Title code needs to fill column A of Title sheet and column B of Publish.

Description code needs to fill column A of Description sheet and column E of publish.

Mostly it is doing that but the list seem to be correctly done but not identical.

That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.

The same for Description.

Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.

I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.

Thanks for taking a look.

Howard

On Thursday, March 20, 2014 11:00:00 AM UTC-7, L. Howard wrote:
Hi Claus,



https://www.dropbox.com/s/w7swpuj776...rop%20Box.xlsm



Ah crap. I just noticed the I have the Titles code screwed up and it is publishing what Description code is supposed to.

Title code should only be one phrase per row and Description is six.

Sorry, Claus, do you have enough info to deal with the mess I am causing?

Howard

On Thursday, March 20, 2014 11:07:41 AM UTC-7, L. Howard wrote:
On Thursday, March 20, 2014 11:00:00 AM UTC-7, L. Howard wrote:



I believe this is another screw up on my part.

I have changed:
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBoun d(myArr)) = myArr
To
Sheets("Sheet2").Range("B2").Resize(rowsize:=UBoun d(myArr)) = myArr
Thinking that was a destination...?

myArr = Range("A2:A2001")
Sheets("Sheet2").Range("A2").Resize(rowsize:=UBoun d(myArr)) = myArr
Application.ScreenUpdating = True

Hi Howard,

Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:

The sheets of concern at this time are Publish Data, Title Builder and Description Builder.
Title code needs to fill column A of Title sheet and column B of Publish.
Description code needs to fill column A of Description sheet and column E of publish.
Mostly it is doing that but the list seem to be correctly done but not identical.
That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.
The same for Description.
Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.
I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.

at the moment I am a little bit confused.

Should the list in column A or Title and column B of Publish be the
same?
Also for Description and Publish?
Or should be randomized between running the code?


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

On Thursday, March 20, 2014 11:18:42 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:



The sheets of concern at this time are Publish Data, Title Builder and Description Builder.

Title code needs to fill column A of Title sheet and column B of Publish.

Description code needs to fill column A of Description sheet and column E of publish.

Mostly it is doing that but the list seem to be correctly done but not identical.

That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.

The same for Description.

Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.

I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.



at the moment I am a little bit confused.



Should the list in column A or Title and column B of Publish be the

same?

Also for Description and Publish?

Or should be randomized between running the code?





Regards

Claus B.


Each time the code is run for Titles both destinations should be identical lists.

Titles column A on Titles sheet and column B on Publish sheet.

Descriptions column A on Descriptions sheet and column E on Publish sheet.

Howard

Hi Howard,

Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:

Each time the code is run for Titles both destinations should be identical lists.

Titles column A on Titles sheet and column B on Publish sheet.

Descriptions column A on Descriptions sheet and column E on Publish sheet.

I hope I understood you correctly.
Please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for workbook "Title Builder Randomizer rev 2.3.xlsm"


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

On Thursday, March 20, 2014 12:03:50 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:



Each time the code is run for Titles both destinations should be identical lists.



Titles column A on Titles sheet and column B on Publish sheet.



Descriptions column A on Descriptions sheet and column E on Publish sheet.



I hope I understood you correctly.

Please have a look:

https://onedrive.live.com/?cid=9378A...121822A3%21326

for workbook "Title Builder Randomizer rev 2.3.xlsm"





Regards

Claus B.

--

That is looking pretty good. I still need the Title portion to produce a single phrase instead of six phrases.

This link demonstrates what the Title output should look like.
Note column A entries are just one phrase.


https://www.dropbox.com/s/rftplrkmdw...20Example.xlsm

Howard