Abstract this sub

NVIT
NVIT used Ask the Experts™
on
I recorded this. It specifically finds the PM column header.
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

Open in new window


I'd like to abstract this so I can pass the column name and desired width. e.g.

= Set_Col_Wid("PM", 5.14)

Open in new window


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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Cells.Find(What:="PM").Activate
ActiveCell.EntireColumn.ColumnWidth = 5.14
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

NVITEnd-user support

Author

Commented:
That works, Martin.
How to provide for if search failed?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Sub SetIt()
With Rows(1)
    On Error GoTo ErrorRoutine
    .Cells.Find(What:="PM").Activate
    ActiveCell.EntireColumn.ColumnWidth = 5.14
End With
Exit Sub
ErrorRoutine:
    Dim strCol As String
    strCol = InputBox("Please enter the column letter")
    Columns(strCol).ColumnWidth = 5.14
End Sub

Open in new window

NVITEnd-user support

Author

Commented:
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.ColumnWidth = 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]
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial