replace only 8 cells of a row from next raw if one cell has value of45g
 

I have this file.

12,br2,3er,6ro,WarehouseA,CottonA,Alpha5u4,Brav56o ,34g,54inch,$99,45
14,cd3,4er,7ro,WarehouseB,CottonGr,Charlie4,Labmer t,45g,55inch,$98,43
15,gd4,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,56inch,$97,42
25,df3,4er,107o,Warehouseg,CottonGb,Mike14,Labmert ,65g,57inch,$96,42
45,gf4,5er,11ro,Warehouse1,CottonGc,Charlie,Labmer t,75g,58inch,$95,41
35,df3,4er,1ro,Warehouse3,CottonGr,Peter345,Labmer t,65g,59inch,$94,40
95,gf4,5er,2ro,WarehouseF,CottonGn,Charli4e,Labmer t,75g,50inch,$93,39
13,ad3,4qr,5ro,Warehousex,Cottonzr,Charlie9,Labmer t,45g,55inch,$98,43
11,ad3,4fr,4ro,Warehousey,Cottonxr,Charlie0,Labmer t,45g,55inch,$98,43
10,vf4,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,50inch,$93,39
16,vf3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,50inch,$93,39

I want to change it to following (based on number 45g in col I)

12,br2,3er,6ro,WarehouseA,CottonA,Alpha5u4,Brav56o ,34g,54inch,$99,45
14,cd3,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,55inch,
$98,43
15,gd4,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,56inch,$97,42
25,df3,4er,107o,Warehouseg,CottonGb,Mike14,Labmert ,65g,57inch,$96,42
45,gf4,5er,11ro,Warehouse1,CottonGc,Charlie,Labmer t,75g,58inch,$95,41
35,df3,4er,1ro,Warehouse3,CottonGr,Peter345,Labmer t,65g,59inch,$94,40
95,gf4,5er,2ro,WarehouseF,CottonGn,Charli4e,Labmer t,75g,50inch,$93,39
13,ad3,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,55inch,$98,43
11,ad3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,55inch,$98,43
10,vf4,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,50inch,$93,39
16,vf3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,50inch,$93,39


Here is the explanation.
I want to change all 45g in col I. At the same time, it should also
change few same raw values if there is any 45g in this raw

or

45g was in I2, then c2,d2,e2,f2,g2,h2,i3,r2 copied from
c3,d3,e3,f3,g3,h3,i3,r3
but if i3 is also 45g then c2,d2,e2,f2,g2,h2,i3,r2 copied from
c4,d4,e4,f4,g4,h4,i4,r4

I have more than 100 rows, it should change 45g and related cells to
preceedings.

replace only 8 cells of a row from next raw if one cell has valueof 45g
 

On Feb 25, 10:01*pm, myshak wrote:
I have this file.

12,br2,3er,6ro,WarehouseA,CottonA,Alpha5u4,Brav56o ,34g,54inch,$99,45
14,cd3,4er,7ro,WarehouseB,CottonGr,Charlie4,Labmer t,45g,55inch,$98,43
15,gd4,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,56inch,$97,42
25,df3,4er,107o,Warehouseg,CottonGb,Mike14,Labmert ,65g,57inch,$96,42
45,gf4,5er,11ro,Warehouse1,CottonGc,Charlie,Labmer t,75g,58inch,$95,41
35,df3,4er,1ro,Warehouse3,CottonGr,Peter345,Labmer t,65g,59inch,$94,40
95,gf4,5er,2ro,WarehouseF,CottonGn,Charli4e,Labmer t,75g,50inch,$93,39
13,ad3,4qr,5ro,Warehousex,Cottonzr,Charlie9,Labmer t,45g,55inch,$98,43
11,ad3,4fr,4ro,Warehousey,Cottonxr,Charlie0,Labmer t,45g,55inch,$98,43
10,vf4,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,50inch,$93,39
16,vf3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,50inch,$93,39

I want to change it to following (based on number 45g in col I)

12,br2,3er,6ro,WarehouseA,CottonA,Alpha5u4,Brav56o ,34g,54inch,$99,45
14,cd3,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,55inch,
$98,43
15,gd4,5er,8ro,WarehouseC,CottonGe,Charlie2,Labmer t,55g,56inch,$97,42
25,df3,4er,107o,Warehouseg,CottonGb,Mike14,Labmert ,65g,57inch,$96,42
45,gf4,5er,11ro,Warehouse1,CottonGc,Charlie,Labmer t,75g,58inch,$95,41
35,df3,4er,1ro,Warehouse3,CottonGr,Peter345,Labmer t,65g,59inch,$94,40
95,gf4,5er,2ro,WarehouseF,CottonGn,Charli4e,Labmer t,75g,50inch,$93,39
13,ad3,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,55inch,$98,43
11,ad3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,55inch,$98,43
10,vf4,5er,0ro,Warehousez,Cottonyn,Charli48,Labmer t,75g,50inch,$93,39
16,vf3,5gr,0go,Warehouseg,Cottongn,Charli18,Labmer t,85g,50inch,$93,39

Here is the explanation.
I want to change all 45g in col I. At the same time, it should also
change few same raw values if there is any 45g in this raw

or

45g was in I2, then c2,d2,e2,f2,g2,h2,i3,r2 copied from
c3,d3,e3,f3,g3,h3,i3,r3
but if i3 is also 45g then c2,d2,e2,f2,g2,h2,i3,r2 copied from
c4,d4,e4,f4,g4,h4,i4,r4

I have more than 100 rows, it should change 45g and related cells to
preceedings.

Try this, you create a macro or a button :

Range("I2").Select
While Not IsEmpty(ActiveCell)
If ActiveCell.Value = "45g" And ActiveCell.Offset(1, 0) < "45g" Then
ActiveCell.Value = ActiveCell.Offset(1, 0).Value
ElseIf ActiveCell.Value = "45g" And ActiveCell.Offset(1, 0) = "45g"
Then
ActiveCell.Value = ActiveCell.Offset(3, 0).Value
End If
ActiveCell.Offset(1, 0).Select
Wend