none
complicated extraction vba excel function. RRS feed

  • Question

  • I am currently a novice at vba and i am using excel 2010. I require some assistance in adding this new condition to the following code below:

    new condition:

    (input) m89s --> (output) 89.35
    (input) m80s --> (output) 83.5
    (input) m89 --> (output) 89.35

    my vba code in excel:

    Sub MG14Aug19()Dim Rng     As Range
    Dim Dn      As Range
    Dim n       As Integer
    Dim Num     As String
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    Columns("B:B").ClearContents
    For Each Dn In Rng
        If Not Dn.Offset(, 2) = "BP" Then
            For n = 1 To Len(Dn)
                If IsNumeric(Mid(Dn, n, 1)) Or Mid(Dn, n, 1) = Chr(46) Then
                    Num = Num & Mid(Dn, n, 1)
                End If
            Next n
        
        If IsNumeric(Left(Dn, 1)) Or Len(Num) = Len(Dn) Then
       
        Dn.Offset(, 1) = Num
            ''''
        ElseIf Num = "" Then
            Select Case Dn
                Case "VLteens": Dn.Offset(, 1) = 13
                Case "Mhteens": Dn.Offset(, 1) = 17
                Case "Mteens": Dn.Offset(, 1) = 16
                Case "Ldoubles": Dn.Offset(, 1) = 11
                Case "Mdoubles": Dn.Offset(, 1) = 10.5
            End Select
        Else
            Select Case UCase(Left(Dn, 1))
            Case "V"
                If UCase(Left(Dn, 2)) = "VL" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 9
                ElseIf UCase(Left(Dn, 2)) = "VH" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 2
                 End If
           
            Case "H": Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 8
            Case "L"
             If UCase(Left(Dn, 3)) = "LM" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 3, 2)) + 3.5
             ElseIf UCase(Left(Dn, 3)) = "L/M" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
             ElseIf UCase(Left(Dn, 3)) = "LOW" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
             ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                    Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 2)) + 2, Val(Mid(Dn, 2, 1)) + 0.2)
             End If
            Case "M"
             If UCase(Left(Dn, 2)) = "MH" Then
                    Dn.Offset(, 1) = Val(Mid(Dn, 3, 3)) + 7
             ElseIf IsNumeric(Mid(Dn, 2, 1)) Then
                    Dn.Offset(, 1) = IIf(IsNumeric(Mid(Dn, 2, 1) + Mid(Dn, 3, 1)), Val(Mid(Dn, 2, 2)) + 2, Val(Mid(Dn, 2, 1)) + 0.35)
             Else
                    Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 5
             
             End If
             
    End Select
     End If
     If InStr(Dn, "-") Then
           Dn.Offset(, 1) = Split(Dn, "-")(0)
    End If
    Num = ""
    End If
    Next Dn
    End Sub

    If anyone can help me to add these condition in my original code above, i would be very grateful.

    Thank you for your time and help.

    Much appreciated. 


    • Edited by chippy786 Thursday, August 23, 2012 3:29 PM
    Thursday, August 23, 2012 3:17 PM

All replies

  • Provide us some sample data where you applied the sub.Or better f you can upload a xl file with sample.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 24, 2012 2:28 PM
    Answerer