IIF replacement code

[rjplante]

I have a code that runs fine on Excel 2016 but crashes miserably on 2010. This is the line that fails.

lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)

The entire macro is listed below. I need to know how to convert this to a standard If-Then-Else statement so that it will run on both 2016 and 2010.

Sub TEXTFILE_CREATE()


Application.ScreenUpdating = False


Dim fPath As String
Dim fName As String
Dim saveName As String
Dim lineText As String
Dim myrng As Range, i, j


Sheets("Script builder").Visible = True


Sheets("Script builder").Range("A14").Value = Sheets("Email Data Dump").Range("E2").Value


If Sheets("Operations").Range("B1").Value = "" Then
    MsgBox "Please select a location to save this file."
    Call GetFolder_FDN
Else
 i = MsgBox("The text file will be saved in the following location: " & Sheets("Operations").Range("B1").Value _
    & vbCrLf & "Would you like to update the destination for this file?", vbYesNo + vbExclamation + vbDefaultButton2)
    If i = 7 Then 'NO
        fPath = Sheets("Operations").Range("B1").Value
        fName = Sheets("Operations").Range("B3").Value & ".txt"


        saveName = fPath & fName
        Open saveName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
 
        Set myrng = Sheets("Script builder").Range("A1:A30")
    
        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] 


        Sheets("Email Data Dump").Activate
    
        Application.ScreenUpdating = True


        Call NewMailMessage
        
        Sheets("Email Data Dump").Activate
        Sheets("Script builder").Visible = False
        
        Application.ScreenUpdating = True
        
    ElseIf i = 6 Then 'YES
        Sheets("Operations").Visible = True
        Sheets("Operations").Activate
        Sheets("Operations").Range("E1").Value = "YES"
        Call GetFolder_FDN
        Application.ScreenUpdating = True
    End If
    
End If
Application.ScreenUpdating = True


End Sub

Thanks for all the help on this one. I am at a loss on how to repair this little issue.

Robert

 

[Joe4]

Try:

If j=1 Then
    lineText = myrng.Cells(i, j)
Else
    lineText = lineText & "," & myrng.Cells(i, j)
End If

 

[rjplante]

I have changed my code to match what you suggested and it works on my machine running Excel 2016, however it fails on the end users machine running Excel 2010. It fails with a Run-time error '13': Type Mismatch on the line indicated below.

[COLOR=#333333]lineText = myrng.Cells(i, j)

[/COLOR]

What could be the problem?

 

[Joe4]

What is the value of j when the error occurs?
Check to see what is in that cell.