Vba find "-" by using seach function and extract only numbers fix errors

Question

Could you please help me on below error in vba. Formula works but there are error. My scenario is need to extract in between number (between two "-")

Ite1-223466678-ghtrdhjuyr321
Ite-654354477-hjuyt-
Dftehh-767678765-4yutiuy

Extract only this 9 digit in between "-" and need to apply till last row of my excel through VBA

Formula:

= Value(mid(A1,search("-",A1)+1, search ("-",A1, search ("-", A1)+1-search("-", A1)-1))

VBA:

Sub Data2

Dim lastrow as long 

Lastrow=Range("A"&rows.count).end(xlup).row
Range("F2:F" & lastrow).formula = " = 
Value(mid(A1,search("-",A1)+1, search 
("-",A1, search ("-", A1)+1- 
 search("-.   ", A1)-1)"

End sub

ERROR

#VALUE ERROR need to fix am not sure how to do if search fails in formula
RUNTIME ERROR 13 data mismatch

Gary's Student · Accepted Answer · 2021-09-04T14:18:12.323

1

Give this a try:

Sub BetweenDashs()
    Dim lastrow As Long, i As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastrow
        arr = Split(Range("A" & i).Value, "-")
        If UBound(arr) > 0 Then
            Range("F" & i).Value = CLng(arr(1))
        End If
    Next i
End Sub

EDIT#1:

If your data is in column C rather than column A, then use:

Sub BetweenDashs()
    Dim lastrow As Long, i As Long
    lastrow = Range("C" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastrow
        arr = Split(Range("C" & i).Value, "-")
        If UBound(arr) > 0 Then
            Range("F" & i).Value = CLng(arr(1))
        End If
    Next i
End Sub

edited Sep 04 '21 at 14:18

answered Sep 04 '21 at 12:50

Gary's Student

95,722
10
59
99

No luck still same error run time error 13.... Column C I have data .... I will do output in column F – Rahul2020 Sep 04 '21 at 13:29
@Rahul2020 See my **EDIT#1** – Gary's Student Sep 04 '21 at 14:18
Yes I tried this one also still same error run time error – Rahul2020 Sep 04 '21 at 14:21
1)It will run for few lines and error throughs because of there is only one time "-" .... In this case can we add some msg to user to check 2) I noticed if there no "-" in text getting same error ... Run time error – Rahul2020 Sep 04 '21 at 14:33
I believe is there anything missing in condition .... I really appreciate your time – Rahul2020 Sep 04 '21 at 14:39

Vba find "-" by using seach function and extract only numbers fix errors

1 Answers1