CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Convert String To Column Named Range

I want to pass a column number and a string to a sub that names the specified column after the string. For example, if my sub call is nameColumn(1, "Some Name"), then the sub names column 1 "SomeName".

My code:

Sub nameColumn(colNum as Integer, passedString as String)

    Dim ws As Worksheet
    Dim colName As String

    Set ws = Sheets("Sheet1")

    'remove any spaces from the string
    colName = Replace(passedString, " ", "")
    'name the column
    ws.Columns(colNum).Name = colName

End Sub

I realize I'm trying to name a range after a string. How do I convert the string in order to use it as the column named range?

Upvotes: 1

Views: 217

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

Your code is missing one important line (see " specify ws object"), otherwise it should work fine:

Sub nameColumn(colNum As Integer, passedString As String)
    Dim ws As Worksheet

    'remove any spaces from the string
    colName = Replace(passedString, " ", "")

    ' specify ws object, for example the first one
    Set ws = Worksheets(1)

    'name the column
    ws.Columns(colNum).Name = Replace(passedString, " ", "")

End Sub

    Sub Test()
        Call nameColumn(2, "TestName")
    End Sub

Sample Sub Test() will assign the string "TestName" as a name for entire 2nd Column (Worksheet with index 1, Column "B").

In a simplified form, you can omit the explicit set of Worksheet object, resulting in a rather compact code snippet like shown below:

Sub nameColumn(colNum As Integer, passedString As String)
    'remove any spaces from the string
    colName = Replace(passedString, " ", "")

    'name the column
    Columns(colNum).Name = Replace(passedString, " ", "")
End Sub

Hope this may help.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub nameColumn(colNum As Integer, passedString As String)

    Dim colName As String

    'remove any spaces from the string
    colName = Replace(passedString, " ", "")
    'name the column
    Cells(1, colNum).EntireColumn.Name = colName

End Sub

Sub main()
    Call nameColumn(1, "whatever")
End Sub

enter image description here

Your posted code was almost correct. You needed to Set ws

Upvotes: 1

Related Questions