Need my Macro to replace words with abbreviations

[ahetzel10]

Hi all,

I am running a macro to delete and hide some columns of weekly data to quickly create a sheet small enough to print out. I want the macro to automatically replace a couple phrases such as "single family" with "SF", and "new construction" with "NC". How can I incorporate this into my Macro?

Thanks!

 

[Fluff]

Hi & welcome to the board
How about

Sub Abbreviate()
   With Columns(1)
      .Replace "single family", "SF", xlPart, , False, , False, False
      .Replace "new construction", "NC", xlPart, , False, , False, False
   End With
End Sub

Changing the column to suit

 

[ahetzel10]

I appreciate the quick help! Now do I just edit the existing code and place this at the end?

 

[Fluff]

Just copy the code (without the sub & End sub lines) & paste it into your existing code.

 

[ahetzel10]

Ok this is what my macro produces for me. Becuase of deleted and hidden columns, "Project type" is column G but is the fifth column from the left.

This is what the end of my attempted code currently looks like with your addition. Not sure what I am doing wrong. Sorry I don't know much about coding...

With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "Permit#"
Columns("H:H").Select
Selection.Copy
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ProjectCity"
Range("H2").Select
With Columns(5)
.Replace "Single Family (Dev.Only)", "SF", xlPart, , False, , False, False
.Replace "new construction", "NC", xlPart, , False, , False, False
End With
End Sub

[TABLE="width: 1106"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Permit#[/TD]
[TD]PermitDate[/TD]
[TD]ProjectAddr[/TD]
[TD]ProjectName[/TD]
[TD]ProjectType[/TD]
[TD]ProjectCity[/TD]
[TD]Company[/TD]
[TD]Phone[/TD]
[TD]Size[/TD]
[TD]Value[/TD]
[TD]WorkType[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/7/2017[/TD]
[TD]808 CASTILLA WY[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]115596[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/23/2017[/TD]
[TD]426 RENOWN CT[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]159626[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9/24/2017[/TD]
[TD]1222 DIANNE DR[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]116530[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10/12/2017[/TD]
[TD]2006 MASSEY LN[/TD]
[TD]PINNACLE @ YARGO BLD[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]VISION HOME CONSTRUCTION, INC.[/TD]
[TD]404-569-4217[/TD]
[TD] [/TD]
[TD]130546[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]9/24/2017[/TD]
[TD]1220 DIANNE DR[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]115595[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10/12/2017[/TD]
[TD]2008 MASSEY LN[/TD]
[TD]PINNACLE @ YARGO BLD[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]VISION HOME CONSTRUCTION, INC.[/TD]
[TD]404-569-4217[/TD]
[TD] [/TD]
[TD]127269[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9/24/2017[/TD]
[TD]1218 DIANNE DR[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]116530[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10/12/2017[/TD]
[TD]2010 MASSEY LN[/TD]
[TD]PINNACLE @ YARGO BLD[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]VISION HOME CONSTRUCTION, INC.[/TD]
[TD]404-569-4217[/TD]
[TD] [/TD]
[TD]131742[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]9/24/2017[/TD]
[TD]1216 DIANNE DR[/TD]
[TD]SUTHERLAND[/TD]
[TD]Single Family (Dev.Only)[/TD]
[TD]WINDER[/TD]
[TD]LGI HOMES[/TD]
[TD]855-441-6300[/TD]
[TD] [/TD]
[TD]115595[/TD]
[TD]New Construction[/TD]
[TD]Barrow[/TD]
[/TR]
</tbody>[/TABLE]

 

[ahetzel10]

Hi, I posted some data on here and it said it needed your approval and it disappeared. I can't get the code to work so I was trying to show you whats going on.

 

[Joe4]

Hi, I posted some data on here and it said it needed your approval and it disappeared.

That is just telling you that it needs Moderator approval before you will be able to see it.
I approved it and you can see it now.

 

[Fluff]

Change Columns(5) to Columns(7)
Col G is the 7th column regardless of hidden columns.

 

[ahetzel10]

Worked! Thank you so much. This will be a big help.

 

[Fluff]

Glad to help & thanks for the feedback