CODEWITHSUNDEEP
excelvba
user1574185
user1574185

Reputation: 51

error reading select cases - excel vba

I am using the following code below and I can not get the following this statement to work:

If UCase(Left(Dn, 2)) = "LM" Then
    Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 3.5

the code will not execute anything from the above statement. 

Case "L"


 **If UCase(Left(Dn, 2)) = "LM" Then
 If IsNumeric(Mid(Dn, 2, 1)) And IsNumeric(Mid(Dn, 3, 1)) Then
            If Mid(Dn, 3, 1) = "0" Then
            Dn.Offset(, 1) = Mid(Dn, 3, 3) + 3.5
           Else
            Dn.Offset(, 1) = Mid(Dn, 3, 3) + 0.35
           End If
            End If

    ElseIf IsNumeric(Mid(Dn, 4, 1)) And IsNumeric(Mid(Dn, 5, 1)) Then
        If Mid(Dn, 5, 1) = "0" And Mid(Dn, 4, 1) = "0" Then
        If Dn.Offset(, 3) = "CMBS" Then
             Dn.Offset(, 1) = Mid(Dn, 3, 3) + 20
        Else
        Dn.Offset(, 1) = Mid(Dn, 3, 3) + 2
        End If

        ElseIf UCase(Left(Dn, 6)) Like "LM###S" Then

        Dn.Offset(, 1) = Mid(Dn, 3, 3) + 2
        Else
        Dn.Offset(, 1) = Mid(Dn, 3, 3) + 0.2
        End If**



    ElseIf UCase(Left(Dn, 3)) = "L/M" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 3.5
    ElseIf UCase(Left(Dn, 3)) = "LM$" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 3.5
    ElseIf UCase(Left(Dn, 2)) = "L$" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 3, 2)) + 2
    ElseIf UCase(Left(Dn, 3)) = "LO-" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
    ElseIf UCase(Left(Dn, 6)) = "LO MID" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 7, 3)) + 3.5
    ElseIf UCase(Left(Dn, 3)) = "LO " Then
        Dn.Offset(, 1) = Val(Mid(Dn, 4, 2)) + 2
    ElseIf UCase(Left(Dn, 5)) Like "L###H" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 0.2
    ElseIf UCase(Left(Dn, 4)) Like "L##H" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 2, 2)) + 0.2
    ElseIf UCase(Left(Dn, 4)) Like "L#" Then
        Dn.Offset(, 1) = Val(Mid(Dn, 2, 2)) + 0.2
    ElseIf IsNumeric(Mid(Dn, 3, 1)) And IsNumeric(Mid(Dn, 4, 1)) Then
        If Mid(Dn, 4, 1) = "0" And Mid(Dn, 3, 1) = "0" Then
            If Dn.Offset(, 3) = "CMBS" Then
                Dn.Offset(, 1) = Mid(Dn, 2, 3) + 20
            Else
                Dn.Offset(, 1) = Mid(Dn, 2, 3) + 2
            End If
        ElseIf UCase(Left(Dn, 5)) Like "L###S" Then
            Dn.Offset(, 1) = Mid(Dn, 2, 3) + 2
        Else
            Dn.Offset(, 1) = Mid(Dn, 2, 3) + 0.2
        End If
    ElseIf IsNumeric(Mid(Dn, 3, 1)) And IsNumeric(Mid(Dn, 4, 1)) Then
        If Mid(Dn, 4, 1) = "0" And Mid(Dn, 3, 1) <> "0" Then
            Dn.Offset(, 1) = Mid(Dn, 2, 3) + 0.2
        End If
    ElseIf UCase(Left(Dn, 3)) Like "L##" Then
        If IsNumeric(Mid(Dn, 2, 1)) Then
            If Mid(Dn, 3, 1) = "0" Then
                Dn.Offset(, 1) = Mid(Dn, 2, 2) + 2
            Else
                Dn.Offset(, 1) = Mid(Dn, 2, 2) + 0.2
            End If
        End If
    End If

input value -->
lm10, LM120S, LM120 desired output -->
13.5 123.5 120.35

Any help with this issue would be very much appreciated.

Upvotes: 0

Views: 175

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Paste this code in a module

Public Function GetNumb(strString As String) As Double
    Dim strLen As Long, i As Long
    Dim Temp As String

    strLen = Len(strString): Temp = ""

    For i = 1 To strLen
        If (Mid(strString, i, 1) = "-") Then
            Temp = Temp & Mid(strString, i, 1)
        End If
        If (Mid(strString, i, 1) = ".") Then
            Temp = Temp & Mid(strString, i, 1)
        End If
        If (IsNumeric(Mid(strString, i, 1))) = True Then
            Temp = Temp & Mid(strString, i, 1)
        End If
    Next i

    If Len(Temp) = 0 Then
        GetNumb = 0
    Else
        GetNumb = CDbl(Trim(Temp))
    End If
End Function

and then change your code

Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 3.5

to

Dn.Offset(, 1) = GetNumb(Dn.Value) + 3.5

Explanation: The function GetNumb extracts numbers from the string so

  1. lm10 ~~> 10
  2. LM120S ~~> 120
  3. LM120 ~~> 120

Upvotes: 1

SeanC
SeanC

Reputation: 15923

the Val(Mid(Dn, 2, 3)) evaluates to zero: M10 starts with a letter, so ends the val process. That should be Val(Mid(Dn, 3, 2))

I am assuming your select statement is Select Case UCase(Left(dn, 1)). if you are not UCaseing the text, then it will not execute any code in the case statement

Upvotes: 1

Dave Sexton
Dave Sexton

Reputation: 11188

Put some Debug.Print statements around your IF line to see what is going on, like so:

Debug,Print Dn
If UCase(Left(Dn, 2)) = "LM" Then
    Debug,Print "hi"
    Dn.Offset(, 1) = Val(Mid(Dn, 2, 3)) + 3.5

What is the Case "L" on the first line doing? Is your code being block from ever reaching the IF statement?

Upvotes: 0

Related Questions