Pull out all search instances and split code

gotovamsee

New Member
Joined
Jun 22, 2009
Messages
14
Dear Friends,

I have created the following code to search entire C Column from WrkBk1 with "<*>" string so as to extract all the matched words into Wrkbk2.

for eg a single cell data in Col-C is given below maintaining similar pattern in other cells too. There may be cells without <*> pattern data also.

[Cell C1 Data]

Enter Pers No. <pernr>
Verify:
1. Effective Date <effdate>
2. Action Type <actiontype>
3. Action Reason <actreason>
4. Organisational Assignment <orgassignment>
5. Last Working Day <lastwrkgday>
6. Eligible for Rehire <Eligrehire>

Now my code below is pulling full para between first and last "<" ">" instead of all the words in <>.

Here it is below:

Sub GetFullName()
Dim str1 As String, str2 As String, rng As Range, beginPos As Integer, endPos As Integer
str1 = InputBox("String start?")
str2 = InputBox("String end?")
For Each rng In Range("A1", Range("A1").End(xlDown))
beginPos = InStr(1, rng.Value, str1)
On Error Resume Next
endPos = InStr(beginPos, rng.Value, str2)
On Error GoTo 0
If endPos = 0 Then endPos = Len(rng.Value) + 1
If InStr(1, rng.Value, str1) > 0 Then
rng.Offset(0, 1) = Mid(rng.Value, beginPos, endPos)
End If
Next rng
End Sub


My actual out put should be split and extracted to new Wrkbk2 with 3rd adjacent cel being name of the source workbook:

For Eg:

A B C
Pers No. <pernr> WrkBk1
Effective Date <effdate> WrkBk1
Action Type <actiontype> WrkBk1
Action Reason <actreason> WrkBk1

Any VB/Macro experts please suggest some code change so that it works as desired.

Quick responses are highly appreciated.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

gotovamsee

New Member
Joined
Jun 22, 2009
Messages
14
Sorry, Dont know why the Cell Content posted wrong here. Herer is the the actual cell content

[Cell C1 Data]

Enter Pers No. <pernr>
Verify:
1. Effective Date <effdate>
2. Action Type <actiontype>
3. Action Reason <actreason>
4. Organisational Assignment <orgassignment>
5. Last Working Day <lastwrkgday>
6. Eligible for Rehire <Eligrehire>


And the desired output should be

A.....................B....................C
Pers No. <pernr> WrkBk1
Effective Date <effdate> WrkBk1
Action Type <actiontype> WrkBk1
Action Reason <actreason> WrkBk1
 

gotovamsee

New Member
Joined
Jun 22, 2009
Messages
14
The data is not being displayed here as desired. All that kept in <> are missing.

So once again posting my input and output requirements with inverted comas for <> values.

[Cell C1 Data]

Enter Pers No. "<pernr>"
Verify:
1. Effective Date "<effdate> "
2. Action Type "<actiontype>"
3. Action Reason "<actreason>"
4. Organisational Assignment "<orgassignment>"
5. Last Working Day "<lastwrkgday>"
6. Eligible for Rehire "<Eligrehire>"

Output:

A.......... B............. C
Pers No....................."<pernr>"......... WrkBk1
Effective Date............"<effdate>"........ WrkBk1
Action Type................"<actiontype>".... WrkBk1
Action Reason............. "<actreason>"........ WrkBk1
 

Watch MrExcel Video

Forum statistics

Threads
1,099,109
Messages
5,466,716
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top