Excel, Microsoft Office, VBA

VBA – Busca de texto de células retornando o número da linha

Encontrado nas andanças na internet, a macro abaixo faz exatamente a função “Localizar” do Excel, só que no VBA.

Public Function FindRowPos(sText As Variant, _
                           Optional SearchDirection As XlSearchDirection = xlNext, _
                           Optional SearchOrder As XlSearchOrder = xlByRows) As Long
 
    Dim lResult As Long, oRg As Range
 
    Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
                         LookAt:=xlPart, SearchOrder:=SearchOrder, _
                         SearchDirection:=SearchDirection, _
                         MatchCase:=False, SearchFormat:=False)
 
    If Not oRg Is Nothing Then lResult = oRg.Row
 
    FindRowPos = lResult
 
    Set oRg = Nothing
End Function

O exemplo de chamada seria bem simples:

Public Sub TestandoOBuscar()
    MsgBox FindRowPos("bla")
    MsgBox FindRowPos("bla", xlNext)
    MsgBox FindRowPos("bla", xlNext, xlByColumns)
End Sub

Bom proveito!

Referências

http://snippets.dzone.com/posts/show/13521

Comentários

comentários