Microsoft Excel - Find text in a row - Asked By Dan on 11-Sep-11 07:36 PM

I am trying to find text in a row.  The cells in the row are from H10:S10.  It has the months in it starting with JAN (H10) and goes to DEC (S10).  I already have the part of the code that knows what month I an looking for.  I need to be able to find that month in H10:S10 and select that cell. From there I will paste the data starting in the cell below and continue down the column until all data has been pasted.  I am trying match but not having much luck, any suggestion will be appreciated.

Dan
wally eye replied to Dan on 11-Sep-11 10:33 PM
There is quite a bit more that probably should be added to this, but this will get you started:


Public Sub CopyData(ByVal rngHeader As Range, ByVal strMonth As String)

    Dim arrHeader       As Variant

    Dim intPos        As Integer

    arrHeader = rngHeader
    For intPos = LBound(arrHeader, 2) To UBound(arrHeader, 2)
      If arrHeader(1, intPos) = strMonth Then
        rngHeader.Cells(1, intPos).Offset(1, 0) = 1
        Exit For
      End If
    Next intPos

    If intPos > UBound(arrHeader, 2) Then
      MsgBox "Invalid Month input", vbOKOnly
    End If

End Sub


I didn't do the application.match because it would return an error if it wasn't found.  You would call it in VBA like:

call copydata(worksheets("Sheet1").Range("H10:S10"), "Jun")

If you had a range to paste into it, you could do something like this:

Public Sub CopyData(ByVal rngHeader As Range, ByVal strMonth As String, ByVal rngCopy As Range)

    Dim arrHeader       As Variant

    Dim intPos        As Integer

    arrHeader = rngHeader
    For intPos = LBound(arrHeader, 2) To UBound(arrHeader, 2)
      If arrHeader(1, intPos) = strMonth Then
        rngHeader.Cells(1, intPos).Offset(1, 0).Resize(rngCopy.Rows.Count, 1) = rngCopy.Value
        Exit For
      End If
    Next intPos

    If intPos > UBound(arrHeader, 2) Then
      MsgBox "Invalid Month input", vbOKOnly
    End If

End Sub


Passing the range you are copying into the procedure:

call copydata(worksheets("Sheet1").Range("H10:S10"), "Jun", worksheets("Sheet1").Range("D15:D27"))

You could just as easily pass an array.
Jitendra Faye replied to Dan on 11-Sep-11 11:50 PM
Try this Macro-



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


Try this and let me know.
Dan replied to Jitendra Faye on 12-Sep-11 06:43 AM
Does not work.  If i am looking for JAN how can I find it in H10:S10 ?  THis has to be simple, but it is frustrating me.
Radhika roy replied to Dan on 12-Sep-11 10:16 AM

Try this Macro-

 

' Input param: Text we want to look for

' Optional input params:

'   Search direction (forward, backward),

'   Search order (in row or column)

' Output: row position of the text being searched

 

Private Function pFindRowPos(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

 

   pFindRowPos = lResult

 

   Set oRg = Nothing

 

End Function

hope this will help you,.