Find columns based on header names and make the column proper

[mbraund]

Hey,

I'm trying to automate some list cleanup work and would love to get some help. The goal is to find all columns that contain "xx" or "yy" and format those entire columns as proper. I found some code in another forum that looked like it might be a good start any help here is appreciated. The goal would be to have this work for multiple columns that could be in any order due to the inconsistent format we receive them back in.


Dim thisRange As Range
Set thisRange = Cells.Find(What:="First", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If thisRange Then thisRange.EntireColumn.Select


Thanks!

[Bob Phillips]

Sub ProperCase()
Dim rng As Range
Dim cell As Range
Dim firstaddress As String

Set cell = Cells.Find("xx", Range("A1"), xlValues, xlPart)
If Not cell Is Nothing Then

firstaddress = cell.Address
Set rng = cell
Do

Set cell = Cells.FindNext(cell)
If Not cell Is Nothing Then

Set rng = Union(rng, cell)
End If
Loop Until cell Is Nothing Or cell.Address = firstaddress


Set cell = Cells.Find("yy", Range("A1"), xlValues, xlPart)
If Not cell Is Nothing Then

firstaddress = cell.Address
Set rng = Union(rng, cell)
Do

Set cell = Cells.FindNext(cell)
If Not cell Is Nothing Then

Set rng = Union(rng, cell)
End If
Loop Until cell Is Nothing Or cell.Address = firstaddress
End If
End If

If Not rng Is Nothing Then

For Each cell In rng

cell.Value = Application.Proper(cell.Value)
Next cell
End If
End Sub

[mbraund]

Thanks for the quick reply. I've tried placing this into my module, updating the find values and received no received no errors but also no results. I'm guessing that I need to update the range in which it searching or does that not matter where it says cells.Find("first", Range("A1")...?

Sub ProperCase()'
' Proper Macro
' =proper() for first name, last name, company, title, address 1, address 2, city, province (non US)


Dim rng As Range
Dim cell As Range
Dim firstaddress As String

Set cell = Cells.Find("first", Range("A1"), xlValues, xlPart)
If Not cell Is Nothing Then

firstaddress = cell.Address
Set rng = cell
Do

Set cell = Cells.FindNext(cell)
If Not cell Is Nothing Then

Set rng = Union(rng, cell)
End If
Loop Until cell Is Nothing Or cell.Address = firstaddress


Set cell = Cells.Find("last", Range("A1"), xlValues, xlPart)
If Not cell Is Nothing Then

firstaddress = cell.Address
Set rng = Union(rng, cell)
Do

Set cell = Cells.FindNext(cell)
If Not cell Is Nothing Then

Set rng = Union(rng, cell)
End If
Loop Until cell Is Nothing Or cell.Address = firstaddress
End If
End If

If Not rng Is Nothing Then

For Each cell In rng

cell.Value = Application.Proper(cell.Value)
Next cell
End If
End Sub

Also if I'm going to repeat this would is the code snippet I would copy below this piece with the last loop having two End Ifs?


Set cell = Cells.Find("last", Range("A1"), xlValues, xlPart)
If Not cell Is Nothing Then

firstaddress = cell.Address
Set rng = Union(rng, cell)
Do

Set cell = Cells.FindNext(cell)
If Not cell Is Nothing Then

Set rng = Union(rng, cell)
End If
Loop Until cell Is Nothing Or cell.Address = firstaddress
End If

[snb]

you can have a start with:


Sub M_snb()
on error resume next

for each cl in columns(sheet1.rows(1).find("first",,,1).column).specialcells(2)
cl.value=strconv(cl.value,3)
next
End Sub

[mbraund]

you can have a start with:


Sub M_snb()
on error resume next

for each cl in columns(sheet1.rows(1).find("first",,,1).column).specialcells(2)
cl.value=strconv(cl.value,3)
next
End Sub

Are you referring to the start of the whole piece of code or above each find command? Would I want the sub tags around it or should it just include this piece?

On Error Resume Next
For Each cl In columns(sheet1.rows(1).find("first",,,1).column).specialcells(2)
cl.value=strconv(cl.value,3)
Next