Looking for quoicker method with "cell.Formula = Replace(cell.Formula"

J

jamada

Active Member
Joined
Mar 23, 2006
Messages
323
Hi there, is there an quicker method to perform the following on 'ActiveSheet"?
Thank You



For Each cell In ActiveSheet.Range("h2:h" & lr)
cell.Formula = Replace(cell.Formula, "ACA", "ACA/52")
cell.Formula = Replace(cell.Formula, "ACB", "ACB/18")
cell.Formula = Replace(cell.Formula, "ACG", "ACG/35")
cell.Formula = Replace(cell.Formula, "ACL", "ACL/32")
cell.Formula = Replace(cell.Formula, "ACP", "ACP/29")
cell.Formula = Replace(cell.Formula, "ACT", "ACT/OO")
cell.Formula = Replace(cell.Formula, "APU", "APU/39")
cell.Formula = Replace(cell.Formula, "ACU", "ACU/13")
cell.Formula = Replace(cell.Formula, "ACX", "ACX/19")
cell.Formula = Replace(cell.Formula, "ANA", "ANA/O4")
cell.Formula = Replace(cell.Formula, "ANB", "ANB/88")
cell.Formula = Replace(cell.Formula, "ANF", "ANF/36")
cell.Formula = Replace(cell.Formula, "ANG", "ANG/O8")
cell.Formula = Replace(cell.Formula, "ANP", "ANP/O1")
cell.Formula = Replace(cell.Formula, "ANT", "ANT/34")
cell.Formula = Replace(cell.Formula, "XXX", "XXX/99")
cell.Formula = Replace(cell.Formula, "CGC", "CGC/25")
cell.Formula = Replace(cell.Formula, "CGL", "CGL/22")
cell.Formula = Replace(cell.Formula, "CGM", "CGM/56")
cell.Formula = Replace(cell.Formula, "CGU", "CGU/38")
cell.Formula = Replace(cell.Formula, "CGW", "CGW/21")
cell.Formula = Replace(cell.Formula, "CNR", "CNR/O7")
cell.Formula = Replace(cell.Formula, "CNX", "CNX/91")
cell.Formula = Replace(cell.Formula, "EQI", "EQI/93")
cell.Formula = Replace(cell.Formula, "EQM", "EQM/42")
cell.Formula = Replace(cell.Formula, "EQT", "EQT/O5")
cell.Formula = Replace(cell.Formula, "FDX", "FDX/61")
cell.Formula = Replace(cell.Formula, "FOA", "FOA/69")
cell.Formula = Replace(cell.Formula, "FOC", "FOC/65")
cell.Formula = Replace(cell.Formula, "FOE", "FOE/62")
cell.Formula = Replace(cell.Formula, "FOI", "FOI/51")
cell.Formula = Replace(cell.Formula, "FOR", "FOR/64")
cell.Formula = Replace(cell.Formula, "FOU", "FOU/63")
cell.Formula = Replace(cell.Formula, "FOX", "FOX/95")
cell.Formula = Replace(cell.Formula, "GNB", "GNB/O6")
cell.Formula = Replace(cell.Formula, "GNC", "GNC/O3")
cell.Formula = Replace(cell.Formula, "GNM", "GNM/33")
cell.Formula = Replace(cell.Formula, "IFO", "IFO/68")
cell.Formula = Replace(cell.Formula, "IFT", "IFT/67")
cell.Formula = Replace(cell.Formula, "IFU", "IFU/66")
cell.Formula = Replace(cell.Formula, "IFX", "IFX/94")
cell.Formula = Replace(cell.Formula, "ISR", "ISR/26")
cell.Formula = Replace(cell.Formula, "ISS", "ISS/O2")
cell.Formula = Replace(cell.Formula, "LOD", "LOD/31")
cell.Formula = Replace(cell.Formula, "MTP", "MTP/41")
cell.Formula = Replace(cell.Formula, "MTF", "MTF/43")
cell.Formula = Replace(cell.Formula, "MGR", "MGR/44")
cell.Formula = Replace(cell.Formula, "MTM", "MTM/45")
cell.Formula = Replace(cell.Formula, "MTR", "MTR/46")
cell.Formula = Replace(cell.Formula, "MTH", "MTH/47")
cell.Formula = Replace(cell.Formula, "MTG", "MTG/48")
cell.Formula = Replace(cell.Formula, "MTD", "MTD/49")
cell.Formula = Replace(cell.Formula, "PCH", "PCH/16")
cell.Formula = Replace(cell.Formula, "PDB", "PDB/14")
cell.Formula = Replace(cell.Formula, "PSC", "PSC/11")
cell.Formula = Replace(cell.Formula, "PSG", "PSG/15")
cell.Formula = Replace(cell.Formula, "PSU", "PSU/17")
cell.Formula = Replace(cell.Formula, "PSX", "PSX/12")
cell.Formula = Replace(cell.Formula, "SEB", "SEB/87")
cell.Formula = Replace(cell.Formula, "SEE", "SEE/89")
cell.Formula = Replace(cell.Formula, "SEI", "SEI/86")
cell.Formula = Replace(cell.Formula, "SEJ", "SEJ/79")
cell.Formula = Replace(cell.Formula, "SEP", "SEP//84")
cell.Formula = Replace(cell.Formula, "SES", "SES/85")
cell.Formula = Replace(cell.Formula, "ANM", "ANM/37")
cell.Formula = Replace(cell.Formula, "SKD", "SKD/O9")
cell.Formula = Replace(cell.Formula, "SKE", "SKE/NA")
cell.Formula = Replace(cell.Formula, "SCS", "SCS/53")
cell.Formula = Replace(cell.Formula, "SDC", "SDC/55")
cell.Formula = Replace(cell.Formula, "SDX", "SDX/82")
cell.Formula = Replace(cell.Formula, "SOA", "SOA/81")
cell.Formula = Replace(cell.Formula, "SOM", "SOM/97")
cell.Formula = Replace(cell.Formula, "SOP", "SOP/96")
cell.Formula = Replace(cell.Formula, "SOT", "SOT/54")
cell.Formula = Replace(cell.Formula, "SOW", "SOW/98")
cell.Formula = Replace(cell.Formula, "SUR", "SUR/83")
cell.Formula = Replace(cell.Formula, "TRU", "TRU/92")
cell.Formula = Replace(cell.Formula, "WXD", "WXD/72")
cell.Formula = Replace(cell.Formula, "WXL", "WXL/71")
cell.Formula = Replace(cell.Formula, "WXR", "WXR/77")
cell.Formula = Replace(cell.Formula, "WXS", "WXS/75")
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Don't loop through the cells?

