Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Determining Column Number 1

Status
Not open for further replies.

sabascal

IS-IT--Management
Aug 20, 2002
85
GB
Given a line containing column title. Is there a build in VBA function that takex as argument a string an that return the column number where this string is?
 
I'm not sure I understand what you want, but

[tab]Cells.Columns("B").Column

will return 2.

Does that help ?

A.C.
 
Sorry for my approximate description
Row 1 contains names: "Attribute 1", "Attribute 2",....
Given an attribute name how can I get the column number?
 
This is not addressing my problem.
I need a function that take as argument my attribute name and that return to me the column number where it is stored
 
This should do the trick. It assumes that your titles are in Row1 of Sheet1, but can be modified to suit your WorkSheet.

Code:
Public Sub PassArg()

Dim strArg As String
Dim intColumn As Integer

strArg = ""
'Code to assign the value which you're looking for.

Code:
intColumn = ColumnRef(strArg)

End Sub

Public Function ColumnRef(strArg As String) As Integer

Sheet1.Select
Cells(1, 1).Select

Do Until ActiveCell.Value = strArg

    ActiveCell.Offset(0, 1).Select

Loop

    ColumnRef = ActiveCell.Column

End Function

Hope this helps.

Leigh Moore
Solutions 4 MS Office Ltd
 
Try ,
Code:
Function ColumnNumber(rng As String, str As String) As Integer
    With Worksheets(1).Range(rng)
        Set c = .Find(str, LookIn:=xlValues)
        If Not c Is Nothing Then
            ColumnNumber = c.Column
        Else
            ColumnNumber = 0
        End If
    End With
End Function

In your example you would use

[tab]=ColumnNumber("1:1","Atribute 1")

Where "1:1" is the range (row 1, in this example) you wnat to search in.

A.C.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top