Macro to find cell, copy and paste the last 4 numerical values into new sheet

[dmott92]

Hey guys,

I want to create a macro that will find all specific text, copy the last 4 numerical values and paste this into a new sheet.

I get data sets that will contain mutliple cells with the string "Manders' M1 (Above zero intensity of Ch2), X.XXX". I am specifically interested in the numerical value at the end and just want to find all of these and paste them into a new sheet.

I have been playing around trying to get it to work with no luck so far. I imagine that the macro would find and cells with the text "Manders' M1" and copy+paste the right 5 values into a new sheet.

Thanks in advance,
Dan

 

[tonyyy]

Dan,

Welcome to the Board.

You might consider the following...

Sub AnotherInStringArr()
'1058421
Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet
Dim arr As Variant
Dim i As Long, j As Long, k As Long

Set ws1 = ActiveSheet
Set ws2 = Sheets.Add(after:=Sheets(Sheets.Count))
arr = ws1.UsedRange
k = 1

For i = LBound(arr) To UBound(arr)
    For j = 1 To ws1.UsedRange.Columns.Count
        If InStr(arr(i, j), "Manders' M1") > 0 Then
            With ws2.Cells(k, 1)
                .Value = Right(arr(i, j), 5)
                .NumberFormat = "0.000"
            End With
            k = k + 1
        End If
    Next j
Next i
End Sub

Cheers,

tonyyy