+ Reply to Thread
Results 1 to 24 of 24

VBA code, select conditional formatting content and transpose Sorted to other sheet

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi all !!


    I'm still study VBA, and yes i learn a lot, but this sheet came to me and certainly i know how to do some stuff
    and on mid of this there is a "missing link" (mentally speaking) that i don't know how to associated with Conditional formatting.

    I'll ask you 2 question in one.

    Question 1 : I'm asking for help to code for only 1 zone
    Question 2 :what line of code do have to modify in order to include 3 zones, perhaps 9 zones ?

    I'm doing this 2 question because of my learning, and i would like to know why and how lil things change when using VBA.
    and also i want to apply by my self to 8 sheet with at least 50 zones in total.

    Please i need help on this :
    ====================
    -.Any Highlighted Numbers (i'm using 5) from Column A3 :38
    that have "Y" on Column C3:C38 of Sheet "R-2" must
    -.Transpose "A2" content Starting on Single cell "I5" of Sheet "T2"
    -.Transpose Sorted Highlighted Numbers "A3:A38" Starting on Single Cell
    "J5" Sheet "T-2"

    -.The Formats of Transpose numbers to Single Cell can be with "," or "-" and Sorted
    example:
    3-5-13-24-28 (Please see Sheet "T1 ")
    3,5,13,24,28

    Next step
    =======
    -.If The "Y" from Column C3:C38 (Sheet R-2) are removed and Place new sets of "Y" to select new sets of numbers,
    Then
    -.Transpose "A2" content to next Available Single cell "I6" of Sheet "T2"
    -.Transpose Sorted Highlighted Numbers "A3:A38" to next Single Cell
    "J6" Sheet "T-2"

    "R2 " Sheet Specs
    ==============
    -.This Sheet have 9 Zones, (27 columns)
    -. Each Zone have 3 Columns, (example in Zone 1 (A3:38, are linked to C3:C38 trought conditional formatted.)
    -.First Column zone 1, A3:A38 Have numbers from 01 to 36 where
    Numbers get highlighted If in 3rd Column C3:C38 i type the Letter "Y"
    (formula used in conditional formatt is : ISNUMBER(SEARCH("Y",C3))=TRUE
    -.2ND Column "Score" (B3:B38) i use as a reference, is not Important


    what this sheet do :
    ===============
    -.If in zone 1, anywhere Column C 3:C38, i type the Letter "Y" , (no quote)
    e.i in Rows C3, C5, C6, C9, C11,
    Then Column A with Numbers 03, 05,13,24,28 Get Hilighted .

    Thank you a lot in advance..


    Best Regards

    David
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,

    I wrote it step by step and commented rather well so hope it is rather easy to understand.

    first main working procedure:

    Please Login or Register  to view this content.
    As you can see it has one argument so cannot be called directry from the worksheet/button but has to be called from other procedure. for instance clear all in T-2!I5:J29 and call:

    Please Login or Register  to view this content.
    (both test() and copy_selected_from_column(input_col As Long) procedures shall be in standard module - can be the same or different modules - does not matter).

    If you are satisfied with the result, clear T-2!I5:J29 again and call next procedure (again inserted in standard module):

    Please Login or Register  to view this content.
    Note that in your sample data you had no Y's for Zone2 - you will get a message box that there was no five Y's in F3:F38.

    Best Regards,
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi Kaper,

    I knew (i didn't knew, i read on few sites, including MS ) that apply VBA on Conditional format is not that easy..

    Great job, Yayyyy !!!!


    Have a wonderful day !!!


    Best Regards

    David.

  4. #4
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Thumbs up Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi Kaper,

    I knew (i didn't knew, i read on few sites, including MS ) that apply VBA on Conditional format is not that easy..

    Great job, Yayyyy !!!!


    Have a wonderful day !!!


    Best Regards

    David.

  5. #5
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi forum administrator,

    for some reason since yesterday, when i post a reply , the system ask to wait 30 seconds,(as if i already posted)
    i wait and when posted, (as usual) the reply is duplicated..

    I'm sorry, i'm not doing on purpose..


    Thank you


    Best regards

    David

  6. #6
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi forum administrator,

    for some reason since yesterday, when i post a reply , the system ask to wait 30 seconds,(as if i already posted)
    i wait and when posted, (as usual) the reply is duplicated..

    I'm sorry, i'm not doing on purpose..


    Thank you


    Best regards

    David

    P.S
    As now, i posted this msgs,and seems that system didn't posted, it get me to another screen, with message on top :

    The following errors occurred with your submission
    This forum requires that you wait 30 seconds between posts. Please try again in 29 seconds.

    Sorry again.

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Cool Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David.
    Run the code and see if that is what you were trying to achieve...
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Check the attached file..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Okay a little change ( I hope I have understand your code as you did )
    Please Login or Register  to view this content.
    The following code has not been changed... ( I don't think it need to ..)
    Please Login or Register  to view this content.
    Regards,
    Last edited by Vikas_Gautam; 10-27-2014 at 08:34 AM.

  9. #9
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi Vikas_Gautan,

    Yes i have try and It work perfect with the sample file, but for some reason won't work on the big (original) file i'm playing with it.
    everything is exact the same (comma, parenthesis,quotes, underscore, sheets names, etc,etc)
    i think it have to do with some conditional format or a hide column or something.. still checking

    Thank you so much !!

    David

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,

    The code you mentioned:
    Please Login or Register  to view this content.
    shall work fine with data in columns V, AE, AN,... etc. Are columns names (letters) corrrect?
    How about rows: Does data in R-2 sheet starts in row 3 and extends 36 rows down?
    is the data to be collected (marked with Y in column V, AE, ...) located in second column left from column with Ys?
    this second left is used here:
    Please Login or Register  to view this content.
    so as it is above, it takes values from columns T, AC, AL, ... (when there is Y in column V, AE, AN, ...)

    If you would rather like to have values from N, W, AF,.... then change this line to:
    Please Login or Register  to view this content.
    Hope it helps,

  11. #11
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi kaper,

    I don't know how to use an easy explanation, but let me try..


    I'm going to use the same code 4 times to grab Y'S from 4 different columns for each steps.
    The code work fine when (first zone or first 9 column at the left on sample file)
    For example from 9 steps, lets concentrate in only 1 (first Step)
    -.String are in AB3:AB38
    -.The first sets of Y's are in AD3:AD38
    -.The Heading Id are located in AB2
    at this point the Code work perfect, it export to sheet T-2 the String marked with Y'S and heading ID !!

    But Now, on same First Step (of 9) i need to do the same as above but using same Column string (AB3:AB38)
    and different column with Y'S and heading

    -.Use same String AB3:AB38
    with
    -.Use Y'S from AF3:AF38
    -.Use AE2 heading Id

    Then

    -.Use same String AB3:AB38
    with
    -.Use Y'S from AH3:AF38
    -.Use AG:2 heading Id

    and finally

    -.Use same String AB3:AB38
    with
    -.Use Y'S from AJ3:AF38
    -.Use AI:2 heading Id

    So the question is : which line value do i have to change to achieve this?


    Thank you !

    David
    Attached Files Attached Files
    Last edited by david gonzalez; 10-29-2014 at 10:36 PM. Reason: edit row number with extra letter

  12. #12
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi,
    As AD is column 30, and AF is column 32 you shall in Sub Sel_Second_Right_Y
    make your loop:
    Please Login or Register  to view this content.
    then corrected code in Sub copy_selected_from_column_2 would be:
    Please Login or Register  to view this content.
    note: inputcol - 4 because inputcol is 32 (AF) while numbers are from AB (AF-4) and header shall be from AE (so inputcol-1):
    Please Login or Register  to view this content.
    of course for third similar (in Sub Sel_Third_Right_Y):
    Please Login or Register  to view this content.
    then in copy_selected_from_column_3:
    Please Login or Register  to view this content.
    and exactly the same as above:
    Please Login or Register  to view this content.
    Probably I do not have to write it, that for fourth
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    would do the job.

    This approach is implemented in first attached file. But as we do all the time the same (side comment - would be even easier if columns AB and AC, AK and AL etc were in oposite sequence, we could skip all that play with offset_to_header and where_header variables), it is wise to do all actions within another loop. For instance:
    Please Login or Register  to view this content.
    This approach is used in second file. Results are the same, but this second one is much more compact and easier to correct if for some reason input data layout changes somewhat.
    Attached Files Attached Files
    Last edited by Kaper; 10-31-2014 at 02:31 PM.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Zones_K32.xlsb Here's the R-2 with added columns.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  14. #14
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,


    please find attached the other zone's sheet.




    Best Regards

    David
    Attached Files Attached Files

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Please Login or Register  to view this content.
    Hi David,


    These will get you the score line but I've lost the numerical order for the Ticket

  16. #16
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,

    I ran the code, and the numbers marked with Y's are copied correct to sheet 'Tickets'
    that's all, is exactly what i was in need (to modify) and You did't

    Except :
    -the word 'score' and the 'string' not going in Sheet tickets.
    -need the numbers sorting with a leading '00'

    I'm going to study Kaper code and yours because I'm curious about where was the point where i was failing, and i'm going to learn from your expertise for near future




    best regards

    David
    Last edited by david gonzalez; 11-28-2014 at 08:34 PM.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,

    Good for you - I'll look at the exception tomorrow

    Orrin

  18. #18
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,

    This code (post 15) might is not exactly what i need right now, But this code is great !!!!!!!
    wow, you went ahead on this idea .
    the code get the string and write on next line (bellow) the score value that belong to each string....
    (now i understand why string are not sorted..)

    I'm saving this code for near use... Indeed!!!

    Thank you XLAdept..


    Regards

    David

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,


    I've formatted the output the way Kaper did and I've sorted the output - if you don't want the scores posted then just comment out the red lines

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,

    the code is copied, but how do i call it?,
    I added the argument from Kaper and it got a message 'Compile error, Sub or Function no defined'
    Please Login or Register  to view this content.

    I think I'm doing something wrong...


    regards

    david

  21. #21
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,

    And also i tried with this one bellow and i got a message : 'Compile error Argument no optional'
    Please Login or Register  to view this content.

    Regards

    David

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,

    This code is from Post#15:

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi XLAdept,

    Yes sir, absolutely right !!!!!!!!


    Thank you so much, and i'm sorry, i feel that so lately i'm lil more dumb as usual....
    did you noticed?..

    Thank you !!!!!!!

    Best regards
    David

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code, select conditional formatting content and transpose Sorted to other sheet

    Hi David,

    No - not dumb - we all have these moments, all of us who can be intent on our objective anyway

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] include conditional formatting with TRANSPOSE function?
    By LizzyJ in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-28-2014, 04:48 AM
  2. Need VBA code to select entire content of word document - excel related
    By derryt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2012, 07:33 AM
  3. Cell Formatting Issue -Sheet Agreement When Sorted
    By rosaf in forum Excel General
    Replies: 9
    Last Post: 06-01-2009, 11:04 AM
  4. Conditional Formatting - Drawing Lines Between Sorted Groups
    By Sam via OfficeKB.com in forum Excel General
    Replies: 2
    Last Post: 05-26-2006, 10:10 AM
  5. [SOLVED] Conditional formatting not getting sorted
    By Lon Sarnoff in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-14-2006, 01:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1