CODEWITHSUNDEEP
vbaexcel
Ramon
Ramon

Reputation: 3

VBA Macro (Excel 2010): Runtime Error 5

I've made a small macro to remove several unwanted characters in a long list of reports. I wanted to remove the HTML tags from a string and ran into an unexpected runtime error.

I've been having this weird runtime error on:

strMatter = Mid(str2010, 1, Len(str2010) - 10)

Whereas something similar does work:

b = Mid(a, 1, Len(a) - 10)

The difference is that the line above is run in a "For To Loop". Logically I've tried several things to work around the issue and it seems that I'm not allowed to decrease a variable whilst in the loop. How can I work around this?

The B column before any macro's are run: strPlaceholder

The B column after the macro has run: str2010

Every help and/or suggestions are more than welcome!

Edit:

I've changed For intRow = 2 To 5000

To For intRow = 2 To 13

And everything works. How do I alter the script in such a way that it finds the last row?

Here's the entire little code:

    Sub WHYYYY()

Dim strPlaceholder As String
Dim strPhrase As String
Dim str2010 As String
Dim strMatter As String
Dim strStart As String
Dim strEnd As String
Dim intRow As Integer
Dim intChar As Integer
Dim intLen As Integer

For intRow = 2 To 5000

    'Clean Description Column
    strPlaceholder = Cells(intRow, 2).Value
    If Not Trim(Len(strPlaceholder)) = 0 Then
        strPhrase = strPlaceholder
        str2010 = Mid(strPhrase, 63, Len(strPhrase))
        strMatter = Mid(str2010, 1, Len(str2010) - 10)
        Cells(intRow, 2) = str2010  'Should be = strMatter
    End If

    'Clean Legal Responsible Column
    strStart = Cells(intRow, 7).Value
    intLen = Len(strStart)

    For intChar = 1 To intLen
        If Not IsNumeric(Mid(strStart, intChar, 1)) Then
            strEnd = strEnd & Mid(strStart, intChar, 1)
        End If
        Cells(intRow, 7) = strEnd
    Next

    Cells(intRow, 7) = Mid(strEnd, 3, 50)
    strEnd = ""

    'Clean Business Responsible Column
    strStart = Cells(intRow, 8).Value
    intLen = Len(strStart)

    For intChar = 1 To intLen
        If Not IsNumeric(Mid(strStart, intChar, 1)) Then
            strEnd = strEnd & Mid(strStart, intChar, 1)
        End If
        Cells(intRow, 8) = strEnd
    Next

    Cells(intRow, 8) = Mid(strEnd, 3, 50)
    strEnd = ""

Next

End Sub

Upvotes: 0

Views: 115

Answers (1)

user3598756
user3598756

Reputation: 29421

I can't understand whether the issue at this time is still the one in the question, which still reads:

I've been having this weird runtime error on:

strMatter = Mid(str2010, 1, Len(str2010) - 10)

Whereas something similar does work:

b = Mid(a, 1, Len(a) - 10)

should it still be then you could change "strategy" like follows

Dim cell As Range

With Worksheets("MySheet") '<~~ replace "MySheet" with your actual sheet name
    With Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues)
        .Replace What:="*><p>", Replacement:="", LookAt:=xlPart, MatchCase:=False
        .Replace What:="</p></div>*", Replacement:="", LookAt:=xlPart, MatchCase:=False

        For Each cell In .Cells
            ' do other stuff
        Next cell

    End With
End With

Upvotes: 1

Related Questions