Code: 
    With ActiveSheet.Range("h2:h" & lr)
        .Replace "ACA", "ACA/52", xlPart
        .Replace "ACB", "ACB/18", xlPart
        .Replace "ACG", "ACG/35", xlPart
        .Replace "ACL", "ACL/32", xlPart
        .Replace "ACP", "ACP/29", xlPart
        .Replace "ACT", "ACT/OO", xlPart
        .Replace "APU", "APU/39", xlPart
        .Replace "ACU", "ACU/13", xlPart
        .Replace "ACX", "ACX/19", xlPart
        .Replace "ANA", "ANA/O4", xlPart
        .Replace "ANB", "ANB/88", xlPart
        .Replace "ANF", "ANF/36", xlPart
        .Replace "ANG", "ANG/O8", xlPart
        .Replace "ANP", "ANP/O1", xlPart
        .Replace "ANT", "ANT/34", xlPart
        .Replace "XXX", "XXX/99", xlPart
        .Replace "CGC", "CGC/25", xlPart
        .Replace "CGL", "CGL/22", xlPart
        .Replace "CGM", "CGM/56", xlPart
        .Replace "CGU", "CGU/38", xlPart
        .Replace "CGW", "CGW/21", xlPart
        .Replace "CNR", "CNR/O7", xlPart
        .Replace "CNX", "CNX/91", xlPart
        .Replace "EQI", "EQI/93", xlPart
        .Replace "EQM", "EQM/42", xlPart
        .Replace "EQT", "EQT/O5", xlPart
        .Replace "FDX", "FDX/61", xlPart
        .Replace "FOA", "FOA/69", xlPart
        .Replace "FOC", "FOC/65", xlPart
        .Replace "FOE", "FOE/62", xlPart
        .Replace "FOI", "FOI/51", xlPart
        .Replace "FOR", "FOR/64", xlPart
        .Replace "FOU", "FOU/63", xlPart
        .Replace "FOX", "FOX/95", xlPart
        .Replace "GNB", "GNB/O6", xlPart
        .Replace "GNC", "GNC/O3", xlPart
        .Replace "GNM", "GNM/33", xlPart
        .Replace "IFO", "IFO/68", xlPart
        .Replace "IFT", "IFT/67", xlPart
        .Replace "IFU", "IFU/66", xlPart
        .Replace "IFX", "IFX/94", xlPart
        .Replace "ISR", "ISR/26", xlPart
        .Replace "ISS", "ISS/O2", xlPart
        .Replace "LOD", "LOD/31", xlPart
        .Replace "MTP", "MTP/41", xlPart
        .Replace "MTF", "MTF/43", xlPart
        .Replace "MGR", "MGR/44", xlPart
        .Replace "MTM", "MTM/45", xlPart
        .Replace "MTR", "MTR/46", xlPart
        .Replace "MTH", "MTH/47", xlPart
        .Replace "MTG", "MTG/48", xlPart
        .Replace "MTD", "MTD/49", xlPart
        .Replace "PCH", "PCH/16", xlPart
        .Replace "PDB", "PDB/14", xlPart
        .Replace "PSC", "PSC/11", xlPart
        .Replace "PSG", "PSG/15", xlPart
        .Replace "PSU", "PSU/17", xlPart
        .Replace "PSX", "PSX/12", xlPart
        .Replace "SEB", "SEB/87", xlPart
        .Replace "SEE", "SEE/89", xlPart
        .Replace "SEI", "SEI/86", xlPart
        .Replace "SEJ", "SEJ/79", xlPart
        .Replace "SEP", "SEP//84", xlPart
        .Replace "SES", "SES/85", xlPart
        .Replace "ANM", "ANM/37", xlPart
        .Replace "SKD", "SKD/O9", xlPart
        .Replace "SKE", "SKE/NA", xlPart
        .Replace "SCS", "SCS/53", xlPart
        .Replace "SDC", "SDC/55", xlPart
        .Replace "SDX", "SDX/82", xlPart
        .Replace "SOA", "SOA/81", xlPart
        .Replace "SOM", "SOM/97", xlPart
        .Replace "SOP", "SOP/96", xlPart
        .Replace "SOT", "SOT/54", xlPart
        .Replace "SOW", "SOW/98", xlPart
        .Replace "SUR", "SUR/83", xlPart
        .Replace "TRU", "TRU/92", xlPart
        .Replace "WXD", "WXD/72", xlPart
        .Replace "WXL", "WXL/71", xlPart
        .Replace "WXR", "WXR/77", xlPart
        .Replace "WXS", "WXS/75", xlPart
    End With
Btw, is the bit in red below correct?
Code: 
cell.Formula = Replace(cell.Formula, "SEP", "SEP[COLOR="#FF0000"]//[/COLOR]84")
 
Last edited:
Upvote 0
Hi MARK858

Unfortunately I'm not versed enough in VBA in many many many things lol, including avoiding how to loop through this?
The 'cell.Formula = Replace(cell.Formula, "SEP", "SEP//84")' you ask about, is I couldn't find away to AVOID SEP being interpreted as a date, so the // seemed to work.
 
Upvote 0
YES I tried it, and that works very well ... I will keep that thank you. I missed it on the first look ... not only are my VBA skill less then desirable, my eye are too!
Cataracts being removed next week ... honestly!

Thank You
 
Upvote 0
jamada said:
my eye are too!
Cataracts being removed next week ... honestly!
Click to expand...


I Had cataracts removed from both eyes when I was 13 and 15, in the days before laser surgery (inherited it before you ask :rofl:).
All the best for the op.
 
Upvote 0
Thank you, right eye being done on Tuesday, the left will be done end of May, never knew how it effected ones eyesight and now I know (learned) something I'd rather not of!
Life! .... Better than the alternative lol!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top