Home |
Search |
Today's Posts |
|
#1
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 |
#2
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. |
#3
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 |
#4
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 |
#5
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 |
#6
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 |
#7
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 |
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) |