Find and replace.

vmjan02 · Oct 27, 2023

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

No Change

101-00_

Active

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

No Change

209-00_

Active

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

No Change

403-00_

Active

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

No Change

108-00_

Active

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_

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_

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_

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_

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_

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_

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

No Change

343-00_

Active

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

No Change

707-00_

Active

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

No Change

428-00_

Active

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

No Change

172-00_

Active

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

No Change

209-00_

Active

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

No Change

213-00_

Active

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

No Change

206-00_

Active

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

No Change

296-00_

Active

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 · Oct 27, 2023

NdNoviceHlp said:
"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

VBA Code:

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 · Oct 27, 2023

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 · Oct 27, 2023

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

Find and replace.

vmjan02

Well-known Member

vmjan02

Well-known Member

Excel Facts

vmjan02

Well-known Member

NdNoviceHlp

Well-known Member

Similar threads

Forum statistics

Share this page

Find and replace.

vmjan02

Well-known Member

vmjan02

Well-known Member

Excel Facts

vmjan02

Well-known Member

NdNoviceHlp

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock