Pulling substring of numbers into Macro data pull from a specified ID.

[DThib]

Nuance is a bear.
My code needs to look through a string of numbers for a specified text,"(10)" and then extract number for a function to the next "(" usually 7 characters.

I can extract part of the string with Left, Mid, Right functions but I get all the other numbers again.
My code is all jumbled up now trying to solve this.

The cells searched will have the following type of data

[TABLE="width: 356"]
<tbody>[TR]
  [TD="width: 356"](01)813502011371(10)1407038(21)190792(17)210228[/TD]
[/TR]
</tbody>[/TABLE]

The identifier (10) number is what I need to pull out as the code loops through a spreadsheet to find matches and place column information in the appropriate place.

Basically:

BatchP = InStr(1, Cells(I, 1), "(10)")
Sheets("Sheet2").Cells(J, 15) = Sheets("Sheet1").Left(Cells(I, 12), BatchP + 1)

I know it is mucked up, please help!

DThib

 

[Fluff]

Not entirely sure what you are trying to do, but this will extract the number after the (10)

Sub Dthib()
   Dim Sp As Variant, x As Variant
   Sp = Split(Replace(Range("A2"), "(", ")"), ")")
   With Application
     x = .Index(Sp, .Match("10", Sp, 0) + 1)
   End With
   If Not IsError(x) Then Range("B2").Value = x
End Sub

 

[DThib]

Thank Fluff

I am using this as part of a larger code. The entire code is to pull data that matches 2 criteria. If the data is matched then pull a column cell from the same row and place in a cell on another spreadsheet. Extracting data to an other task.

This line will pull the cell that contains my first box above. It is a readout of several components, but I only need the (10) that is from the same row as the match to criteria. This larger code loops through to find all matches.

Does that help?

DThib

 

[Leith Ross]

Hello DThib,

Let's say I = 2 in the macro. What would be the values of the Cells(i, 1) and Sheet1.Cells(I, 12) ?

 

[Fluff]

Thank Fluff

I am using this as part of a larger code. The entire code is to pull data that matches 2 criteria. If the data is matched then pull a column cell from the same row and place in a cell on another spreadsheet. Extracting data to an other task.

This line will pull the cell that contains my first box above. It is a readout of several components, but I only need the (10) that is from the same row as the match to criteria. This larger code loops through to find all matches.

Does that help?

DThib

I'm afraid you've totally lost me.
Please remember that we have no idea what you are trying to do, or what your data looks like.

 

[DThib]

I'm afraid you've totally lost me.
Please remember that we have no idea what you are trying to do, or what your data looks like.

Here is the whole section with the part I am attempting highlighted.

Sub Workie1()


  Dim LastRow, SecondRow As Long
  Dim I As Long
  Dim J As Long, BatchP As Long
    
    
    LastRow = Sheets("MK_DB1").Cells(Rows.Count, "B").End(xlUp).Row
    SecondRow = Sheets("SPR").Cells(Rows.Count, "B").End(xlUp).Row
    
    I = 1 + LastRow
    J = 1 + SecondRow
    For I = 1 To LastRow 'Each i In Sheets("GlobalSPR_Query")
        If Sheets("MK_DB1").Cells(I, 22) = "Knapczyk, Maciej" And Sheets("MK_DB1").Cells(I, 25) <> "" Then
             'Sheets("SPR").Cells(j, 1) = Format(Now(), "DD-MMM-YYYY")
             If Not Sheets("MK_DB1").Cells(I, 2).Value = Sheets("SPR").Cells(J, 1).Value Then
               Sheets("SPR").Cells(J, 1) = Sheets("MK_DB1").Cells(I, 2).Value
             End If 'SPR
             If Sheets("MK_DB1").Cells(I, 8).Value = "" Then
              Sheets("SPR").Cells(J, 2) = Sheets("MK_DB1").Cells(I, 9).Value
             Else
               Sheets("SPR").Cells(J, 2) = Sheets("MK_DB1").Cells(I, 8).Value 'AIC
             End If
             Sheets("SPR").Cells(J, 3) = Sheets("MK_DB1").Cells(I, 16).Value 'SW Ver
             Sheets("SPR").Cells(J, 4) = Sheets("MK_DB1").Cells(I, 24).Value 'Date Assigned
             If Sheets("MK_DB1").Cells(I, 5) = 0 Then
               Sheets("SPR").Cells(J, 8) = ""
             Else: Sheets("SPR").Cells(J, 8) = Sheets("MK_DB1").Cells(I, 5)
             End If 'SalesForce
             Sheets("SPR").Cells(J, 5) = Sheets("MK_DB1").Cells(I, 18).Value 'DateSPREntered
             If Sheets("MK_DB1").Cells(I, 10).Value = "" Then
                Sheets("SPR").Cells(J, 14) = Sheets("MK_DB1").Cells(I, 11).Value
             Else
                Sheets("SPR").Cells(J, 14) = Sheets("MK_DB1").Cells(I, 10).Value 'Serial Number
             End If
             Sheets("SPR").Cells(J, 20) = Sheets("MK_DB1").Cells(I, 12).Value 'Component Lot Number
[COLOR=#b22222][B]             BatchP = InStr(1, Cells(I, 1), "(10)")[/B]
[/COLOR][B][COLOR=#b22222]             Sheets("SPR").Cells(J, 15) = Sheets("MK_DB1").Left(Cells(I, 10), BatchP - 1) 'Batch # Left(Cells(I, 10), BatchP + 1)[/COLOR][COLOR=#008000]
[/COLOR][/B][COLOR=#008000]'This is the place where the row cell will have the data. and needs to get pulled in this macro with the rest of the data.[/COLOR]
             Sheets("SPR").Cells(J, 24) = Sheets("MK_DB1").Cells(I, 39).Value 'Date Occurred
             Sheets("SPR").Cells(J, 25) = Sheets("MK_DB1").Cells(I, 31).Value 'Symptom
             Sheets("SPR").Cells(J, 30) = Sheets("MK_DB1").Cells(I, 40).Value 'Submitted by Name
             Sheets("SPR").Cells(J, 18) = Sheets("MK_DB1").Cells(I, 48).Value 'AIC Material Number
             Sheets("SPR").Cells(J, 17) = Sheets("MK_DB1").Cells(I, 49).Value 'Component Lot #
             Sheets("SPR").Cells(J, 34) = Sheets("MK_DB1").Cells(I, 23).Value 'Status
        J = J + 1
        End If
         ' Build arguments for items taken from the feed. 23May2019
    Next I
     


End Sub

 

[Fluff]

That code does not match your OP & is therefore useless.
Please describe (in words) what you are trying to do, remembering that we cannot see your data.

 

[DThib]

Thanks Fluff,

I actually worked your solution into my needs in the macro.

It works great!

Appreciate the advice.

 

[Fluff]

Glad you figured it out & thanks for the feedback