Creating a Procedure to Call HeaderName but no success to call a procedure for HeaderName’s Column Position

[SamDsouza]

Hello

Was trying to create a Procedure to Call Header Name and it Column position

Got stuck as I could not put together to get the column Reference of Column Header Name.

In module

Call HeaderNameColPosName("Header9")
Call ColPosof(“Header9”)

What needs to be coded for Exact Col pos of ColHeader to derive it succesffuly to get the correct Column Reference when procedure called for “Header9” like above
I need to call another procedure for the Column Reference for above
So when

Call ColPosof(“Header9”)

I should get as colPosofHeader9.Column

And then I can utilise wherever required like below

For eg

as wsRng.Columns(ColPosofHeader9.Column).Address(0,0)

Private Sub HeaderNameColPosName(SrchHeaderName As String)
Dim SrchtxtHeaderName As String
Dim ColFindHeaderName As Range
Public ColNamePos As String
Dim ColumnLetter As String

With Worksheets("Sheet1").Range("A1:J1")
               SrchtxtHeaderName = SrchHeaderName
               Set ColFindHeaderName = .Find(What:=SrchtxtHeaderName, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
               ColumnLetter = Replace(Cells(1, ColFindHeaderName.Column).Address(0, 0), 1, "")
               ColNamePos = ColumnLetter
            ‘’’’  MsgBox SrchHeaderName & " is with Col.Name " & ColNamePos
End With
End Sub

Your help will be highly appreciated
Thanks SamD
88

 

[dmt32]

One idea maybe would be to make a function of the code to return the column number

Something like following

Function HeaderNameColPosName(ByVal SrchHeaderName As String) As Variant
    Dim arr As Variant
   
    arr = Worksheets("Sheet1").Range("A1:J1").Value2
             
    HeaderNameColPosName = Application.Match(SrchHeaderName, arr, 0)

End Function

and to test idea

Sub atest()
    Dim HeaderName, whichcolumn
   
    HeaderName = "Header9"
   
    whichcolumn = HeaderNameColPosName(HeaderName)
'name not found
    If IsError(whichcolumn) Then Exit Sub
   
    MsgBox HeaderName & " is with Column " & Columns(whichcolumn).Address & " Column Number " & whichcolumn
End Sub

If suggestion is what you are looking for you could add another parameter to function to pass the Range - this would allow you to use it on other worksheets / ranges.

Hope Helpful

Dave

 

[mohadin]

ColNamePos = ColFindHeaderName.Column

 

[SamDsouza]

Thanks Dave(dmt32) for a quick response
I would like to know
arr = Worksheets("Sheet1").Range("A1:J1").Value2 for what purpose you have used value2

If suggestion is what you are looking for you could add another parameter to function to pass the Range - this would allow you to use it on other worksheets / ranges.

How can you do the above

Will it be not possible to use
Call aTest("Header9")
I ran above it gave me invalid outside ..... error
89

 

[dmt32]

Thanks Dave(dmt32) for a quick response
I would like to know
arr = Worksheets("Sheet1").Range("A1:J1").Value2 for what purpose you have used value2

The difference between Range.Value and Range.Value2 is the data types they work with -

Range.Value can return formatted Currency and date

Range.Value2 returns just the underlying value which includes empty, string, error, number and Boolean

How can you do the above

Just add a range parameter

Function HeaderNameColPosName(ByVal Target As Range, ByVal SrchHeaderName As String) As Variant
    Dim arr As Variant
    
    arr = Target.Value2
              
    HeaderNameColPosName = Application.Match(SrchHeaderName, arr, 0)

End Function

You then pass the range to it

whichcolumn = HeaderNameColPosName(Worksheets("Sheet1").Range("A1:J1"), HeaderName)

Will it be not possible to use
Call aTest("Header9")
I ran above it gave me invalid outside ..... error
89

You can do that but would need to place the Variable in code as a parameter to pass value to the code

Sub atest(ByVal HeaderName As String)
    Dim whichcolumn
    
    
    whichcolumn = HeaderNameColPosName(Worksheets("Sheet1").Range("A1:J1"), HeaderName)
'name not found
    If IsError(whichcolumn) Then Exit Sub
    
    MsgBox HeaderName & " is with Column " & Columns(whichcolumn).Address & " Column Number " & whichcolumn
End Sub

Dave

 

[SamDsouza]

Thanks Dave Will check and revert

SamD
90

 

[SamDsouza]

I tried as per your post #5 and this time code was executed in userform added the below command button
It worked till Calling atest("Header7") but also tried to get Columns(whichcolumn).Address - raised the error Application Defined Error.......
the reason to get reference of .Columns(whichcolumn).Address is to work with that column (Whichcolumn)
this is to get exactly as per my #1
ie
Call aTest("Header7")
.Columns(whichcolumn).Address or Calling whichColumn("Header7")
End Sub

Private Sub CommandButton1_Click()
   Dim whichcolumn
   Call atest("Header7")
   Worksheets("Sheet1").Cells(2, 7).Value = Worksheets("Sheet1").Columns(whichcolumn).Address
End Sub

91

 

[dmt32]

I tried as per your post #5 and this time code was executed in userform added the below command button
It worked till Calling atest("Header7") but also tried to get Columns(whichcolumn).Address - raised the error Application Defined Error.......
the reason to get reference of .Columns(whichcolumn).Address is to work with that column (Whichcolumn)
this is to get exactly as per my #1
ie
Call aTest("Header7")
.Columns(whichcolumn).Address or Calling whichColumn("Header7")
End Sub

Private Sub CommandButton1_Click()
   Dim whichcolumn
   Call atest("Header7")
   Worksheets("Sheet1").Cells(2, 7).Value = Worksheets("Sheet1").Columns(whichcolumn).Address
End Sub

91

If want to use code in that manner then couple changes required

In the atest code you need to include the whichcolumn variable as an argument

Private Sub CommandButton1_Click()
   Dim whichcolumn
   Call atest("Header7", whichcolumn)
   Worksheets("Sheet1").Cells(2, 7).Value = Worksheets("Sheet1").Columns(whichcolumn).Address
End Sub

In the atest code itselef, also needs to be added as a parameter

Sub atest(ByVal HeaderName As String, ByRef whichcolumn As Variant)
    
    whichcolumn = HeaderNameColPosName(Worksheets("Sheet1").Range("A1:J1"), HeaderName)
'name not found
    If IsError(whichcolumn) Then Exit Sub
    
    MsgBox HeaderName & " is with Column " & Columns(whichcolumn).Address & " Column Number " & whichcolumn
End Sub

This should return the value in whichcolumn to calling procedure.

Dave

 

[SamDsouza]

Dave Thank you so much for your wonderful help and explanation. This has really encouraged me to develop functions and procedures for which i had less confidence.
Indeed your solution was perfect to the need.
Really Grateful to you and appreciate your time and efforts spend for explanation and solution. ?

SamD
92

 

[dmt32]

Many thanks for your kind feedback – very much appreciated



Dave