NVIT
asked on
Abstract this sub
I recorded this. It specifically finds the PM column header.
Then sets the column width.
I'd like to abstract this so I can pass the column name and desired width. e.g.
Is there a way to 'generalize' Columns("E:E").Select so that it knows what column to select, regardless of its location?
Can you help?
Then sets the column width.
Sub Set_Col_Wid_PM()
Range("A1").Select
Cells.Find(What:="PM", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Columns("E:E").Select
Selection.ColumnWidth = 5.14
End Sub
I'd like to abstract this so I can pass the column name and desired width. e.g.
= Set_Col_Wid("PM", 5.14)
Is there a way to 'generalize' Columns("E:E").Select so that it knows what column to select, regardless of its location?
Can you help?
Continuing my post, if there's any possibility of "PM" appearing anyplace else then row 1.
With Rows(1)
.Cells.Find(What:="PM").Activate
ActiveCell.EntireColumn.ColumnWidth = 5.14
End With
ASKER
That works, Martin.
How to provide for if search failed?
How to provide for if search failed?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! Thank you! Have a nice day/night...
With a few changes, I figured out the abstraction part...
[code]Sub Col_Wid(strFind, nColWidth)
On Error GoTo ErrNotFound:
Range("A1").Select
Cells.Find(What:=strFind). Activate
ActiveCell.EntireColumn.Co lumnWidth = nColWidth
Exit Sub
ErrNotFound:
MsgBox "Col_Wid. Column " & strFind & " not found"
End Sub
[/code]
...so I can use it like:
[code]
Sub SetColWidths()
Col_Wid "PN", 9.86
Col_Wid "Project Title", 45
End Sub
[/code]
With a few changes, I figured out the abstraction part...
[code]Sub Col_Wid(strFind, nColWidth)
On Error GoTo ErrNotFound:
Range("A1").Select
Cells.Find(What:=strFind).
ActiveCell.EntireColumn.Co
Exit Sub
ErrNotFound:
MsgBox "Col_Wid. Column " & strFind & " not found"
End Sub
[/code]
...so I can use it like:
[code]
Sub SetColWidths()
Col_Wid "PN", 9.86
Col_Wid "Project Title", 45
End Sub
[/code]
You're welcome and I'm glad I was able to help.
In my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
In my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
ActiveCell.EntireColumn.Co