Find, Intersect Title

[Guzzlr]

Hello All,

Sub Find_ManHours()

   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("MAN-HOURS:", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
      Range("G4").PasteSpecial Paste:=xlPasteValues
   End If
End Sub

Having an issue...as always
The following code is finding Man-Hours, and placing it in cell G4. However, I also want to find the number associated with Man-Hours. This number is in the same column of A:A, and the next cell lower. For example, if Man-Hours is in A10, the number associated with it will be the number in A11.
Then, that number should be copy/paste as values in G5, next to the intersect of Man-Hours in G4.
Thanks for the help

 

[Fluff]

how about

Sub Find_ManHours()

   Dim Fnd As Range
   
   Set Fnd = Range("A:A").find("MAN-HOURS:", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("G4:G5").Value = Fnd.Resize(2).Value
   End If
End Sub

 

[Guzzlr]

how about

Sub Find_ManHours()

   Dim Fnd As Range
   
   Set Fnd = Range("A:A").find("MAN-HOURS:", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("G4:G5").Value = Fnd.Resize(2).Value
   End If
End Sub

A couple of things:
1. The number is placed in G5, how can the number be placed in H4, so its next to Man-Hours in G4?
2. Also, I forgot to say, there are multiple instances of: "Man-Hours" in Column A:A, each separate instance will have a number associated it with it in the cell directly below.

So if I have Man-hours in A:12, and 6.0 in A:13, and another instance of Man-Hours in A:23, and an number 9 in A:24 then the output will have:
Man-Hours in G:4, and 6.0 in H:4
Man-Hours in G:5, and 9.0 in H:5

and So on down to the last row with data.
Thanks for the help

 

[Fluff]

It's in G5 because that's where you said you wanted it
To put it in H4 use

Sub Find_ManHours()

   Dim Fnd As Range
   
   Set Fnd = Range("A:A").find("MAN-HOURS:", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("G4:H4").Value = Application.Transpose(Fnd.Resize(2).Value)
   End If
End Sub

To find all the values you'll need to loop through the column. have a look here http://www.cpearson.com/excel/findall.aspx

 

[Guzzlr]

To find all the values you'll need to loop through the column. have a look here http://www.cpearson.com/excel/findall.aspx

With Range("B1:B10")
    Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Range("B1:B10").Find("MAN-HOURS", , , xlWhole, , , False, , False, after:=LastCell)

If Not FoundCell Is Nothing Then
 Range("G2:H2").Value = Application.Transpose(Fnd.Resize(2).Value)
    'FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
    Debug.Print FoundCell.Address
    Set FoundCell = Range("A1:A10").FindNext(after:=FoundCell)
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

End Sub

So I've been playing around with the idea of Carson. I'm faulting on the "Set FoundCell after:=LastCell. Also, I'm not seeing how to use "FirstAddr = FoundCell.Address" if I'm trying to Transpose the found Man Hours in Range G2:H2.
Thanks for the help

 

[Fluff]

try

Sub Find_ManHours()

   Dim Fnd As Range
   Dim Faddr As String
   Dim i As Long
   i = 4
   
   Set Fnd = Range("A:A").find("MAN-HOURS", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("G" & i).Resize(, 2).Value = Application.Transpose(Fnd.Resize(2).Value)
      i = i + 1
      Faddr = Fnd.Address
   End If
   Do
      Set Fnd = Range("A:A").FindNext(Fnd)
      If Fnd.Address = Faddr Then Exit Do
      Range("G" & i).Resize(, 2).Value = Application.Transpose(Fnd.Resize(2).Value)
      i = i + 1
   Loop
   
End Sub

 

[Guzzlr]

try

Sub Find_ManHours()

   Dim Fnd As Range
   Dim Faddr As String
   Dim i As Long
   i = 4
   
   Set Fnd = Range("A:A").find("MAN-HOURS", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("G" & i).Resize(, 2).Value = Application.Transpose(Fnd.Resize(2).Value)
      i = i + 1
      Faddr = Fnd.Address
   End If
   Do
      Set Fnd = Range("A:A").FindNext(Fnd)
      If Fnd.Address = Faddr Then Exit Do
      Range("G" & i).Resize(, 2).Value = Application.Transpose(Fnd.Resize(2).Value)
      i = i + 1
   Loop
   
End Sub

As always...I'd be lost with out you guys...Thanks for the help!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Guzzlr]

   Set Fnd = Range("A:A").find("MAN-HOURS", , , xlWhole, , , False, , False)

Getting back to this discussion.
For the .find, the code finds "MAN-HOURS". for the column of "A:A", can that same code logic use a "wildcard" to find "MAN-HOURS"?
For example. If row 739 in column A has the statement: "5. OPERATIONAL CHECK MAN-HOURS", the code would still pick up "MAN-HOURS" even though there are other characters in that cell?
Thanks for the help

 

[Fluff]

Simply xchange xlWhole to xlPart