Code to find column by name

[sandy40]

Hi,

I have a scenario but not able to start .. If we have a Column called as Salary in Excel and this Column for the First Time will be in A1 then next time can be in Z1 so I need to write a code to find the Column Name ' Salary' Can some one please help me with this

 

[Michael M]

Re: How to find a column in Excel by writing a Code - If there is a column called as Salary where in Salary Column can change i.e for the First Time the Salary Column Name will be in A1 then it will be in Z1 if that being the case how to find ColumnN

Maybe this

Sub MM1()
Dim c As Integer
For c = 1 To Columns.Count
    If Cells(1, c).Value = "Salary" Then
        Cells(1, c).EntireColumn.Select
    End If
Next c
End Sub

 

[GTO]

Re: How to find a column in Excel by writing a Code - If there is a column called as Salary where in Salary Column can change i.e for the First Time the Salary Column Name will be in A1 then it will be in Z1 if that being the case how to find ColumnN

Greetings and welcome to the site Sandy

Another way would be to use the .Find method.

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]juliette[/TD]
[TD]afroasiatic[/TD]
[TD]blackthorn[/TD]
[TD]synaeresis[/TD]
[TD]salary[/TD]
[TD]windcheater[/TD]
[TD]sacculation[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

In a Standard Module:

Option Explicit
  
Sub example()
Dim rngSalary As Range
  
  'Using the .Find method (which I happen to use a wrapper function for my preferred defaults), either
  'retun a Range object if found, or Nothing if not
  Set rngSalary = RangeFound(ThisWorkbook.Worksheets("Sheet1").Rows(1), "Salary", , , xlWhole, , xlNext)
  
  'Test to see if we found it and proceed.
  If Not rngSalary Is Nothing Then
    MsgBox "Found at " & rngSalary.Address
  Else
    MsgBox "Not found"
  End If


End Sub
  
Function RangeFound(SearchRange As Range, _
                    Optional ByVal FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange.Cells(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function