Dannygreen1992
New Member
- Joined
- Aug 5, 2018
- Messages
- 5
Hi All
I'm having a nightmare with a workbook, it has a whole bunch of amateur code, but it worked
however! the report this workbook imported its results from has been changed and the Date formats are acting crazy, swapping the months and days. The issue being that the workbook uses an Index Match formula and can no longer match the dates, it appears the new format adds a null value to each cell
The only thing that has worked is copying the cells into notepad and pasting back into the table, which I'm unsure how to write a module for as I've never activated or worked with notepad in VBA
I need a method of targeting a whole sheet and removing all null values, this is how they appear in the raw report:
Date
07/04/2019
08/04/2019
09/04/2019
10/04/2019
11/04/2019
12/04/2019
13/04/2019
And this is the outcome once the workbook has imported the data and made it into a table, he bottom cell is the only one showing the actual date, but the null value still ruins the index match:
04/07/2019
04/08/2019
04/09/2019
04/10/2019
04/11/2019
04/12/2019
13/04/2019
Cell as Date {dd-mm-yyyy}:
43650
43681
43712
43742
43773
43803
13/04/2019
Cells as text:
04 July 2019
04 August 2019
04 September 2019
04 October 2019
04 November 2019
04 December 2019
13/04/2019
Cells as date
Any and all ideas appreciated, again I can confirm every cell has a null value at the end of the string
Many Thanks
Danny
I'm having a nightmare with a workbook, it has a whole bunch of amateur code, but it worked
however! the report this workbook imported its results from has been changed and the Date formats are acting crazy, swapping the months and days. The issue being that the workbook uses an Index Match formula and can no longer match the dates, it appears the new format adds a null value to each cell
The only thing that has worked is copying the cells into notepad and pasting back into the table, which I'm unsure how to write a module for as I've never activated or worked with notepad in VBA
I need a method of targeting a whole sheet and removing all null values, this is how they appear in the raw report:
Date
07/04/2019
08/04/2019
09/04/2019
10/04/2019
11/04/2019
12/04/2019
13/04/2019
And this is the outcome once the workbook has imported the data and made it into a table, he bottom cell is the only one showing the actual date, but the null value still ruins the index match:
04/07/2019
04/08/2019
04/09/2019
04/10/2019
04/11/2019
04/12/2019
13/04/2019
Cell as Date {dd-mm-yyyy}:
43650
43681
43712
43742
43773
43803
13/04/2019
Cells as text:
04 July 2019
04 August 2019
04 September 2019
04 October 2019
04 November 2019
04 December 2019
13/04/2019
Cells as date
Any and all ideas appreciated, again I can confirm every cell has a null value at the end of the string
Many Thanks
Danny