PDA

View Full Version : Solved: Extract and find



Gil
12-15-2010, 07:43 AM
I have the following formula which extracts the number 569 from 2085697325. The formula is =MID(B4,FIND("",B4)+3,3). I then need to find 569 in either of 2 cells containing the following 737,847,232,568,255 and 560,587,569,758,326,580. I have tried Find,Match Exact and some others but am now stuck.
The end result I would like to achieve at the moment is to id the cell containing the 569, delete the contents of the other cell,copy the found cell and paste in a new cell.
I hope that makes sense, please see attachment.

Bob Phillips
12-15-2010, 08:12 AM
That will all need VBA. Look at Instr in VBA help.

Gil
12-15-2010, 11:24 AM
In the long term yes it will be idealy located in VBA. At present I was just trying to build up the formula in cells to see the steps needed to automate all the actions.
I have now put my first step into vba code and attached the file. I just need a little help with my next part which is to id the cell where 569 resides. That is to highlight the cell ready to copy.

Bob Phillips
12-15-2010, 11:33 AM
Sub Macro2()
Dim cell As Range

Range("E4").Value = Mid$(Range("B4").Value, 4, 3)
For Each cell In Range("I4:J4")

If InStr(cell.Value, Range("E4").Text) > 0 Then

MsgBox cell.Address
Exit For
End If
Next cell

End Sub

Gil
12-15-2010, 12:12 PM
Thank you
That was a great reply but instead of the message box can it not just select the cell.
Many thanks

Bob Phillips
12-15-2010, 12:52 PM
Sure



cell.Select

Gil
12-15-2010, 01:15 PM
Many thanks XLD. My task has been resolved.

Cheers Gil