replace text cell

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Replace Text in a cell

Replace Text in a cell

resolvedResolved · Low Priority · Version 2010

Nicole has attended:
Access Advanced course
Access VBA course

Replace Text in a cell

Hi
I'm trying to replace the titles of 3 columns in Excel
So far I've managed to change one but unsure how to replace the values of the rest with different title in columns B1 & C1.

Sub change_title()

With Range("A1").Value = Replace(.Value, "Constituent Unique Ref No", "ConsID")

End With

End Sub

Thanks
Nicole

RE: Replace Text in a cell

Hi Nicole

Thanks for getting in touch. This is much easier than you might think. You can modify the contents of any cell with the format

Range(cell reference) = new value

So your code would look something like this:

Sub change_title()

Range("A1") = "Constituent Unique Ref No"
Range("B1") = "ConsID"
Range("C1") = "My label"

End Sub

Just a footnote on that, if you want use a number leave off the quotes, else Excel will treat it as text:

Range("A1") = 150

If you're looking to do a "find and replace" style action, record that code and modify accordingly.

I hope that helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Replace Text in a cell

Thanks! Worked like a charm :)

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Change The Default Font in Access 2003

You can change the default font in Access 2003 so that whenever you create a new database your preferred font is automatically set.

To change the default font:

1.From the menu bar select Tools.
2.Click Options.
3.Go to the Datasheet tab:
4.In the Default font section select your preferred font from the list of fonts e.g. Arial.
5.Select your preferred size from the size menu e.g. 12.
6.Click Apply.

Click on the Tables/queries tab:
1.In the Query Design font section select your preferred font from the list of fonts e.g. Arial.
2.Select your preferred size from the size menu e.g. 12.
3.Click Apply.
4.Click OK.

The default font for Access has now been changed.

View all Access hints and tips


Server loaded in 0.11 secs.