Pull out all search instances and split code

[Closed]
Report
Posts
2
Registration date
Monday June 22, 2009
Status
Member
Last seen
June 23, 2009
-
Posts
2
Registration date
Monday June 22, 2009
Status
Member
Last seen
June 23, 2009
-
Hello,
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.
Verify:
1. Effective Date
2. Action Type
3. Action Reason
4. Organisational Assignment
5. Last Working Day
6. Eligible for Rehire

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. WrkBk1
Effective Date WrkBk1
Action Type WrkBk1
Action Reason WrkBk1

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

Quick responses are highly appreciated.

1 reply

Posts
2
Registration date
Monday June 22, 2009
Status
Member
Last seen
June 23, 2009

Dear Friends, (Revised post)

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.

[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.