help needed to compile a macro (replace only 8 cells of a row fromnext raw if one cell has value of

[myshak]

I have this file.

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

 

[Earl Kiosterud]

myshak,

If I take your description literally, I think this is what you want.

Sub Convertt()
Dim i As Long
Dim arg
i = 2 ' starting row
arg = Cells(i, "I")
Do
If arg = "45g" Then
Cells(i, "C") = Cells(i + 1, "C")
Cells(i, "D") = Cells(1 + 1, "D")
Cells(i, "E") = Cells(i + 1, "E")
Cells(i, "F") = Cells(i + 1, "F")
Cells(i, "G") = Cells(i + 1, "G")
Cells(i, "H") = Cells(i + 1, "H")
Cells(i, "I") = Cells(i + 1, "I")
Cells(i, "R") = Cells(i + 1, "R")
End If
i = i + 1
arg = Cells(i, "I")
Loop While arg <> "" ' stop when empty cell found
End Sub

But I'm confused by this statement:

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

Maybe you meant c3 is copied from c4.

This will stop at the first empty cell in column I.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------