Error runtime 13 export a selected column to text file

[RaidHack]

HI
Can you help me?
This macro give me a error runtime 13 mismatch when the loop arrive to a blank cell
how can fix it?
i'm sorry for my english

Sub saveText2()
    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = ThisWorkbook.Path & "\Command.txt"
    
    Open filename For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    Set myrng = Range("M4:M120")
    




    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
        Next j
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , lineText
    Next i
    
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
 End Sub

 

[Domenic]

Hi, and welcome to MrExcel.

I don't think that a blank cell will cause an error. It's likely something else causing it. Maybe some sort of error value, such as #N/A, #DIV/0!, #NUM!, etc? Try running the macro again. This time, when the error occurs, click on Debug. This will highlight the line causing the error in yellow, which most likely will be this line...

[COLOR=#333333]lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)[/COLOR]

If so, place your cursor over myrng.Cells(i, j) . What does it display?

By the way, your English is fine.

 

[RaidHack]

Hi, and welcome to MrExcel.

I don't think that a blank cell will cause an error. It's likely something else causing it. Maybe some sort of error value, such as #N/A, #DIV/0!, #NUM!, etc? Try running the macro again. This time, when the error occurs, click on Debug. This will highlight the line causing the error in yellow, which most likely will be this line...

[COLOR=#333333]lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)[/COLOR]

If so, place your cursor over myrng.Cells(i, j) . What does it display?

By the way, your English is fine.

ok,
i=24 and j=1
but i just fixed it.
can i ask you a thing?

Sub Command()On Error Resume Next
Dim filename As String, lineText As String
Dim myrng As Range, i, j
filename = ThisWorkbook.Path & "\Command.txt"
Open filename For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
Set myrng = Range("M4:M160")
finalrow = Cells(Rows.Count, "M").End(xlUp).Row
For i = 1 To myrng.Rows.Count
     For j = 1 To myrng.Columns.Count
     If myrng.Cells(i, j) = "Errore 2007" Then
    GoTo ritorno
     End If
            lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)


Next j
lineText = StrConv(lineText, vbUnicode)
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , lineText
ritorno:
Next i
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

how can i say to Macro to create a unicode text file?
i use a symbol which become "???"

 

[Domenic]

Have a look here...

https://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba

 

[RaidHack]

didnt work

 

[Domenic]

Can you post the code that you tried?

 

[RaidHack]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

Sub Command()On Error Resume Next
Dim filename As String, lineText As String
Dim myrng As Range, i, j
filename = ThisWorkbook.Path & "\Command.txt"
filename.Charset = "utf-8"

Open filename For Output As #1
Set myrng = Range("M4:M160")
finalrow = Cells(Rows.Count, "M").End(xlUp).Row
For i = 1 To myrng.Rows.Count
For j = 1 To myrng.Columns.Count
If myrng.Cells(i, j) = "Errore 2007" Then
GoTo ritorno
End If
lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)


Next j
lineText = StrConv(lineText, vbUnicode)
Print #1 , lineText
ritorno:
Next i
Close #1
End Sub</code></pre>

 

[RaidHack]

is it corretct??
i and visual basic arent best friends, not yet

 

[Domenic]

Try the following...

Option Explicit

Sub Command()


    Dim stream As Object
    Dim outFileName As String
    Dim sourceRange As Range
    Dim currentValue As Variant
    Dim lineText As String
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    
    outFileName = ThisWorkbook.Path & "\Command.txt"
    
    lastRow = Cells(Rows.Count, "M").End(xlUp).Row
    
    Set sourceRange = Range("M4:M" & lastRow)
    
    lineText = ""
    For i = 1 To sourceRange.Rows.Count
        For j = 1 To sourceRange.Columns.Count
            currentValue = sourceRange(i, j).Value
            If Not IsError(currentValue) Then
                lineText = lineText & IIf(j > 1, ",", "") & currentValue
            End If
        Next j
        lineText = lineText & vbCrLf
    Next i
    
    Set stream = CreateObject("ADODB.Stream")
    
    With stream
        .Charset = "UTF-8"
        .Mode = 3 'adModeReadWrite
        .Type = 2 'adTypeText
        .Open
        .WriteText lineText
        .SaveToFile outFileName, 2 '1=adSaveCreateNotExist, 2=adSaveCreateOverWrite
        .Close
    End With
    
End Sub