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