Need vba to solve select criteria get SR no after # from desc and end with result if blank

Dear experts

I have month end data which contains multiple columns range A:G

Criteria is: in B:B column value apart from CSV File & Process by others select rest of all then column E:E select only blanks & N/A# then in column D:D see some description part exist with long string which some values comes with number sign # after # there is digits values is SR NO should be retrieve from description and fill the constrain rows which are not found SR no in description for blank cells of E:E column ,fill result like "SR No Not found"

Easy understand:
B:B select all apart from CSV File & Process by others then
E:E column select only Blank and n/A then
D:D in description part contains # after some digits values is SR No copy to E:E on consistent rows
G:G what are rows still blank in B:B towards G:G fill the text "SR No Not found"

find the attachment

Hi Julielara

This Code in the attached will work on your Sample Data (report dec) but there are many inconsistencies in the Data. It will probably need to be modified to accommodate any additional unknown inconsistencies.

CTRL + x will fire the Code.

Option Explicit

Sub SR_Numbers()
   Dim ws           As Worksheet
   Dim Rng          As Range
   Dim Cel          As Range
   Dim LR           As Long

   Application.ScreenUpdating = False
   Set ws = Sheets("report dec")
   With ws
      If Not .AutoFilterMode Then
         .Rows("1:1").AutoFilter
      End If
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      .Range("A1:G" & LR).AutoFilter Field:=2, Criteria1:= _
                                     "<>CSV File", Operator:=xlAnd, Criteria2:="<>Processed by Others"
      .Range("A1:G" & LR).AutoFilter Field:=5, Criteria1:="=NA", _
                                     Operator:=xlOr, Criteria2:="="
      Set Rng = .Range(.Cells(2, "D"), .Cells(LR, "D")).SpecialCells(xlCellTypeVisible)
      For Each Cel In Rng
         On Error Resume Next
         Cel.Offset(0, 1) = Split(Cel.Value, "#")(1)
         On Error GoTo 0

         On Error Resume Next
         Cel.Offset(0, 1) = Split(Cel.Offset(0, 1).Value, "-")(0)
         On Error GoTo 0

         On Error Resume Next
         Cel.Offset(0, 1) = Split(Cel.Offset(0, 1).Value, " ")(1)
         On Error GoTo 0

         If InStr(Cel.Offset(0, 1).Text, "_") > 0 Then
            Cel.Offset(0, 1) = Split(Cel.Offset(0, 1).Value, "_")(0)
         End If

         If Cel.Offset(0, 1).Value = "" Then
            Cel.Offset(0, 3).Value = "SR No Not found"
         Else
            Cel.Offset(0, 1).Value = "# " & Cel.Offset(0, 1).Value
         End If
      Next Cel
      .AutoFilterMode = False
   End With
   Application.ScreenUpdating = True
End Sub

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.