Find and replace.

[vmjan02]

I have this data and need to find and replace , this will be a life saver as now this takes 6 to 7 hrs for me to complete the task.
is there a way in vab that it will serach a value and replace it

only for column e were cell value is 1.
eg:- row no 13
e13 =1 then
it will find b13 from N13 to last non blank column and if found will replace it with K13.
b13 = 68248592160750700 seach this value in N19 to ZP19(last non blank cell in row 19)
b13 found in cell AA13 so now AA13 value should be replace by K13
and next

can a vba works it out this way.

sample data below.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2
3
4
5
6
7
8	PERCENTILE 99%	Max	Diff	d/Per	Above 50%	Actual Column	Pervious	After	Per Val	Next Val	Avg	Remarks		Short name	NE_STATE	OLT	Short name	01/01/23, 00:00	02/01/23, 00:00	03/01/23, 00:00	04/01/23, 00:00	05/01/23, 00:00	06/01/23, 00:00	07/01/23, 00:00	08/01/23, 00:00	09/01/23, 00:00	10/01/23, 00:00	11/01/23, 00:00
9	3.71187E+12	3.96318E+12	2.51308E+11	6.77%	0				#N/A	#N/A	#N/A	No Change		101-00_	Active	101-00_	101-00_	2.57046E+12	3.03085E+12	3.10764E+12	2.89118E+12	2.84709E+12	3.24747E+12	3.06845E+12	2.90603E+12	3.30355E+12	2.83128E+12	2.83665E+12
10	2.62976E+13	2.65269E+13	2.29263E+11	0.87%	0				#N/A	#N/A	#N/A	No Change		209-00_	Active	209-00_	209-00_	2.10913E+13	2.00048E+13	2.0549E+13	1.92544E+13	1.90795E+13	1.81372E+13	2.02025E+13	1.80057E+13	1.97399E+13	1.78594E+13	1.9489E+13
11	2.96603E+13	3.05316E+13	8.71317E+11	2.94%	0				#N/A	#N/A	#N/A	No Change		403-00_	Active	403-00_	403-00_	2.21808E+13	2.18034E+13	2.34455E+13	2.32703E+13	2.13711E+13	2.19559E+13	2.34576E+13	2.32089E+13	2.20241E+13	2.32954E+13	2.18373E+13
12	4.63924E+13	4.96095E+13	3.21714E+12	6.93%	0				#N/A	#N/A	#N/A	No Change		108-00_	Active	108-00_	108-00_	3.22984E+13	3.25831E+13	3.73777E+13	3.61234E+13	3.51334E+13	3.41433E+13	3.50704E+13	3.4919E+13	3.51274E+13	3.65808E+13	3.40239E+13
13	5.51755E+13	6.82486E+16	6.81934E+16	123593.60%	1	31/07/2023	30/07/2023	01/08/2023	4.5412E+13	4.46555E+13	4.50338E+13	Change		206-00_	Active	206-00_	206-00_	4.84895E+13	4.55798E+13	4.74961E+13	4.2477E+13	4.3761E+13	4.64112E+13	4.67637E+13	4.1764E+13	4.65392E+13	6.82486E+16	4.44107E+13
14	5.25261E+13	7.21047E+16	7.20522E+16	137174.12%	1	07/07/2023	06/07/2023	08/07/2023	4.42827E+13	4.37339E+13	4.40083E+13	Change		219-00_	Active	219-00_	219-00_	2.13122E+13	2.08266E+13	2.08223E+13	7.21047E+16	1.92175E+13	1.77083E+13	1.92632E+13	1.85455E+13	2.03177E+13	1.92304E+13	1.78454E+13
15	3.58949E+13	7.2091E+16	7.20551E+16	200738.91%	1	14/08/2023	13/08/2023	15/08/2023	3.20029E+13	2.87905E+13	3.03967E+13	Change		215-00_	Active	215-00_	215-00_	2.57899E+13	3.1169E+13	3.01287E+13	2.98757E+13	2.74744E+13	7.2091E+16	3.03128E+13	2.79463E+13	3.08E+13	2.69527E+13	2.69774E+13
16	2.80592E+13	7.20753E+16	7.20473E+16	256768.52%	1	20/04/2023	19/04/2023	21/04/2023	1.99106E+13	1.68039E+13	1.83573E+13	Change		405-00_	Active	405-00_	405-00_	2.07905E+13	7.20753E+16	2.03198E+13	2.13614E+13	2.01161E+13	2.14677E+13	2.21228E+13	1.8983E+13	1.96145E+13	1.76097E+13	2.03697E+13
17	2.17752E+13	7.01804E+16	7.01587E+16	322195.38%	1	01/05/2023	30/04/2023	02/05/2023	1.87419E+13	2.95061E+14	1.56902E+14	Change		212-00_	Active	212-00_	212-00_	1.86716E+13	1.8463E+13	1.92229E+13	1.78519E+13	1.77784E+13	1.68654E+13	1.8098E+13	7.01804E+16	1.94947E+13	1.82121E+13	1.75575E+13
18	3.67045E+12	7.2061E+16	7.20573E+16	1963174.55%	1	09/02/2023	08/02/2023	10/02/2023	3.4556E+12	1.76086E+12	2.60823E+12	Change		201-00_	Active	201-00_	201-00_	2.47656E+12	2.61643E+12	2.62939E+12	2.79215E+12	7.2061E+16	1.64502E+12	2.71797E+12	3.20158E+12	3.39275E+12	3.06009E+12	7.2061E+16
19	1.52361E+14	1.57775E+14	5.41368E+12	3.55%	0				#N/A	#N/A	#N/A	No Change		343-00_	Active	343-00_	343-00_	1.01412E+14	9.98625E+13	1.05566E+14	1.12905E+14	1.19261E+14	1.02882E+14	1.15174E+14	1.10825E+14	1.05661E+14	1.33623E+14	1.04548E+14
20	1.12484E+14	1.20721E+14	8.23793E+12	7.32%	0				#N/A	#N/A	#N/A	No Change		707-00_	Active	707-00_	707-00_	6.44394E+13	7.71739E+13	7.35691E+13	7.43816E+13	7.28574E+13	8.70437E+13	8.70779E+13	7.84663E+13	7.92791E+13	7.58194E+13	7.70722E+13
21	1.05509E+14	1.11848E+14	6.33914E+12	6.01%	0				#N/A	#N/A	#N/A	No Change		428-00_	Active	428-00_	428-00_	6.35586E+13	8.2578E+13	7.46724E+13	7.89953E+13	7.56625E+13	7.44405E+13	7.86401E+13	7.49082E+13	7.5356E+13	7.37645E+13	7.1967E+13
22	1.27801E+14	1.30688E+14	2.88699E+12	2.26%	0				#N/A	#N/A	#N/A	No Change		172-00_	Active	172-00_	172-00_	5.9024E+13	5.99894E+13	6.59582E+13	6.64529E+13	6.2487E+13	5.70194E+13	6.44993E+13	6.05956E+13	6.09441E+13	6.24502E+13	5.94318E+13
23	8.58339E+13	8.89753E+13	3.14144E+12	3.66%	0				#N/A	#N/A	#N/A	No Change		209-00_	Active	209-00_	209-00_	7.70253E+13	7.19025E+13	7.29452E+13	6.79377E+13	6.63824E+13	6.74652E+13	7.06308E+13	6.12602E+13	6.68593E+13	5.99955E+13	6.75234E+13
24	8.34736E+13	8.4398E+13	9.24468E+11	1.11%	0				#N/A	#N/A	#N/A	No Change		213-00_	Active	213-00_	213-00_	7.13665E+13	6.66799E+13	7.29732E+13	6.35129E+13	6.35211E+13	6.94525E+13	6.82313E+13	6.0591E+13	6.70254E+13	5.83124E+13	6.46105E+13
25	8.28309E+13	8.97382E+13	6.90728E+12	8.34%	0				#N/A	#N/A	#N/A	No Change		206-00_	Active	206-00_	206-00_	7.18268E+13	6.84E+13	6.7977E+13	6.63546E+13	6.53581E+13	5.88576E+13	6.73027E+13	6.5402E+13	6.99695E+13	6.24803E+13	5.90705E+13
26	8.00764E+13	8.57892E+13	5.71275E+12	7.13%	0				#N/A	#N/A	#N/A	No Change		296-00_	Active	296-00_	296-00_	6.75498E+13	6.28066E+13	6.62272E+13	6.07234E+13	6.47338E+13	6.28066E+13	6.59657E+13	6.08039E+13	6.53773E+13	6.01964E+13	6.33779E+13
27
28
Sheet1

 

[vmjan02]

"no it is not deplacint it" .... I don't understand?

Is "B" the find value or replacement Value. Is "K" the find value or replacement value. Is the pic in #1 the before or after replacement? Dave

it is working now the only problem in don't know is

If .Range("E" & Cnt) = 1 Then

is giving me "Type Mismatch" error but if i dugbug it Fn+8 step by step then no error

 

[vmjan02]

you are a genious man thnaks a ton it has saved a lot of time

it is working perfect, thanks a ton thank you very much

 

[NdNoviceHlp]

I'm glad you got it sorted out. You are welcome and thanks for posting your outcome. Dave