Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
I am running code that produces a huge list (short phrases) in column A2 and down.
In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells. However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces. I am fairly certain manipulating the data in column A is the better way to cope with the "blank looking" cells that have the spaces. Where I am stumped is the cells with the short phrases have spaces that are needed. So a blanket remove spaces from column A would not work. I cannot figure how to distinguish the good spaces from the bad spaces then remove the cells with the bad spaces. The end result being a list of the phrases in A2 and down with no blanks. I looked for examples of filter code and for If IsText but did not come up with anything. Also, I cannot be sure it will always be 5 spaces each time. Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
L. Howard wrote:
I am running code that produces a huge list (short phrases) in column A2 and down. In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells. However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces. I am fairly certain manipulating the data in column A is the better way to cope with the "blank looking" cells that have the spaces. Where I am stumped is the cells with the short phrases have spaces that are needed. So a blanket remove spaces from column A would not work. I cannot figure how to distinguish the good spaces from the bad spaces then remove the cells with the bad spaces. The end result being a list of the phrases in A2 and down with no blanks. I looked for examples of filter code and for If IsText but did not come up with anything. Also, I cannot be sure it will always be 5 spaces each time. At the spot where you put the text ino the cell, try something like this: If Len(Trim$(random_phrase)) 0 Then ActiveCell.Value = random_phrase -- Thanks for the feedback, dream-killer. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Sun, 16 Mar 2014 16:06:20 -0700 (PDT) schrieb L. Howard: I am running code that produces a huge list (short phrases) in column A2 and down. In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells. However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces. can we see your code or your workbook? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Sunday, March 16, 2014 11:26:51 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Sun, 16 Mar 2014 16:06:20 -0700 (PDT) schrieb L. Howard: I am running code that produces a huge list (short phrases) in column A2 and down. In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells. However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces. can we see your code or your workbook? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 can we see your code or your workbook? Regards Claus B. -- Thanks Auric, I tried to incorporate your suggestion but couldn't make it work. Changed the random_phrase to Tlt in the Sub Titles_Col_A and to (b) in the other code Claus wrote but no go. Not even sure I had it in the correct spot. Claus, you may recognize the major code here. If you look on the sheet at cell D46 you will see a purple section that is blank. This is what causes the 6 spaces in the column A output. The blank section (maybe two sections) would be normal use. I tried putting a "false" character in each purple cell, * for instance, and then tried a 'find and clear contents' to try to get it to a true blank but just isn't my day I guess. Thanks for taking a look. https://www.dropbox.com/s/z7zqw8odbl...p%20 Box.xlsm Howard I often get an error message about trouble posting and to try again later. Wonder if it is something on my end or what?? Has a Reload option that wipes everything out and I have to start over again from scratch. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Woops, I left this line in the code, which runs amok, delete it and click the Titles button to produce the list in column A
If Len(Trim$(Tlt)) 0 Then ActiveCell.Value = Tlt For Each Tlt In aeRng Range("A" & n).Resize(6).Value = .Transpose(.Index(Tlt.Resize(, 11), 0, Array(1, 3, 5, 7, 9, 11))) If Len(Trim$(Tlt)) 0 Then ActiveCell.Value = Tlt n = n + 6 Next Tlt Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard: If Len(Trim$(Tlt)) 0 Then ActiveCell.Value = Tlt why has range D46:N65 no data? Or how should it be filled? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Monday, March 17, 2014 2:14:59 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard: If Len(Trim$(Tlt)) 0 Then ActiveCell.Value = Tlt why has range D46:N65 no data? Or how should it be filled? Regards Claus B. On Monday, March 17, 2014 2:14:59 AM UTC-7, Claus Busch wrote: Hi Howard, Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard: If Len(Trim$(Tlt)) 0 Then ActiveCell.Value = Tlt why has range D46:N65 no data? Or how should it be filled? Hi Claus, Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase. I don't know exactly why it is blank, except the user may only want 5 individual short phrases in each result returned to column A. May also only want 4 short phrases in the column A return, so 2 of those colored ranges could be blank. You most likely picked up that each of those section (above and below the D46:N65 blank range) provide the words for a randomly selected phrase. So if all sections are filled with words then there would be 6 short phrases in each cell in column A. That is why I thought the solution should be manipulating the data in column A rather rewrite all the main code. The code works very well as written. Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 17 Mar 2014 05:19:17 -0700 (PDT) schrieb L. Howard: Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase. then fill range AQ only if there are values in range D:N. Instead of formula use following macro: Sub FillAQ() Dim rngC As Range Dim i As Long Dim myStr As String For Each rngC In Range("AQ2:AQ131") myStr = "" For i = 19 To 29 Step 2 If Len(Cells(rngC.Row, i)) 0 Then myStr = myStr & Cells(rngC.Row, i) & " " End If Next myStr = RTrim(myStr) rngC = myStr Next End Sub And change the Randomize macro to: Sub Six_By_Six_Title_Randomizer() '/ by Claus Dim a(19) As Variant, b, c, d, e, f Dim Small As Integer, Big As Integer Dim i As Long, j As Long, n As Long, k As Long Dim conT As Long Dim iI As Long Dim arrOut As Variant Dim myCol As Long Application.ScreenUpdating = False [AE2:AO2010,A2:A12100].ClearContents Small = 1 For conT = 1 To 100 For n = 2 To 112 Step 22 For k = 3 To 13 Step 2 Big = Small + 19 j = 0 For i = Small To Big a(j) = i j = j + 1 Next b = a: Randomize d = UBound(b) For c = 0 To d e = Int(d * Rnd) + 1 f = b(c): b(c) = b(e): b(e) = f Next Cells(n, k).Resize(rowsize:=20) = WorksheetFunction.Transpose(b) Small = Small + 20 Next 'k Next 'n FillAQ myCol = 31 For iI = 2 To 112 Step 22 arrOut = Range("AQ" & iI).Resize(rowsize:=20) Cells(Rows.Count, myCol).End(xlUp).Offset(1, 0) _ .Resize(rowsize:=20) = arrOut myCol = myCol + 2 Next Small = 1 Next 'conT Application.ScreenUpdating = True End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 17 Mar 2014 13:44:55 +0100 schrieb Claus Busch: Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase. have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for workbook "Title Builder Randomizer rev 2.0.xlsm" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for workbook "Title Builder Randomizer rev 2.0.xlsm" Regards Claus B. -- have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for workbook "Title Builder Randomizer rev 2.0.xlsm" Regards Claus B. -- Wonderful!!! I will be adamant to the user who has made this work so well. I should have known that it is best fix the problem at the source instead of patching up poor results. I'll study the old code and new to see if I can gather some insight on how you did that. Most of it remains over my head, but I pick up a little bit from time to time. Thanks Claus. Regards, Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 17 Mar 2014 10:07:46 -0700 (PDT) schrieb L. Howard: I'll study the old code and new to see if I can gather some insight on how you did that. Most of it remains over my head, but I pick up a little bit from time to time. this code only reads cells with values into the array instead of 6 items. And so the range is resized with Ubound(arrOut) +1 you get no empty cells. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
this code only reads cells with values into the array instead of 6 items. And so the range is resized with Ubound(arrOut) +1 you get no empty cells. Regards Claus B. -- So I'm thinking that is done here where the space " " is replaced with nothing "" and if still greater than 0 it means there is text so it reads it into the array. If Len(Replace(Tlt.Offset(0, i), " ", "")) 0 Then ReDim Preserve arrOut(j) arrOut(j) = Tlt.Offset(0, i) j = j + 1 End If |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 17 Mar 2014 15:20:17 -0700 (PDT) schrieb L. Howard: So I'm thinking that is done here where the space " " is replaced with nothing "" and if still greater than 0 it means there is text so it reads it into the array. If Len(Replace(Tlt.Offset(0, i), " ", "")) 0 Then ReDim Preserve arrOut(j) arrOut(j) = Tlt.Offset(0, i) j = j + 1 End If yes, that is the only change I made Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
I need another nudge.
Want to transfer data workbook Y. A list of target worksheets in book Y is in column AU of the source workbook/worksheet. The target column for each target sheet is next to it in column AV. The code errors out on this line where I have put "c" and c in place of "what goes here??" Set wksTarget = wkbTarget.Sheets("What goes here??") Set wksTarget = wkbTarget.Sheets("c") Set wksTarget = wkbTarget.Sheets(c) None work. The Msgboxes both return a correct sheet name and a column number. Which is Allee & 1 as they are the first entries of the list. Thanks, Howard Sub Transfer_Titles() Dim myRng As Range Dim rngC As Range Dim i As Long Dim myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range Dim c As Range Dim trgWs As Range Dim trgCol As Long Set myRng = Range("A2:A12100") For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next '/ List of target sheet names in column AU2:AU21 '/ Destination column for each target sheet is in AV next to sheet name Set wkbSource = Workbooks("Title Builder Randomizer rev 2.0 xfer titles.xlsm") Set wkbTarget = Workbooks("Y.xlsm") For Each c In Range("AU2:AU21") MsgBox c trgCol = c.Offset(0, 1) MsgBox trgCol Set wksTarget = wkbTarget.Sheets("What goes here??") With wksSource wksTarget.Cells(2, trgCol).Resize(rowsize:=myRng.Cells.Count) _ = WorksheetFunction.Transpose(myArr) End With Next 'c End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Also, I added this in case the workbook Y was not open. If Not IsFileOpen("C:\Users\Howard Kittle\Documents\Y.xlsm") Then Workbooks.Open ("C:\Users\Howard Kittle\Documents\Y.xlsm") End If It does indeed open the workbook if not open and the code runs on down until it errors out as I posted above. However, the Msgboxes both return blank until the code is run again, then correct data is indicated. (of course it still errors out on that same line) I don't understand why it prevents the variables from returning in the msgboxes on the opening run. Howard |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Tue, 18 Mar 2014 14:41:45 -0700 (PDT) schrieb L. Howard: I need another nudge. Want to transfer data workbook Y. A list of target worksheets in book Y is in column AU of the source workbook/worksheet. The target column for each target sheet is next to it in column AV. Then try: Sub Transfer_Titles() Dim Dest As Range Dim i As Long Dim myArr() As Variant Dim arrDest As Variant Dim LRow As Long Dim wkbSource As Workbook, wkbTarget As Workbook LRow = Cells(Rows.Count, 1).End(xlUp).Row myArr = Range("A2:A" & LRow) '/ List of target sheet names in column AU2:AU21 '/ Destination column for each target sheet is in AV next to sheet name Set wkbSource = ThisWorkbook Set wkbTarget = Workbooks("Y.xlsm") arrDest = Range("AU2:AV21") For i = LBound(arrDest) To UBound(arrDest) Set Dest = wkbTarget.Sheets(arrDest(i, 1)).Cells(2, arrDest(i, 2)) Dest.Resize(rowsize:=UBound(myArr)) = myArr Dest.EntireColumn.AutoFit Next 'i End Sub Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 19 Mar 2014 09:11:24 +0100 schrieb Claus Busch: Enter a code line with With ThisWorkbook.Sheets("Title Builder") in case the sheet "Title Builder" is not the active sheet Then don't forget the dots in front of the ranges LRow = Cells(Rows.Count, 1).End(xlUp).Row if all sections in all colors are filled you have more than 12100 rows, otherwise you can have less. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
The target column for each target sheet is next to it in column AV. Then try: Sub Transfer_Titles() Dim Dest As Range Dim i As Long Dim myArr() As Variant Dim arrDest As Variant Dim LRow As Long Dim wkbSource As Workbook, wkbTarget As Workbook LRow = Cells(Rows.Count, 1).End(xlUp).Row myArr = Range("A2:A" & LRow) '/ List of target sheet names in column AU2:AU21 '/ Destination column for each target sheet is in AV next to sheet name Set wkbSource = ThisWorkbook Set wkbTarget = Workbooks("Y.xlsm") arrDest = Range("AU2:AV21") For i = LBound(arrDest) To UBound(arrDest) Set Dest = wkbTarget.Sheets(arrDest(i, 1)).Cells(2, arrDest(i, 2)) Dest.Resize(rowsize:=UBound(myArr)) = myArr Dest.EntireColumn.AutoFit Next 'i End Sub Regards Claus B. Oh yes! That works very quick. Nice. About 0.623 seconds to transfer 10,000 rows to twenty sheets. I wrote a couple of lines to recalc the data between each sheet transfer so that each sheet got a unique set of data and the time was 36 seconds. That seems quite reasonable to me given it is the recalc that takes the time, not the transfer. Thanks Claus. The array speed is always impressive. Howard |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Wednesday, March 19, 2014 1:22:46 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Wed, 19 Mar 2014 09:11:24 +0100 schrieb Claus Busch: Enter a code line with With ThisWorkbook.Sheets("Title Builder") in case the sheet "Title Builder" is not the active sheet Then don't forget the dots in front of the ranges LRow = Cells(Rows.Count, 1).End(xlUp).Row if all sections in all colors are filled you have more than 12100 rows, otherwise you can have less. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional Hi Claus, On a different sheet in the same project, I am trying to output column AQ to both column A and sheet 2 column B of the same workbook. This sheet is very almost identical to the Titles sheet except here it is returning up to six short phrases to the taget cells. This is one of many attempts to get it to write to sheet2. Is this a case like you describe above. Writes to sheet 1 just fine, the active sheet. Sub A2_Down_Copy() Dim lRowCount lRowCount = Cells(Rows.Count, "AE").End(xlUp).Row With Sheets("Sheet1").Range("A2").Resize(lRowCount) .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)": .Value = .Value End With With Sheets("Sheet2").Range("B2").Resize(lRowCount) .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)": .Value = .Value End With End Sub Thanks. Howard |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Wed, 19 Mar 2014 15:52:12 -0700 (PDT) schrieb L. Howard: This is one of many attempts to get it to write to sheet2. Is this a case like you describe above. I would do it with the formula only once. Try: Sub A2_Down_Copy() Dim lRowCount Dim myArr As Variant With Sheets("Sheet1") lRowCount = .Cells(Rows.Count, "AE").End(xlUp).Row With .Range("A2").Resize(lRowCount) .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)" .Value = .Value myArr = Range("A2:A" & lRowCount) End With End With Sheets("Sheet2").Range("B2").Resize(lRowCount) = myArr End Sub Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Wednesday, March 19, 2014 11:37:20 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Wed, 19 Mar 2014 15:52:12 -0700 (PDT) schrieb L. Howard: This is one of many attempts to get it to write to sheet2. Is this a case like you describe above. I would do it with the formula only once. Try: Sub A2_Down_Copy() Dim lRowCount Dim myArr As Variant With Sheets("Sheet1") lRowCount = .Cells(Rows.Count, "AE").End(xlUp).Row With .Range("A2").Resize(lRowCount) .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)" .Value = .Value myArr = Range("A2:A" & lRowCount) End With End With Sheets("Sheet2").Range("B2").Resize(lRowCount) = myArr End Sub Regards Claus B. -- Thanks Claus. That works well for me. Sheet 1 copy is perfect. The Sheet 2 copy was producing a ghost #N/A in row 2002. I did this and it went away. Sheets("Sheet2").Range("B2").Resize(lRowCount - 1) = myArr Row 2 and 2001 on both sheets are identical each time I test. 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. But like your code, always top notch. Thanks again. Howard |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard: Sheets("Sheet2").Range("B2").Resize(lRowCount - 1) = myArr that is my bad 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. do you want to skip blank cells? Can you send me the workbook? Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
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 |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Thu, 20 Mar 2014 13:42:20 -0700 (PDT) schrieb L. Howard: 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 I improved the code. Please have another look. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
On Thursday, March 20, 2014 2:11:01 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 20 Mar 2014 13:42:20 -0700 (PDT) schrieb L. Howard: 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 I improved the code. Please have another look. Regards Claus B. -- Yes, that looks great! I hope those codes will serve me well in finishing the workbook with the remaining sheets. At this point I don't know what all info on the remaining sheet is the stuff to bring to Publish. Terrific work in my eyes. Thanks much. Howard |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Claus,
Some advice please. The "Title Builder" and "Description Builder" worksheets are my focus here. I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete. 12,000 Titles and 2000 Descriptions to column A on their respective sheets. The GOOD news is that duplicates are very very rare. Have yet to find one on the Description sheet and Titles I usually get from 0 to 3. These are very acceptable results. The BAD news is the length of time to run the codes. I have tried Calculation set to manual on both codes and the time is very acceptable, about 1 second. The major BAD news with this is that the duplicates occur at a huge and unacceptable level. So I guess there are some necessary calculations not happening and the duplications occur. I'm open to suggestions, perhaps do those Titles and Descriptions calculations in a separate workbook/s and transfer the data to the proper columns on the main workbook..?? This is a Demo main workbook if you need to see anything on it. https://www.dropbox.com/s/vz387bfi02...p%20B ox.xlsm Thanks. Howard |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
This should say...
I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A. Howard |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Sun, 23 Mar 2014 19:06:47 -0700 (PDT) schrieb L. Howard: I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A. if I would know what the workbook should do I could help better. Do you need the columns AE:AO? I thought in column A should only be 1 sentence? Have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for workbook "Demo Work Book rev2.9" I changed the formulas in AQ and the code for "Six_By_Six_Title_Randomizer" and "CopyToA2_2_Titles" If you can work with this suggestion you can similiar change the rest of the code. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 24 Mar 2014 11:37:21 +0100 schrieb Claus Busch: Have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for workbook "Demo Work Book rev2.9" the newest version is "Demo Work Book rev2.10" I deleted rev2.9 because I misunderstood the problem Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked.
So, maybe the revised code with some more code at the end to remove duplicates is the answer. There would only be about 11,900 non duplicated entries but that might a workable solution. The goal is for Titles to produce 12,000 single phrases in column A, with no duplicates. Entries like this are considered a duplicate. Greetings to you! Ordering a King Std Top? On Hand 65% savings! Greetings to you! Ordering a King Std Top? On Hand 65% savings! Entries like these are not duplicates. BuenosDias - Fishing for a Contemporary Serta Queen Set? . $below retail. BuenosDias - Pursing a Current Beauty Rest-Bed? $$ under retail. Buenos dias! Buying that King Comfort Set? Full Line $$ below retail, The goal for Descriptions is to produce 2,000 entries in column A where each entry will be up to 6 individual phrases per entry. No duplicates allowed with Descriptions also. And it seems duplicates in the Descriptions are very rare. Probably because of the multiple phrases in each entry. As far as need the columns AE:AO, I cannot say. Speed of the code and avoiding duplicates is paramount concern. If they can be eliminated that is okay. I have tested the Titles in a one sheet workbook that does nothing but the 12,000 rows of single phrase entries, and the duplicates range between 0 and 4 on any given run of the code. The well written code spits the 12,000 entries out in about 1 second. That speed and that small amount of duplicates would be acceptable. I did notice in the workbook I sent that some of the comments in the code are inaccurate, my bad and I am sorry if that caused confusion. Best to ignore them and the goals for the code is as I have stated above or as close as we can get to them. Howard |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 24 Mar 2014 06:46:51 -0700 (PDT) schrieb L. Howard: I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked. I ran the Titles code also but I get a maximum of 4 duplicates. The message of "RemoveDuplicates" is wrong. Write in B2 (under "Len") =--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is through filter this column for 1. Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with blanks and spaces
Hi Howard,
Am Mon, 24 Mar 2014 15:57:57 +0100 schrieb Claus Busch: =--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is through filter this column for 1. here is a typo. The formula should be: =--(Countif($A$2:$A$12001;A2)1) Regards Claus B. -- Vista Ultimate SP2 / Windows7 SP1 Office 2007 Ultimate SP3 / 2010 Prodessional |
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) |