How-tos - Sharing Tips and Tutorials for Microsoft Office
Here we share the tips and tricks we know about Microsoft Office. It's sharing all those things that we learn and experience along the way of developing Office add-ins and using Microsoft Office.
Latest Articles
- How to launch Outlook from command line?
- How to send an email with subject line in red color in Outlook?
- How to import contacts from Google to Outlook?
- How to export or insert contacts details to Word in Outlook?
- How to assign a quick launch button to a template in Outlook?
- How to find intersection of two column lists in Excel?
- How to find exact word in a string in Excel?
- How to prevent users sending emails without subject entered in Outlook?
- How to create a macro code to achieve find and replace text in Excel?
- How to move items older than n days/weeks/months in Outlook?
I have to replace list of bank with proper name based on key words. For example, if somebody enters Halifax, it should be Halifax Bank of Scotland (HBOS) or if somebody enters LloydsTSB, it should be Lloyds.
Original value Replacing Value
LloydsTSB : Lloyds
Santander : Santander (Abbey)
Argos Card Services : Argos
Halifax : Halifax Bank of Scotland (HBOS)
The down formula works in many cases. But if there is two Halifax bank on my list, I will get Halifax Bank of Scotland (HBOS) (HBOS)
. So, first time it finds Halifax, replace with correct one. Again it finds Halifax and replace. How can I correct this problem ?
Sub FindReplaceNew()
Dim xTitledId As String
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitledId ...
Sub MergeDoc(WdApp As Word.Application, xFileName As String, Doc As Word.Document)
Dim xNewDoc2 As Word.Document
Dim xSec As Section
Set xNewDoc2 = WdApp.Documents.Open(FileName:=xFileName, Visible:=False)
Set xSec = Doc.Sections.Add
xNewDoc2.Content.Copy
xSec.PageSetup = xNewDoc2.PageSetup
xSec.Range.PasteAndFormat wdFormatOriginalFormatting
xNewDoc2.Close
End Sub
Peter
If you want to set another color, you just need to fill your desired color to the cells, and then apply the above code in this article.
Please try it.
The above code works well when renaming the sheet name and the items in the drop down list will be updated automatically.
You can try it.
For 2nd occurence -
=FIND("c",A1,FIND("c",A1)+1)
For 3rd occurence -
=FIND("c",A1,FIND("c",A1,FIND("c",A1)+1)+1) and so on...
Thanks!