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>
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:
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)
My actual out put should be split and extracted to new Wrkbk2 with 3rd adjacent cel being name of the source workbook:
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.
June 22, 2009 11:33 PM
June 23, 2009 12:45 PM