Change Column Text on Condition

[PMARI]

Hi Friends,

I need a Macro to change Text in column " F" with respect to the No. in Column "C"

<table style="width: 453px; height: 288px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <col style="width: 130pt;" width="173"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">Col "C" Order No.
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">Col "F' Type</td> <td style="width: 130pt;" width="173">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">300000</td> <td>
</td> <td>Dom</td> <td class="xl22">Change as REPLACEMENT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">321000</td> <td>
</td> <td>Dom</td> <td class="xl22">Change as REPLACEMENT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1200000</td> <td>
</td> <td>Dom</td> <td>Dom ( No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">102514</td> <td>
</td> <td>Dom</td> <td>Dom ( No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2365900</td> <td>
</td> <td>Exp</td> <td>Exp (No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3000120</td> <td>
</td> <td>Dom</td> <td class="xl22">Change as REPLACEMENT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1254800</td> <td>
</td> <td>Dom</td> <td>Dom ( No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1425600</td> <td>
</td> <td>Dom</td> <td>Dom ( No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">250001</td> <td>
</td> <td>Exp</td> <td>Exp (No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1000254</td> <td>
</td> <td>Dom</td> <td>Dom ( No Change)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3002154</td> <td>
</td> <td>Dom</td> <td class="xl22">Change as REPLACEMENT</td> </tr> </tbody></table>
When an order no.in "C" Starts with "3" Then text in "F" to be changed as "Replacement", otherwise leave the Text as it is.

Any help highly appreciated.

Regards,PM.

 

[circledchicken]

Hi PMARI,

Do you need to use a macro or would a formula suffice? Perhaps try the following in column G and drag down:

=IF(LEFT(C1,1)="3","REPLACEMENT",F1)

 

[Peter_SSs]

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PMARI()<br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Range("C" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("C1").Resize(lr, 4)<br>        a = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> Left(a(i, 1), 1) = "3" <SPAN style="color:#00007F">Then</SPAN><br>                a(i, 4) = "REPLACEMENT"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[PMARI]

Dear Peter Sss

Thanks a Lot. Thanks for the code.

 

[PMARI]

Hi PMARI,

Do you need to use a macro or would a formula suffice? Perhaps try the following in column G and drag down:

=IF(LEFT(C1,1)="3","REPLACEMENT",F1)

Hi, Thanks for reply.