CODEWITHSUNDEEP
vbaexcelnumbersfind
De De De De
De De De De

Reputation: 426

Get Column number of matching string in first row. Excel VBA

I'm trying to create a function to get the column number of the cell based on matching string being inserted. If there is two match being found in the first row, I would like to return the last match instead. For example, "TotalSalary Jan" and "TotalSalary Feb". With "TotalSalary" inserted as parameter, I'll be getting the column number for "TotalSalary Feb". My code:

Private Function GetColumnNumber(name As String) As Integer

Dim res As Object, ret As Integer

Set res = Sheets("Unified").Cells(1, 1).EntireRow.Find(What:=name, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If Not res Is Nothing Then
    ret = res.Column
    Do
        Set res = .FindNext(res)
        ret = res.Column
    Loop While Not res Is Nothing And res.Column <> ret
    GetColumnNumber = ret
End If

End Function

By the way, the code is not working properly. res object could not findnext for column number.

Upvotes: 1

Views: 11196

Answers (3)

De De De De
De De De De

Reputation: 426

I have created another method to add on in this function. This is working btw... I'm using the Variant Array to get Column Number.

Private Function GetColumnNumber(name As String) As Integer
    Dim play As Variant, j As Long, Current As Integer
    Set play = Sheets("Unified").Range("1:1")
    For i = 1 To play.Columns.Count
        If InStr(play(1, i), name) > 0 Then
            Current = i
        End If
    Next i
    GetColumnNumberArray = Current
End Function

I have a look at this article and it is very helpful for optimizing your code. http://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/ Apparently, the usage of find and match is very demanding command for your computer.

Upvotes: 1

MrBeAN
MrBeAN

Reputation: 78

I used a different approach, by changing the direction of the search you can find the last instance with a single find method.

Private Function GetColumnNumber(name As String) As Integer

Dim res As Object

Set res = Sheets("Unified").Cells(1, 1).EntireRow.Find(What:=name _
                                                       , LookIn:=xlValues _
                                                       , LookAt:=xlPart _
                                                       , SearchOrder:=xlByColumns _
                                                       , SearchDirection:=xlPrevious _
                                                       , MatchCase:=False)

If res Is Nothing Then
    GetColumnNumber = 0
Else
    GetColumnNumber = res.Column
End If

End Function

Upvotes: 1

Jur Pertin
Jur Pertin

Reputation: 564

Try this and let me know.

Private Function GetColumnNumber(strKeyword As String) As Integer

    Dim rngColLoop      As Range
    Dim intCounter      As Integer
    Dim intColNum       As Integer
    Dim intPrevious     As Integer
    Dim intCurrent      As Integer

    lngCounter = 0
    With Sheets("Unified").Cells(1, 1).EntireRow
        For Each rngColLoop In .Columns
            If Trim(rngColLoop) <> "" Then
                If InStr(1, UCase(Trim(rngColLoop)), UCase(Trim(strKeyword))) > 0 Then
                    intCounter = intCounter + 1
                    If intCounter = 1 Then
                        intPrevious = rngColLoop.Column
                        intCurrent = rngColLoop.Column
                    Else
                        intPrevious = intCurrent
                        intCurrent = rngColLoop.Column
                    End If

                End If
            End If
        Next rngColLoop
    End With
    If intCounter = 0 Then
        GetColumnNumber = 0
    Else
        GetColumnNumber = intCurrent
    End If

    Set rngColLoop = Nothing

End Function

Upvotes: 2

Related Questions