• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Searching for a string and move to different sheets

xboon_95

New Member
Hi all,

I'm trying to import a .txt file to Excel and the program will filter a string called "¯ Starting Leak Checking(negative pressure)..." in column A only, and once it is found, the program will move this string onwards to the next sheet. For instance, the data is shown below :

¯ Starting Leak Checking(negative pressure)...

00:00:00 00:00:01 L -86 1 115
00:00:00 00:00:02 L -80 2 123
00:00:00 00:00:03 L -90 2 122
00:00:00 00:00:04 O -98 2 156
00:00:00 00:00:05 P -104 2 135
00:00:00 00:00:06 L 5 1 453
00:00:00 00:00:07 L 10 2 123

¯ Starting Leak Checking(negative pressure)...

00:00:01 00:00:01 L -87 1 117
00:00:02 00:00:02 P -86 1 154
00:00:03 00:00:03 O -91 2 167
00:00:04 00:00:04 O -98 2 156
00:00:05 00:00:05 P -104 2 135
00:00:06 00:00:06 L 7 1 453
00:00:07 00:00:07 L 10 2 127

¯ Starting Leak Checking(negative pressure)...
00:00:08 00:00:01 L -87 1 117
00:00:09 00:00:02 P -86 1 154
00:00:10 00:00:03 O -91 2 167
00:00:11 00:00:04 O -98 2 156
00:00:12 00:00:05 P -104 2 135
00:00:13 00:00:06 L 7 1 453
00:00:14 00:00:07 L 10 2 127

¯ Starting Leak Checking(negative pressure)...
00:00:15 00:00:01 L -87 1 117
00:00:16 00:00:02 P -86 1 154
00:00:17 00:00:03 O -91 2 167
00:00:18 00:00:04 O -98 2 156
00:00:19 00:00:05 P -104 2 135
00:00:20 00:00:06 L 7 1 453
00:00:21 00:00:07 L 10 2 127

Since there are 4 strings of "¯ Starting Leak Checking(negative pressure)...", what I'm trying to achieve now is to move these data into 4 different sheets, with the first string of "¯ Starting Leak Checking(negative pressure)..." and it's data in the first sheet, second string of "¯ Starting Leak Checking(negative pressure)..." in the second sheet and ultimately, there will be 4 different sheets. Is it possible to do this? Any help will be appreciated. I tried the code below but it doesn't work.

Dim c As Range, i As Long
Dim firstAddress As String
i = 1
With Range("A1:Q18215")
Set c = .Find("Dim c As Range, i As Long Dim firstAddress As String
i = 1
With Range("A1:Q18215")
Set c = .Find("» Starting Leak Checking(negative pressure)...")
If Not c Is Nothing Then
firstAddress = c.Address
Do
.Range(c, c.End(xlDown)).Copy Sheets("sheet" & i).Range("A1:Q18215")
i = i + 1
Set c = .FindNext(c)
Loop Until c.Address = firstAddress
End If
End With")
If Not c Is Nothing Then
firstAddress = c.Address
Do
.Range(c, c.End(xlDown)).Copy Sheets("sheet" & i).Range("A1:Q18215")
i = i + 1
Set c = .FindNext(c)
Loop Until c.Address = firstAddress
End If
End With
 
Hi,

A user from other forums has already provided me with the codes, but now I'm facing another problem, which is the naming of the sheets. For all my calculation codes, I only indicated "Sheet1" as my desired sheet. In this case, since the data is separated into different sheets, the naming of the sheets will be different. How do I solve this problem? Since I can't standardize the name of the sheets. Hope that you can help me with this. His codes is shown below :

Dim keyPhrase As String
Dim topCell As Range, bottomCell As Range
Dim dataCells As Variant
Dim sourceSheet As Worksheet

Set sourceSheet = Sheet1
keyPhrase = "¯ Starting Leak Checking(negative pressure)..."
Application.ScreenUpdating = False

With sourceSheet.Columns(1)
Set bottomCell = .Cells(.Rows.Count, 1).End(xlUp)
Set topCell = .Find(keyPhrase, After:=bottomCell, SearchDirection:=xlPrevious, LookAt:=xlWhole)

Do Until (topCell Is Nothing)
If (bottomCell.Row < topCell.Row) Then Exit Do

With .Parent.Parent
With .Worksheets.Add(after:=.Sheets(.Sheets.Count), Type:=xlWorksheet)
Range(topCell, bottomCell).EntireRow.Copy Destination:=.Range("A1")
End With
End With

topCell.Value = keyPhrase & "."

Set bottomCell = topCell.End(xlUp)
Set topCell = .Find(keyPhrase, After:=bottomCell, SearchDirection:=xlPrevious, LookAt:=xlWhole)
Loop
End With

Application.ScreenUpdating = True
 

Attachments

  • 123.txt
    1 KB · Views: 2
Back
Top