Finding a string and then copy/pasting it 3 rows up and one column to the right

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
I have a log file from equipment at my job that I am trying to organize to make it a more useful to me. I want to look through over 5000 rows in column A and when it finds a cell with the words "BHS ASSOCIATION FULLY AVAILABLE", it cuts it out and then paste it 3 rows up and one column over in B. Basically in column B directly across from the row in column A that has "REPT:CELL" in it.

Here is an example of the output from my log file that I pasted into column A.
[TABLE="width: 538"]
<tbody>[TR]
[TD] 58 REPT:CELL 945 CDM 2, CRC, HEH[/TD]
[/TR]
[TR]
[TD] SUPPRESSED MSGS: 0[/TD]
[/TR]
[TR]
[TD] ERROR TYPE: ONEBTS MODULAR CELL ERROR[/TD]
[/TR]
[TR]
[TD] SET: MLG-BHS ASSOCIATION FULLY AVAILABLE[/TD]
[/TR]
[TR]
[TD] MLG 1 DCS 2, SM 14, PSU 0, BHS 1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] 01 05 02 00 00 01 01 00[/TD]
[/TR]
[TR]
[TD] 01 00 00 00 02 0E 01 00[/TD]
[/TR]
[TR]
[TD] 00 00 00 00 01 10 20 03[/TD]
[/TR]
[TR]
[TD] 00 00 00[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]10/11/18 02:58:41 #239639[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] 59 REPT:CELL 1025 CDM 1, CRC, HEH[/TD]
[/TR]
[TR]
[TD] SUPPRESSED MSGS: 0[/TD]
[/TR]
[TR]
[TD] ERROR TYPE: ONEBTS MODULAR CELL ERROR[/TD]
[/TR]
[TR]
[TD] SET: MLG-BHS ASSOCIATION FULLY AVAILABLE[/TD]
[/TR]
[TR]
[TD] MLG 1 DCS 2, SM 12, PSU 0, BHS 1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] 01 05 02 00 00 01 01 00[/TD]
[/TR]
[TR]
[TD] 01 00 00 00 02 0C 01 00[/TD]
[/TR]
[TR]
[TD] 00 00 00 00 01 10 10 01[/TD]
[/TR]
[TR]
[TD] 00 00 00[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]10/11/18 02:59:02 #240536[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hello this does as you ask.

Note that I've started searching from row 4 in column A as anything before that can't be moved up 3 rows...

Code:
Sub FindAndMove()
    Dim c As Range
    Dim sFind As String
    
    sFind = "*BHS ASSOCIATION FULLY AVAILABLE*"
    
    For Each c In Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If c Like sFind Then
            c.Cut Cells(c.Row - 3, c.Column + 1)
        End If
    Next c
End Sub
 
Upvote 0
Solution
Here is another option for you to try in a copy of your workbook.

Code:
Sub MoveUpAndOver()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(isnumber(search(""BHS ASSOCIATION FULLY AVAILABLE"",#)),#,"""")", "#", .Offset(3).Address))
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top