vba help - Fill Blank cells of above date

[Mallesh23]

Hi Team,

Need VBA help to fill dates in Blank cells of Column A.

Expected Output shown in Column D. thanks in advance for your help.

Below is data with Expected Output.

Book1
	A	B	C	D	E
1	Date	Company		Date	Company
2	05/10/2020	ABC		05/10/2020	ABC
3		Agst Ref		05/10/2020	Agst Ref
4		XXX		05/10/2020	XXX
5	08/10/2020	XYZ		08/10/2020	XYZ
6		ABC		08/10/2020	ABC
7		XXX		08/10/2020	XXX
8	12/10/2020	PQR		12/10/2020	PQR
9		Agst Ref		12/10/2020	Agst Ref
10		XXX		12/10/2020	XXX
11		XXX		12/10/2020	XXX
12		XXX		12/10/2020	XXX
13		XXX		12/10/2020	XXX
14		XXX		12/10/2020	XXX
15		XXX		12/10/2020	XXX
16		XXX		12/10/2020	XXX
17	13/10/2020	RST		13/10/2020	RST
18		xxx		13/10/2020	xxx
19		xxx		13/10/2020	xxx
20		xxx		13/10/2020	xxx
21	23/10/2020	DEF		23/10/2020	DEF
22		Agst Ref		23/10/2020	Agst Ref
23		Agst Ref		23/10/2020	Agst Ref
24		Agst Ref		23/10/2020	Agst Ref
Sheet1

Thanks
mg

 

[MARK858]

With the data in your last post what does the formula

=CODE(A8)

return?

 

[Mallesh23]

Hi Mark,

Perfect ! Thanks for your help, Macro is giving correct Output now .

I extract this dump from Tally software.

Can you tell me what was the issue and how you sorted it.



Thanks
mg

 

[MARK858]

My last post shouldn't have sorted anything, the last post that should have altered anything was post 7 that you stated didn't change the formulas in E?

 

[Mallesh23]

Hi Mark,,

Few cells issue got sorted, there are few blanks cells Trim and Fill Blank macro,
didn't worked on those cells. Actually I pasted data from Different input files. I Don't remember.

One of data data it worked.

Below is the Break up of Input data.... and its output after formula and after macro.

also not getting values when applied formula, formula is automatic.calculation

=LEN(A2)

=LEN(A2)

=LEN(A2)

=LEN(A2)

Book8
	A	B	C	D	E	F
1	Date	Particular	Before trim FORMULA IS Blank Output	Before Running any Macro Formula Code	After Running Trim Macro	Final Output on Date Column
2	01/09/2020	xyz	FALSE	52	FALSE	01/09/2020
3		xyz	TRUE	#VALUE!	TRUE	01/09/2020
4		xyz	TRUE	#VALUE!	TRUE	01/09/2020
5		xyz	TRUE	#VALUE!	TRUE	01/09/2020
6		xyz	TRUE	#VALUE!	TRUE	01/09/2020
7		xyz	FALSE	#VALUE!	FALSE	Still issue here
8	01/09/2020	xyz	FALSE	52	FALSE	01/09/2020
9		xyz	TRUE	#VALUE!	TRUE	01/09/2020
10		xyz	FALSE	#VALUE!	FALSE	Still issue here
11	01/09/2020	xyz	FALSE	52	FALSE	01/09/2020
12		xyz	FALSE	#VALUE!	FALSE	Still issue here
13	01/09/2020	xyz	FALSE	52	FALSE	01/09/2020
14		xyz	TRUE	#VALUE!	TRUE	01/09/2020
15		xyz	FALSE	#VALUE!	FALSE	Still issue here
16		xyz	FALSE	#VALUE!	FALSE	Still issue here
17		xyz	TRUE	#VALUE!	TRUE	Still issue here
Sheet8

Cell Formulas
Range	Formula
D2:D17	D2	=CODE(A2)
E2:E17	E2	=ISBLANK(A2)

Thanks
mg

 

[MARK858]

If you click on A7 what (if anything) appears in the formula bar?

 

[Mallesh23]

Hi Mark,

In formula bar it is not showing anything,
with open eyes its blank cell
if clicked on that cell ... numberformat shows text
Applied len formula it shows 0 values.

then afterwords through loop, I did cleared Contents for 0 lengths cells,

Macro is giving correct output

For i = 2 To 485
    If Len(Cells(i, 1).Value) = 0 Then
        Cells(i, 1).ClearContents
    End If
Next i

    On Error Resume Next
    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With

Thanks
mg

 

[MARK858]

Happy that you got it sorted, you probably had one of the less common non-printing characters in there. That is one of the problems with downloading from the web you do tend to get these characters dragged in (most commonly character 160 but the code would have taken care of that one).

If you get the same issue again then there is some other code we can try.

 

[Mallesh23]

Hi Mark,

Can you share alternative code to handle it. I just got this project still not delivered.
When Macro delivered for testing user might identify it. if available please share else I will continue this existing.



Thanks
mg

 

[MARK858]

If you have some raw data upload the file to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and post the link it provides in the thread and I will test the code.

Make sure that you amend any sensitive data before uploading the file.

 

[Mallesh23]

Hi Mark,

Thanks for your help and patience, I am unable to upload it. But
Will use existing code which you have provided, which is working at the moment. if any issue arises will use fresh thread. ?



Thanks
mg