Find, Intersect Title

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Hello All,
Code:
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
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
So getting back to this, my code Fluff gave me is:
Code:
Set Fnd = Range("A:A").Find("MAN-HOURS", , , xlPart, , , 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("M" & i).Resize(, 2).Value = Application.Transpose(Fnd.Resize(2).Value)
        i = i + 1
    Loop

This is working and finding the number associated with Man-Hours that is one cell over, and down.
However, I also now need to find the number associated with Man Hours, which is over one cell but also on the same row...so there is two searches for the number associated with Man-Hours. one that is one cell over and down one row, and the other that is one cell over, and on the same row.
Would it be in the RESIZE area using an AND statement? Instead of Resize(,2).value. maybe Resize(,2) AND Resize(,1).value
Or am I completely wrong, and another loop is included?
Thanks
 
Upvote 0
If "Man hours" is found in A5 what cells do you want copied & where do you want them placed?
 
Upvote 0
If "Man hours" is found in A5 what cells do you want copied & where do you want them placed?

So when I export PDF to excel, I get for example: "Total Man-Hours/Clcok-Hours 100.0/51.5...all in one cell. If this statement is in M3, then Man-Hours would be in M3 and 100.0 would be in M4
Thanks for the help
 
Last edited:
Upvote 0
That does not answer my question. ;)
 
Last edited:
Upvote 0
If "Man hours" is found in A5 what cells do you want copied & where do you want them placed?

If Man Hours is found in A5, the hours associated with Man Hours will also be in A5. So,Man Hours would be placed in M3 and the Hours associated placed in N3.
However, Man Hours may be found anywhere in column A, depending on the document...
Thanks
 
Upvote 0
It seems that you now want to split the contents of a cell, into different cells.
If that is the case, then it's a totaly different question so you will need to start a new thread.
 
Upvote 0
It seems that you now want to split the contents of a cell, into different cells.
If that is the case, then it's a totaly different question so you will need to start a new thread.

K, thanks
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top