Pasting Strings from an Array into a Sheet Truncates a String

[mchac]

I've written some ugly but largely functional VBA that opens webpages, pulls address data by searching for prefix and suffix characters and extracts the text in between. For the most part it works fine. But for at least one instance the end of the string gets truncated (the city, state and zip are missing) when pasted into the sheet. If I look at the array in the watches window before pasting, I can see that all the text I need is there but when it is pasted the last characters (HENDERSON NV 89012) don't appear.

If I extract the code for extracting the address and point at the specific webpage where I'm having trouble, it looks like this (and works fine).

Sub tester()


    Dim sURL As String, sHTML As String
    Dim oHttp As Object
    Dim ExtractAddress As String


    Set oHttp = CreateObject("MSXML2.XMLHTTP")
                 
    'Go to the needed page on the SEC website
    sURL = "http://www.sec.gov/cgi-bin/browse-edgar?CIK=1496443" & "&Find=Search&owner=exclude&action=getcompany"
     
    oHttp.Open "GET", sURL, False
    oHttp.send
    sHTML = oHttp.responseText
     


    Const PrefixChars As String = ""
    Const SuffixChars As String = ""
    Dim StartPos As Long, EndPos As Long
    StartPos = InStr(sHTML, PrefixChars)
    
    StartPos = StartPos + Len(PrefixChars)
    EndPos = InStr(StartPos, sHTML, SuffixChars) - 1
    ExtractAddress = Mid(sHTML, StartPos, EndPos - StartPos + 1)


    StartPos = InStr(EndPos, sHTML, PrefixChars)
    StartPos = StartPos + Len(PrefixChars)
    EndPos = InStr(StartPos, sHTML, SuffixChars) - 1
    ExtractAddress = ExtractAddress & ", " & Mid(sHTML, StartPos, EndPos - StartPos + 1)


    StartPos = InStr(EndPos, sHTML, PrefixChars)
    StartPos = StartPos + Len(PrefixChars)
    If StartPos < InStr(EndPos, sHTML, "/div") Then
        EndPos = InStr(StartPos, sHTML, SuffixChars) - 1
        ExtractAddress = ExtractAddress & ", " & Mid(sHTML, StartPos, EndPos - StartPos + 1)
    End If


    MsgBox ExtractAddress


End Sub

In the main sub from which the above code was extracted for testing, I call a function to paste the array into a sheet and the function looks like this:

Private Function WriteDataInSheet(DATA_ARRAY, SHEET)


    Dim OUTPUTRANGE As Range
    Dim LAST_ROW_NUMBER As Long
    Dim LAST_COLUMN_NUMBER As Long
    Dim LAST_COLUMN_LETTER As String
    
    'Sheets(SHEET).Cells.Clear
    Sheets(SHEET).Rows("2:" & Rows.Count).ClearContents


    LAST_ROW_NUMBER = UBound(DATA_ARRAY, 1)
    LAST_COLUMN_NUMBER = UBound(DATA_ARRAY, 2)
    LAST_COLUMN_LETTER = Split(Evaluate("address(1," & LAST_COLUMN_NUMBER & ")"), "$")(1)
    
    Set OUTPUTRANGE = Sheets(SHEET).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER + 1)
    
    OUTPUTRANGE = DATA_ARRAY
    
    Set OUTPUTRANGE = Nothing
    
End Function

I can't figure out how to track down what is going wrong i.e. why the city, state, zip are in the array just prior to pasting into the sheet but are not present once the array is pasted.

Any ideas on how to track down the error is appreciated.

 

[mole999]

in a cell nearby put =LEN(result cell) and see how much is there

which version of excel are you using

 

[mchac]

Thanks mole.
the LEN result is 69.
I'm in Excel 2010, Win7 64bit, 16gb ram.

I was wondering about the length of the string also but that doesnt seem to be the problem.


ahhh, just found it. sorry for the hassle.
there is a carriage return character in there. If i drag the status bar (think that's what it's called) just above the sheet so i can see two lines, the city state zip is there in the second line.

Do you know what the CR character is so I can search and selectively delete before putting the string in the array?

 

[mole999]

about half way down this page https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ for reference

 

[mchac]

thanks very much