Issue with blanks and spaces

Discussion in 'Excel Programming' started by L. Howard, Mar 16, 2014.

  1. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 24 Mar 2014 09:43:56 -0700 (PDT) schrieb L. Howard:
    have another look for the workbook.


    Regards
    Claus B.
     
    Claus Busch, Mar 24, 2014
    #41
    1. Advertisements

  2. L. Howard

    L. Howard Guest

    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 24, 2014
    #42
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Mar 28, 2014
    #43
  4. L. Howard

    Claus Busch Guest

    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.
     
    Claus Busch, Mar 28, 2014
    #44
  5. L. Howard

    L. Howard Guest


    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
    #45
  6. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Mar 28, 2014
    #46
  7. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:
    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
    #47
  8. L. Howard

    Claus Busch Guest

    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.
     
    Claus Busch, Mar 28, 2014
    #48
  9. L. Howard

    L. Howard Guest

    Okay, Got it. Sure do thank you for the little clean ups of the code.

    Howard
     
    L. Howard, Mar 28, 2014
    #49
  10. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 9, 2014
    #50
  11. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 03:43:16 -0700 (PDT) schrieb L. Howard:
    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
    #51
  12. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 13:27:35 +0200 schrieb Claus Busch:
    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.
     
    Claus Busch, Apr 9, 2014
    #52
  13. L. Howard

    L. Howard Guest

    The Headers are incorrect, I should have cleaned that up before sending. Sorry, that is bad info.

    Howard
     
    L. Howard, Apr 9, 2014
    #53
  14. L. Howard

    L. Howard Guest

    in one loop you only get 20 phrases in columns AE:AO. So you have to
    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
     
    L. Howard, Apr 9, 2014
    #54
  15. L. Howard

    Claus Busch Guest

    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.
     
    Claus Busch, Apr 9, 2014
    #55
  16. L. Howard

    L. Howard Guest

    Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point.

    Regards,
    Howard
     
    L. Howard, Apr 9, 2014
    #56
  17. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 07:33:18 -0700 (PDT) schrieb L. Howard:
    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.
     
    Claus Busch, Apr 9, 2014
    #57
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.