Posts by Ingo_Ingo

    Thanks for the answer, but I do not see how it could help me, that link.. I know that site.
    I do not have access to the source file - it is "read only" - I would have wanted a code to do the processing - in memory - and then write it directly to the file where I run the code.
    It seems difficult if nobody could answer.

    Hello,


    I would need a little help.
    I have to copy some of the numbers in the "Data" sheet, column A of the source.xlsx file. Here are usually between 500 to 12000 number
    From here, only the numbers starting with 25 must be copied, in the "Import" sheet, column A, of the file Final.xlsm
    If is not to complicate need to sort the number copied.
    In each file the data starts with A2 because in A1 is the header


    Thanks.

    OK Alan, If you can't help me then maybe other people from here understand what I need.


    Please tell me where in your code is the line to get unique year... because with your code is listed all the year like this:
    1993
    1993
    1993
    1993
    ...
    ...
    1994
    1994
    1994
    and so on


    and we need to have like this:


    1993
    1994
    1995
    1996
    1997
    ...
    ....
    2018

    Alan,


    Can not upload the original file because of the confidential data it contains.
    The problem is simple. To not work directly in the datasheet, we put formulas (like "= IF (Date! A2 =" "," ",Date! A2") in A2 in the test sheet.
    Range with formulas is A2: A4500 and range with data is A2: A3775.
    In the separate column, we want to appear unique years in column A. These years will be used for displaying in DV from cell C2. In C2 we can choose one of the (unique) years.
    In DV from cell E2 we want to choose one of the date (from column A) that corresponds to the year chosen in C2.
    Ex. In DV, in C2 I choose 1993, and in Dv in E2 I should have all the data (from column A) for that year.


    Thanks for your patience and understanding.

    The mismatch error on this line of code happens because on line 75 you don't have any data that matches a year/date. Change that and the error goes away.


    No. I run VBA code in original file where are no empty cells...
    The data in column A is the result of some formulas that copy them from another sheet,
    and the error comes from the fact that the copied rank is higher than the data range.
    However, the VBA code copies many years and we need unique years, as we have specified, several times.
    Can you help me with this?

    Alan,


    I do not need to list Unique Years in Column C.
    Years can be listed anywhere beyond the AA column, and in C2 I have DV with unique years in column A.
    We also identify the data for the selected year in C2, to choose the date in DV from E2.


    You can list the years in - let's say - the AB column then remove the duplicates, so that only the unique years remain. This list will be used for DV from C2
    In the column - let's say - AC, only data that corresponds to the year chosen in C2 will be removed. This list will be used for DV from E2.
    (DV = data validation)


    Thank you.

    Hi, Alan,


    the code lists the years (not only the unique years) and I get the error: "Run-time error" 13: Type mismatch "and highlight the code line:
    "Range (" C "& i) = Year (Range (" A "& i)
    and stops here.

    Alan,


    Thank you for VBA code.


    The date are like this: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 84"]

    [tr]


    [TD="align: right"]08.08.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11.08.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12.08.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]19.08.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]05.09.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]10.09.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]19.09.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]03.10.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]09.10.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14.10.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]31.10.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13.11.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]20.11.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]02.12.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17.12.1993[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16.01.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]20.01.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]27.01.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]02.02.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]03.02.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]10.02.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]10.03.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]22.03.1994[/TD]

    [/tr]


    [tr]


    [TD="align: right"]29.03.1994[/TD]

    [/tr]


    [/TABLE]
    ...
    ...


    Now my boss want this date in column A (not in D)
    In C2 he want a DV with unique year in column A


    Like:
    1993
    1994
    ....
    ....
    2018


    In E2 he want a DV with all date from column A but only for year selected in C2


    Say in C2 he choose 2011 and he wants in E2 to choose one of date from year 2011.
    (It is possible to create that DV directly from VBA code?)


    Sorry I was not explained clear.
    Thank you very much.

    Hello,


    Please help me to create a VBA code that will do the following:
    In column D, data is given in dd.mm.yyyy format. Data is sorted ascending but not consecutive. Using a VBA code, I want in the cell E2 (where is the validation date) to have the years for those data, (single years), and in cell F2 to choose all the data in column D for the year chosen in E2.
    Thank you in advance.

    Hi,


    Why you need the 4[SIZE=10px]th[/SIZE] ComboBox? Here is a simple cell in which it will appear - regardless of the previous selections, of the 3 ComboBox - Red, Yellow or Green.
    Then, you need a solution with formula or code VBA?

    Hi,


    The VBA code work well.
    VLOOKUP don't find ddd, fff and zzz and show error.


    Change in your code this line:


    .FormulaR1C1 = "=VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE)"


    with this


    .FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE),"""")"


    but it depends on the excel version.