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

 

[NdNoviceHlp]

Hi vmjan02. "this takes 6 to 7 hrs" ... yuck!. It's snowing outside and it seems I have some time available. Can you post a sample wb? Dave

 

[vmjan02]

@ NdNovicehlp
Have already posted the sample in my 1st post. in xl2bb, you can copy it, for working.

 

[NdNoviceHlp]

Well isn't that handy. Is there actually an AC column? It copy/pasted but is not shown. Dave

 

[vmjan02]

Well isn't that handy. Is there actually an AC column? It copy/pasted but is not shown. Dave

yes data my vary this sampe data till AB, but columns may also go till XC as well, everday the colum and row data changes.

acutal data i have currently i am doing it is till KE and rows are 8000

so col and row will vary.
I hope i am clear now.

 

[NdNoviceHlp]

The copy/paste thing doesn't want to work right? Anyways, if an "E" row = 1 then: if the B row value equals the same value as the N to last column row then: replace it with the K row value.
This seems like it should work. Please save a copy of your wb before testing. The code identifies the changed cells with red interior. Change the sheet name to suit. HTH. Dave

Sub test()
Dim LastCol As Integer, LastRow As Integer, Cnt As Integer, Cnt2 As Integer
Dim FindVal As Double, RepVal As Double, Rng As Range, r As Range
With Sheets("sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
For Cnt = 9 To LastRow
If .Range("E" & Cnt) = 1 Then
For Cnt2 = Cnt To LastRow
    If .Range("E" & Cnt2) = 0 Then
    Cnt = Cnt2
    Exit For
    Else
    'it will find b13 from N13 to last non blank column and if found will replace it with K13.
    FindVal = .Range("B" & Cnt2)
    RepVal = .Range("K" & Cnt2)
    Set Rng = .Range(.Cells(Cnt2, "N"), .Cells(Cnt2, LastCol))
        For Each r In Rng
        If r.Value = FindVal Then
        r.Value = RepVal
        r.Interior.Color = vbRed
        End If
        Next r
    End If
Next Cnt2
End If '.Range("E" & Cnt) = 1
Next Cnt
End With
End Sub

 

[vmjan02]

The copy/paste thing doesn't want to work right? Anyways, if an "E" row = 1 then: if the B row value equals the same value as the N to last column row then: replace it with the K row value.
This seems like it should work. Please save a copy of your wb before testing. The code identifies the changed cells with red interior. Change the sheet name to suit. HTH. Dave

Sub test()
Dim LastCol As Integer, LastRow As Integer, Cnt As Integer, Cnt2 As Integer
Dim FindVal As Double, RepVal As Double, Rng As Range, r As Range
With Sheets("sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
For Cnt = 9 To LastRow
If .Range("E" & Cnt) = 1 Then
For Cnt2 = Cnt To LastRow
    If .Range("E" & Cnt2) = 0 Then
    Cnt = Cnt2
    Exit For
    Else
    'it will find b13 from N13 to last non blank column and if found will replace it with K13.
    FindVal = .Range("B" & Cnt2)
    RepVal = .Range("K" & Cnt2)
    Set Rng = .Range(.Cells(Cnt2, "N"), .Cells(Cnt2, LastCol))
        For Each r In Rng
        If r.Value = FindVal Then
        r.Value = RepVal
        r.Interior.Color = vbRed
        End If
        Next r
    End If
Next Cnt2
End If '.Range("E" & Cnt) = 1
Next Cnt
End With
End Sub

I have dug it

in Findval it is searching as 6.82.....+16 were as the actual value to search is 68248592160750700 and replace it wil 45033773320234.5

so its not replacing it, any way out

 FindVal = .Range("B" & Cnt2)   '68248592160750700

 

[NdNoviceHlp]

Not sure I understand. It's not replacing it with the correct value or it's replacing it but not with the extended value? Dave

 

[vmjan02]

Not sure I understand. It's not replacing it with the correct value or it's replacing it but not with the extended value? Dave

no it is not replacing it,

I tried it wil

Dim FindVal As Double insted Dim FindVal As LongLong but no luck

 

[NdNoviceHlp]

"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