Number format

[Vtookup]

Hi all.
i have this formula COUNTIFS(Rec1!$D$4:$R$4,"<>",Rec1!D6:R6,"") to count blanks. now it stop working after i import data by paste value into that range. by paste value, empty space become "0". before i manually input data and leave space empty if zero. how should i amend this formula. thanks in advance for the help.

 

[MARK858]

#7 , D4 is a recipient. no formula just a blank space. which again will turn empty after data transfer to "Rec1" worksheet.
hope you get what i mean.
thanks

Wasn't what Post number 7 asked you to do, please re-read it.
Please can you also do the same with a cell with the 0 after the copy/paste values.

Off to work now so will catch up with the thread when I get back.

 

[Vtookup]

Sorry for the long break.
regarding #7 , applying code(D4) is giving me value error.
D4 is an empty cell that gets data then throw it to another sheet. with this confusions, is it okays if we change the formula?
to count how many blanks in a row base on how many days input
Row 4 is date and row 6 is data. Thanks

 

[MARK858]

to count how many blanks in a row base on how many days input

No because they aren't blanks they are 0's aren't they?

Can you please carry out the below from post number 11 so we can see if we can clean up the data.

Please can you also do the same with a cell with the 0 after the copy/paste values.

 

[Vtookup]

Hi.
applying code(D#) every number return as 0, if this what you mean?
on #13 , let's leave blank behind and count zeros? is it okay?
thanks

 

[MARK858]

Hi.
applying code(D#) every number return as 0, if this what you mean?
on #13 , let's leave blank behind and count zeros? is it okay?
thanks

You can't because if =CODE(D#) is returning 0 you have a nul in the cell not a zero. If it was a zero it would return 48.
Does the below make any change to the =CODE(D#) result (obviously changing D4 in the code to the cell you are referencing in the formula).

Sub rangenul()
Range("D4").Replace Chr(0), "", xlWhole
End Sub

If not does the below make any difference?

Sub rangenul()
Range("D4").Replace "", "", xlWhole
End Sub

We are looking for the =CODE formula to return a #VALUE error.