Change description

[Panoos64]

Hi all, i would like to write a vba command so to run through col. "A" and where find the account's description "Telephones & Postages" should convert it to "Telephones / Postages". In 1st schedule is an extracvt of original data and in 2nd is the expected result. Thanks in advance.

1
2	Marketing expenses
3	Subscriptions & Donations
4	Telephones & Postages
5	Sundry expenses
6	Management Fees

<colgroup><col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:10422;width:214pt" width="285"> </colgroup><tbody>
[TD="width: 41"][/TD]
[TD="class: xl66, width: 285"]A[/TD]

[TD="class: xl65"]Description[/TD]

</tbody>

1
2	Marketing expenses
3	Subscriptions & Donations
4	Telephones / Postages
5	Sundry expenses
6	Management Fees

<colgroup><col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:10422;width:214pt" width="285"> </colgroup><tbody>
[TD="width: 41"][/TD]
[TD="class: xl66, width: 285"]A[/TD]

[TD="class: xl65"]Description[/TD]

</tbody>

 

[Joe4]

Looping in VBA is pretty inefficient and should be avoided when possible.
You can do what you want simply by using the built-in "Find/Replace" functionality.
If you want the VBA code for that, simply turn on the Macro Recorder while performing the Find/Replace steps manually.

 

[Panoos64]

Ok Joe4, I am sorry that i done a mistake again. I have in col. "A" twice the same description and i wanted to make them unique. This is the reason that the one of those, i should change it to "Telephones / Postages". Apologies once again and many thanks for your interesting to resolve my projects.
Have a nice day

 

[Joe4]

So, are you certain that you will only ever have EXACTLY two instances of this, and you only want to change the second one?
So there will never be three or more records like this?

 

[Panoos64]

Hi Joe4. Is duplicate description (just 2 same descriptions) but i want to keep both of them as unique, changing the first one (not second). Below i extended my example and i present it with more details. The first are the original data and in second is the expected result. Thanks once again for your support. Have a nice day

1
2
3	Marketing expenses
4	Subscriptions & Donations
5	Telephones & Postages
6	Sundry expenses
7	Management Fees
8
9
10	Printing & Stationery
11	Telephones & Postages
12	Electricity

<tbody>
[TD="class: xl64, width: 285"]A[/TD]

[TD="class: xl63"] Description
[/TD]

[TD="class: xl65"] Selling & Distribution Expenses
[/TD]

[TD="class: xl65"] Administration Expenses
[/TD]

</tbody>

1
2
3	Marketing expenses
4	Subscriptions & Donations
5	Telephones / Postages
6	Sundry expenses
7	Management Fees
8
9
10	Printing & Stationery
11	Telephones & Postages
12	Electricity

<colgroup><col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:10422;width:214pt" width="285"> </colgroup><tbody>
[TD="width: 41"][/TD]
[TD="class: xl66, width: 285"]A
[/TD]

[TD="class: xl65"] Description
[/TD]

[TD="class: xl67"] Selling & Distribution Expenses
[/TD]

[TD="class: xl67"] Administration Expenses
[/TD]

</tbody>

 

[Joe4]

So, you just want to replace the first one you find?
Is that correct?

 

[Rick Rothstein]

Hi Joe4. Is duplicate description (just 2 same descriptions) but i want to keep both of them as unique, changing the first one (not second).

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ChangeFirstAmpersandToSlashForTelephonesAndPostages()
  On Error GoTo NoTelephonesPostage
  With Columns("A:A").Find("Telephones & Postages", Range("A1"), xlValues, xlWhole, , xlNext, False, False).Cells
    .Value = Replace(.Value, "&", "/")
  End With
NoTelephonesPostage:
End Sub[/td]
[/tr]
[/table]

 

[Panoos64]

Thank you so much Rick. It works perfect and is according to what i was required. Thanks also for all your support.
Hv a nice day

 

[Peter_SSs]

.. or even more directly

Sub ChangeFirstOne()
  On Error Resume Next
  Columns("A").Find(What:="Telephones & Postages", LookAt:=xlWhole, SearchFormat:=False).Value = "Telephones / Postages"
End Sub

 

[Panoos64]

Many thanks Peter. The code works perfect too and it appears the expected result. Also thanks for your time spent for my project.
Have a nice day