VBA: paste as text failed, split needs error handling

[Archangelos]

Hello guys,

I have a small project running and I have faced a few issues.

Firstly, take a look at the first picture.

It's MS word and the data are copied (and pasted as unformatted text) from a website. The data are tab separated therefore it's easy to copy them to Excel and continue with the process. Take a look at the blue coloured characters. In the first line the blue coloured characters say "21657 3/4". That's the flaw of the webpage. The number 21657 and the string 3/4 should have been in different columns. The character in the middle should have been TAB, not space. Well, VBA can do it. However, Excel handles it as a number that constists of the integer number 21657 and the fraction 3/4. When I paste it to Excel it changes from "21657 3/4" to "21657,75".

The obvious solution to that is to format the cells as text in order to maintain "21657 3/4". Well, it does not work. The solution I came up to is to replace the space with a predefined string and then paste the data to Excel.

The second picture shows the result.

The space has been replaced with the string "qqFECsrqq" (in MS Word using VBA). Then, when the time comes for VBA to run in Excel the data of column I split to two different columns. THe code is the following.

Sub qaqSplitSRFEC(destSymbol As Integer, destFEC As Integer, TelikiSeira As Integer)Dim i As Integer
Dim SymbolFEC() As String
Dim qaDebug As Boolean
Dim TempString As String


qaDebug = False


    For i = 1 To TelikiSeira
        If qaDebug = True Then
           MsgBox "i= " & i
        End If
        TempString = Cells(i, SRFECcol)
        Cells(i, SRFECcol) = Replace(TempString, " ", "")
        SymbolFEC = Split(Cells(i, SRFECcol).Value, "qqFECsrqq")
        Cells(i, destSymbol) = SymbolFEC(0)
        Cells(i, destFEC) = SymbolFEC(0 + 1)
    Next i


End Sub

The problem is that some records do not have the integer number and the fraction but only the integer number. In the picture above you can see that the first three records have the value "30000qqFECsrQQ5/6" and as a result of it the code returns two different strings: "30000" and "5/6". However, the next records has only one integer number: "4936" (this is what I got from the website in the first place).

When the code comes across such records I get an error message.

Is there any way to handle the error? Can I tell VBA to ignore it and continue running?

 

[Fluff]

Try

If UBound(SymbolFEC) > 0 Then Cells(i, destFEC) = SymbolFEC(1)

 

[Archangelos]

Thanks buddy, you have been very helpful.

Shortly after I had uploaded my post I came up with an idea: search for the temporary string ("qqFECsrqq") and if it does not exist then add the string "qqFECsrqqN/A". The split worked fine and if the fraction style number did not exist the second cell would take the value N/A (meaning Not Available).
The following code shows what I did initially.

        'The old way to prevent the code from halting if there was no FEC                  If InStr(Cells(i, SRFECcol), "qqFECsrqq") = 0 Then
                     Cells(i, SRFECcol) = Cells(i, SRFECcol) & "qqFECsrqqN/A"
                  End If

After I saw your answer I changed my code.

        SymbolFEC = Split(Cells(i, SRFECcol).Value, "qqFECsrqq")       
        Cells(i, destSymbol) = SymbolFEC(0)
        
        If UBound(SymbolFEC) > 0 Then
           Cells(i, destFEC) = SymbolFEC(0 + 1)
        Else
           Cells(i, destFEC) = "N/A"
        End If

Thanks again for your help.

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[Archangelos]

Eureka!


I found it!

I understand why this text thing did not work!


In the past, I pasted it from Notepad and worked. The last days, I pasted from MS Word and it seems that the pasted data «carried» some kind of formatting.

Once I did a paste special in Excel (instead of an ordinary paste) the blue letters arrives as text.

Of course, the split issue remains but I do not have to replace the space with the qqFECsrqq string.

PS: http://en.wikipedia.org/wiki/Eureka