Proper on specific columns

jmaxwell

New Member
Joined
Nov 19, 2010
Messages
16
Hi,

I would like to use the following code below on certain columns, but not all my columns are in the same order and I have to keep updating the code to reflect the correct columns all the time. Can someone please help me the the code below to it find the column with the header name: First Name, Last Name, and City? These are the three columns that I need to use the proper code below for all my excel files. Thank you


Sub Proper_AB()
Dim LR As Long, cell As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:B" & LR)
cell.Value = StrConv(cell.Value, vbProperCase)
Next cell
End Sub
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
jmaxwell,


Sample raw data with titles in row 1:


Excel Workbook
ABCDEFGH
1First NameLast NameCity
2jmaxwellmrexcel
3
Sheet1





After the macro:


Excel Workbook
ABCDEFGH
1First NameLast NameCity
2JMaxwellMrexcel
3
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub Proper_Found_Titles()
' hiker95, 03,24,2011
' http://www.mrexcel.com/forum/showthread.php?t=538634
Dim LR As Long, cell As Range
Dim Tary, a As Long, FC As Long
Application.ScreenUpdating = False
Tary = Array("First Name", "Last Name", "City")
For a = LBound(Tary) To UBound(Tary)
  FC = 0
  On Error Resume Next
  FC = Application.Match(Tary(a), Rows(1), 0)
  If FC > 0 Then
    LR = Cells(Rows.Count, FC).End(xlUp).Row
    For Each cell In Range(Cells(1, FC), Cells(LR, FC))
      cell.Value = StrConv(cell.Value, vbProperCase)
    Next cell
  End If
Next a
Application.ScreenUpdating = True
End Sub


Then run the Proper_Found_Titles macro.
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
I feel so slow...
another same/similar version...
Code:
Sub Proper_AB()
'First Name, Last Name, and City
Dim LR As Long, cell As Range
Dim colFName, colLName, colCity As Long
Dim r0, r1, r2, r3 As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
colFName = Cells.Find(What:="First Name", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Column
colLName = Cells.Find(What:="Last Name", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Column
colCity = Cells.Find(What:="City", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Column
Set r1 = Range(Cells(1, colFName), Cells(LR, colFName))
Set r2 = Range(Cells(1, colLName), Cells(LR, colLName))
Set r3 = Range(Cells(1, colCity), Cells(LR, colCity))
Set r0 = Union(r1, r2, r3)
For Each cell In r0 ' Range("A1:B" & LR)
    cell.Value = StrConv(cell.Value, vbProperCase)
Next cell
End Sub
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
jmaxwell,

Thank you very much.

How much time do you think my macro will save you in hours per day/week/month?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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
Top