Link to home
Start Free TrialLog in
Avatar of NVIT
NVITFlag for United States of America

asked on

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?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Cells.Find(What:="PM").Activate
ActiveCell.EntireColumn.ColumnWidth = 5.14
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

Avatar of NVIT

ASKER

That works, Martin.
How to provide for if search failed?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NVIT

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]
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