Abstract this sub

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?

Microsoft ExcelVBAMicrosoft OfficeMicrosoft Applications

Last Comment

Martin Liss

8/22/2022 - Mon

Martin Liss

8/10/2016

Cells.Find(What:="PM").Activate
ActiveCell.EntireColumn.ColumnWidth = 5.14

Martin Liss

8/10/2016

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

NVIT

8/10/2016

ASKER

That works, Martin.
How to provide for if search failed?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

Martin Liss

8/10/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

NVIT

8/10/2016

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.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 Liss

8/10/2016

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