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.
Alan,
Thank you for VBA code.
The date are like this: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 84"]
[TD="align: right"]08.08.1993[/TD]
[TD="align: right"]11.08.1993[/TD]
[TD="align: right"]12.08.1993[/TD]
[TD="align: right"]19.08.1993[/TD]
[TD="align: right"]05.09.1993[/TD]
[TD="align: right"]10.09.1993[/TD]
[TD="align: right"]19.09.1993[/TD]
[TD="align: right"]03.10.1993[/TD]
[TD="align: right"]09.10.1993[/TD]
[TD="align: right"]14.10.1993[/TD]
[TD="align: right"]31.10.1993[/TD]
[TD="align: right"]13.11.1993[/TD]
[TD="align: right"]20.11.1993[/TD]
[TD="align: right"]02.12.1993[/TD]
[TD="align: right"]17.12.1993[/TD]
[TD="align: right"]16.01.1994[/TD]
[TD="align: right"]20.01.1994[/TD]
[TD="align: right"]27.01.1994[/TD]
[TD="align: right"]02.02.1994[/TD]
[TD="align: right"]03.02.1994[/TD]
[TD="align: right"]10.02.1994[/TD]
[TD="align: right"]10.03.1994[/TD]
[TD="align: right"]22.03.1994[/TD]
[TD="align: right"]29.03.1994[/TD]
[/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.
I am not understanding your needs. While pictures are nice, they are of little value in solving an issue. Suggest you upload a workbook showing a before and after sample of data. Mock up the after scenario manually so that we fully understand your needs.
I'm sorry I was not quite explicit.
In attach is a file with all explanation.
Thank you for your patience.
Option Explicit
Sub Ingo()
Dim lr As Long, i As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("C2") = Year(Range("A2"))
For i = 3 To lr
If Range("A" & i) <> Range("A" & i - 1) Then
Range("C" & i) = Year(Range("A" & i))
End If
Next i
For i = 2 To lr
lr2 = Range("E" & Rows.Count).End(xlUp).Row
If Year(Range("A" & i)) = Range("C2") Then
Range("A" & i).Copy Range("E" & lr2 + 1)
End If
Next i
End SubHi, 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,
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.
Anybody?
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?
I have tested in the sample workbook you have provided. It works for me. Perhaps the layout and data in your sample is not representative of your actual file Perhaps you should load your actual file for testing as I cannot determine what the issues are when you do not provide an accurate file to test.
Quote
error comes from the fact that the copied rank is higher than the data range.
This statement is unclear and I do not know what it means.
QuoteHowever, the VBA code copies many years and we need unique years, as we have specified, several times.
and I have provided for this in the sample workbook per the code provided. Again, show us the errors so that we can see what is happening as we are not mind readers and cannot see what you are seeing
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.
I'm sorry this is not working for you. I still do not understand your issues. If you are unable to upload a file that truly represents your original then I am no longer able to help. If you wish for me to continue, then upload your file and dummy up the confidential information so that I can deal with reality.
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
Don’t have an account yet? Register yourself now and be a part of our community!