+ Reply to Thread
Results 1 to 22 of 22

Macro to select longest column (date period)

  1. #1
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Macro to select longest column (date period)

    Hello everyone,

    Could you help me to do a macro that permits me to selecti the column with the largest period?
    For example in the file attached, I have the quotes of 3 stocks, the column A and C has a longer period than the E.
    So i'd like to select the longest period (in this case A or C) in order to paste after the quotations with only one date in the beginning of a table

    biggestperiodselection.xlsx

    Thank you very much in advance
    R
    Last edited by bryan444; 12-03-2012 at 06:21 AM.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to select longest column (date period)

    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Thanks a lot for your help Patel!! I really appreciate
    Now is there a way to generalize it?
    For example if there is more than 3 stocks ( or less!)

    Best regards

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    maybe like this:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to select longest column (date period)

    why + 1 ?
    WorksheetFunction.Max returns 0 if arguments are not numbers, in this case var is a string array

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, patel45,

    youīre right but I think I should avoid the array here (adapted code for 6 columns to check):
    Please Login or Register  to view this content.
    Ciao,
    Holger

  7. #7
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    I'd like to do a vlookup with the longest period and put the values of the other stocks
    and with the possibility with no limit of number of stocks (or if not lets say 80 stocks)
    Is it possible to generalize the formula?

    REgards

  8. #8
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Actually I undertood one thing (that's why I also opened another thread)
    When we specify the period with bdh on one row, it automatically completes the rest (without having to type it)
    So my idea is to copy paste in values then do this Vlookup thing selecting the biggest period of all the selected stocks than to put it on a chart

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    ...with no limit of number of stocks
    The limit is the number of Columns available. As long as you only need the number of the column with the most entries you can store the variable lngCounter to lngCol and have that. The upper limit of the loop is set to 11 right now, it could be augmented to Columns.Count which would equal the number of columns availabe in the sheet. As I guess that not all columns will be used you might use
    Please Login or Register  to view this content.
    lngCol holds the number and could be used, the last line of code was to display the results.
    I'd like to do a vlookup with the longest period and put the values of the other stocks
    What VLookup? Or better: VLookUp on what with which value? And from which column?
    Actually I undertood one thing (that's why I also opened another thread)
    When we specify the period with bdh on one row, it automatically completes the rest (without having to type it)
    Thereīs no automation in your workbook (which canīt be as it is in FileFormat macrofree), and if starting a new thread solves your problem itīs okay for me (but it might be hard to follow for other users if no links to any other related threads are delivered). Autocompletion can be achieved by using the Worksheet_Change-event if you type in any data into a wanted column and leave the cell.

    Ciao,
    Holger

  10. #10
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    I'd like to select the longest column that concern dates

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    to what I learned about VBA there is hardly a reason to select or activate anything. From your sample Dates are listed in the odd columns, will that be any different in your real workbook? If so you could step through all columns and check if FP is part of the header:
    Please Login or Register  to view this content.
    Like stated before lngCol holds the number of the column and could be used for further action (here colour the cells in that range):
    Please Login or Register  to view this content.
    Ciao,
    Holger

  12. #12
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Hahobe
    That's awesome
    and great explanation!!
    Thanks you very much I really appreciate!!!

  13. #13
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    I'm sorry just a question what is FP? it works but just to know ;-)

  14. #14
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Oh just understood..
    actually it is possible that I may have in the future names without FP...
    maybe I have to generalize it to "header with text"

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    I was relying on your sample workbook for teh code. If you need to check for dates you should alter that code maybe to something like
    Please Login or Register  to view this content.
    HTH,
    Holger

  16. #16
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Hahobe
    Thanks a lot! I'll check this and tell you :-)

  17. #17
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Hahobe
    Thanks a lot!!
    There is an error when I put this code with this
    Please Login or Register  to view this content.
    Please find the updated file here https://docs.google.com/open?id=0B1X...UxlYm1iNjBKUk0

    Best regards

    R

  18. #18
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    if I narrow down the code to just the loop and the copying thereīs no error showing up in the sample (Range is being copied) - what Error Number is being displayed to you?

    Ciao,
    Holger

  19. #19
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Hi thanks for your help again
    I have Runtime error 1004
    application defined or object defined error

  20. #20
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)


  21. #21
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to select longest column (date period)

    Hi, bryan444,

    1004 is a remarkable error message as there are mulitple reasons for that error to be thrown. And even with looking at your workbook I donīt see the reason for that exception to come up. Iīll try to figure out but am not sure to come up with a possible solution to this.

    Ciao,
    Holger

  22. #22
    Forum Contributor
    Join Date
    10-18-2012
    Location
    ardeduck schools
    MS-Off Ver
    Excel 2003
    Posts
    100

    Re: Macro to select longest column (date period)

    Thanks a lot Hahobe! :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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