Proper on specific columns

[jmaxwell]

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

 

[hiker95]

jmaxwell,


Sample raw data with titles in row 1:

Excel Workbook
	A	B	C	D	E	F	G	H
1		First Name			Last Name		City
2		j			maxwell		mrexcel
3
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1		First Name			Last Name		City
2		J			Maxwell		Mrexcel
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.

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]

I feel so slow...
another same/similar version...

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

 

[jmaxwell]

This is going to save me so much time. Thank you and have a great day!

 

[hiker95]

jmaxwell,

Thank you very much.

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