How to Replace blank cell with specific value

[manoj_arsul]

I want program for below condition

1. filter to column "D" for blank .
2. Replace all blank cell in column "D" with Not OK.
3. Clear Filter .
4. filter to column "D" for Not OK.
5. filter to column "A" for Pending.
6. Replace all these pending As Pending - Not OK

Example is
Row Data
<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

Pending	Pune	India	Ok
Pending	Mumbai	India	Ok
Pending	London	England
Pending	Pune	India
Pending	Mumbai	India	Ok
Pending	Mumbai	India
Pending	London	England
Pending	Pune	India
Waiting	London	England	Ok
Waiting	Pune	India	Ok
Waiting	Mumbai	India
Waiting	London	England
Waiting	Pune	India	Ok
Working	Mumbai	India	Ok
Working	London	England
Working	Pune	India	Ok
Working	Mumbai	India	Ok
Closed	London	England
Closed	London	England
Closed	Pune	India
Closed	Mumbai	India
Closed	London	England	Ok
Closed	Pune	India	Ok
Closed	Mumbai	India
Closed	London	England

<tbody>
[TD="bgcolor: #FFFF00"] Status* [/TD]
[TD="bgcolor: #FFFF00"] City [/TD]
[TD="bgcolor: #FFFF00"] Country [/TD]
[TD="bgcolor: #FFFF00"] Test [/TD]

</tbody>

Out Put :

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

Pending	Pune	India	Ok
Pending	Mumbai	India	Ok
Pending - Not OK	London	England	Not OK
Pending - Not OK	Pune	India	Not OK
Pending	Mumbai	India	Ok
Pending - Not OK	Mumbai	India	Not OK
Pending - Not OK	London	England	Not OK
Pending - Not OK	Pune	India	Not OK
Waiting	London	England	Ok
Waiting	Pune	India	Ok
Waiting	Mumbai	India	Not OK
Waiting	London	England	Not OK
Waiting	Pune	India	Ok
Working	Mumbai	India	Ok
Working	London	England	Not OK
Working	Pune	India	Ok
Working	Mumbai	India	Ok
Closed	London	England	Not OK
Closed	London	England	Not OK
Closed	Pune	India	Not OK
Closed	Mumbai	India	Not OK
Closed	London	England	Ok
Closed	Pune	India	Ok
Closed	Mumbai	India	Not OK
Closed	London	England	Not OK

<tbody>
[TD="bgcolor: #FFFF00"] Status* [/TD]
[TD="bgcolor: #FFFF00"] City [/TD]
[TD="bgcolor: #FFFF00"] Country [/TD]
[TD="bgcolor: #FFFF00"] Test [/TD]

</tbody>

 

[Fluff]

How about

Sub NotOk()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 3).SpecialCells(xlBlanks).Value = "Not OK"
      .Value = Evaluate(Replace(Replace("if((@=""Pending"")*(@d=""Not OK""),""Pending-Not OK"",@)", "@d", .Offset(, 3).Address), "@", .Address))
   End With
End Sub

 

[manoj_arsul]

How about

Sub NotOk()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 3).SpecialCells(xlBlanks).Value = "Not OK"
      .Value = Evaluate(Replace(Replace("if((@=""Pending"")*(@d=""Not OK""),""Pending-Not OK"",@)", "@d", .Offset(, 3).Address), "@", .Address))
   End With
End Sub

Thank u @Fluff

 

[Fluff]

You're welcome

 

[manoj_arsul]

I want one more program for the below condition.(Last 5 Days Count City wise .)


1) Filter to Column "A" Status For Working .
2) Filter to Column "B" City For Pune.
3) Get Till date (Today) Count of column "A" in the cell of table ( Today 19th)
4) Filter Today()-1 & Get date Count of column "A" in the cell of table ( 18th July)
5) Filter Today()-2 & Get date Count of column "A" in the cell of table ( 17th July)
6) Filter Today()-3 & Get date Count of column "A" in the cell of table ( 16th July)
7) Filter Today()-4 & Get date Count of column "A" in the cell of table ( 15th July)

Do the Same condition with other Pending , Pending - Not OK , Waiting

Source Data: -
<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

Pending	Pune	India	Ok	19-Jul-18
Pending	Mumbai	India	Ok	17-Jun-18
Pending - Not OK	London	England	Not OK	16-May-18
Pending - Not OK	Pune	India	Not OK	15-Jul-18
Pending	Mumbai	India	Ok	18-Jul-18
Pending - Not OK	Mumbai	India	Not OK	19-Jul-18
Pending - Not OK	London	England	Not OK	17-Jul-18
Pending - Not OK	Pune	India	Not OK	16-Jul-18
Waiting	London	England	Ok	15-Feb-18
Waiting	Pune	India	Ok	18-Jul-18
Waiting	Mumbai	India	Not OK	19-Jul-18
Waiting	London	England	Not OK	17-Jul-18
Waiting	Pune	India	Ok	16-Jun-18
Working	Mumbai	India	Ok	5-Mar-18
Working	London	England	Not OK	18-Jul-18
Working	Pune	India	Ok	19-Jul-18
Working	Pune	India	Ok	17-Mar-18
Closed	London	England	Not OK	16-Jul-18
Closed	London	England	Not OK	15-Jul-18
Closed	Pune	India	Not OK	18-Jul-18
Closed	Mumbai	India	Not OK	19-Jul-18
Closed	London	England	Ok	17-Jul-18
Closed	Pune	India	Ok	16-Jul-18
Closed	Mumbai	India	Not OK	15-Jul-18
Closed	London	England	Not OK	18-Jul-18

<tbody>
[TD="bgcolor: #FFFF00"] Status* [/TD]
[TD="bgcolor: #FFFF00"] City [/TD]
[TD="bgcolor: #FFFF00"] Country [/TD]
[TD="bgcolor: #FFFF00"] Test [/TD]
[TD="bgcolor: #FFFF00"] Date [/TD]

</tbody>

Out Put :

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small } </style>

0	0	1	0
0	0	2	1
1	0	2	1
1	0	2	2
2	1	2	2

<tbody>
[TD="bgcolor: #FFFF00"] Pune [/TD]
[TD="bgcolor: #DAE3F3"] Working Count [/TD]
[TD="bgcolor: #DAE3F3"] Pending Count [/TD]
[TD="bgcolor: #DAE3F3"] Pending - Not OK [/TD]
[TD="bgcolor: #DAE3F3"] Waiting Count [/TD]

[TD="bgcolor: #DAE3F3"] 15-Jul-18 [/TD]

[TD="bgcolor: #DAE3F3"] 16-Jul-18 [/TD]

[TD="bgcolor: #DAE3F3"] 17-Jul-18 [/TD]

[TD="bgcolor: #DAE3F3"] 18-Jul-18 [/TD]

[TD="bgcolor: #DAE3F3"] 19-Jul-18 [/TD]

</tbody>

 

[Fluff]

As this is a completely different question, please start a new thread.
But before you do, I do not know where you are copy/pasting your posts from, but please do not do it. It normally occurs when you are pasting a table. if you follow the link in my signature you will tools to help with that.
I have had to clean a lot of them up as they are so small as to be virtually unreadable.
You can use the test board to check that things are working properly before starting a new thread

 

[manoj_arsul]

ok Fluff I am going to raise new Thread for the same .

And this is not copy paste Question . Only I am going to same source data ( only for reference ).

Thank you Again !!!