Find Them and Extract Them – vba Macro

[Baconbo]

Find Them and Extract Them – vba Macro

Hi Chandoo – As with everything you've produced, this is a really interesting and creative approach to simplify large database work. I have been trying to extend the Skills extracted information (cell I5) to include the corresponding Company (cell K5), Name (cell L5), and Title (cell M5) information. Every modification I've made to your macro to accomplish this has resulted in various types of errors. It didn't seem to be that difficult a task initially (I'm only "fair" at vba), but I'm out of ideas to make this work. Need help.

Thanks for all your awesome Excel user examples!!

Baconbo

 

[Baconbo]

Well, as I approached this issue with fresh eyes, I was able to work out the answer to my own problem. Now that I know the answer, I am humbled by the simplicity of the code. It is as follows:

Sub findAll()
  Dim findWhat As String, address As String
  Dim frs As Range, rs As Range, fCount As Long, rs2 As Range, rs3 As Range, rs4 As Range
 
  findWhat = InputBox("Enter what you want to find?", "Find what...")
 
  If Len(findWhat) > 0 Then
  clearFinds
  Set frs = Range("b4").CurrentRegion

  Set rs = frs.Find(What:=findWhat)

  If Not rs Is Nothing Then
  address = rs.address
  Do
  Range("I5").Offset(fCount).Value = rs.Value
  Range("J5").Offset(fCount).Value = rs.address
  Range("k5").Offset(fCount).Value = rs.Offset(0, -1)
  Range("l5").Offset(fCount).Value = rs.Offset(0, -2)
  Range("m5").Offset(fCount).Value = rs.Offset(0, 1)

  Set rs = frs.FindNext(rs)

  fCount = fCount + 1
  Loop While Not rs Is Nothing And rs.address <> address
  End If
  End If
End Sub

Thanks for being there when I needed to ask the question!!

Baconbo

 

[SirJB7]

Hi, Baconbo!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, glad you could solve it by yourself and thank you for sharing with the community.

Regards!