Type Mismatch error, but writing to a Text file ...

Question

0.00/5 (No votes)

See more:

I am getting a message "Run-time error '13': Type mismatch ... I realise that this can happen if I am trying to add to a non-numeric or that kind of thing, but here I am just trying to write the value of a Cell to a Text file, so it doesn't matter what it is ... what is wrong ?

VB

Sub SortAndPrepareTables()

    Dim myPath As String
    Dim myTextFile As String
    Dim myTables As String
    Dim myFile As Integer
    Dim myRow As Range
    Dim myCell As Range
    
    Range("A17:N23").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("M18:M23"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("N18:N23"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K18:K23"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A17:N23")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    myPath = Application.ActiveWorkbook.Path
    myTextFile = "League Cup Tables.txt"
    myTables = myPath & "\" & myTextFile
    myFile = FreeFile()

    Open myTables For Output As myFile
    
    For Each myRow In Range("A17:N23").Rows
        For Each myCell In myRow.Rows
             Print #myFile, myCell.Value <<< Error occurs here
        Next myCell
        Write #myFile,
    Next myRow
        
    Close myFile

End Sub

Posted 29-Jan-14 2:41am

Gary Heath

Add a Solution

2 solutions

Solution 1

I think you need to convert the value to a string; see http://www.vb6.us/tutorials/writing-files-vb6-using-write-and-print[^].

Posted 29-Jan-14 2:57am

Richard MacCutchan

Comments

Gary Heath 29-Jan-14 12:19pm

This is what I was wondering but I don't see where that particular page helps. I tried the Cstr(myCell.value) command but it made no difference :-(

Richard MacCutchan 29-Jan-14 12:35pm

It explains how to use Format$ to produce strings from values. However it could be that there is some other reason why that value is causing the problem. You need to check what is in that particular cell that you are trying to print.

Gary Heath 30-Jan-14 15:02pm

Sorry, I've been out and about for the past 48 hours, as far as I know, that Cell contains the letter "P" but I can't prove that because even MsgBox gives me the same error !!!

Richard MacCutchan 31-Jan-14 3:14am

Then you need to use your debugger to step into the code and examine these variables.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gary Heath · Accepted Answer · 2014-02-03T05:45:00

I have changed the way I do this, and instead of using the code ...

VB

For Each myRow In Range("A17:N23").Rows
    For Each myCell In myRow.Rows
        Print #myFile, myCell.Value <<< Error occurs here
    Next myCell
    Write #myFile,
Next myRow

... I am using ...

VB

For myRow = 17 To 23
    For myColumn = 1 To 14
        myCell = Trim(Cells(myRow, myColumn).Value)
        Print #myFile, myCell
    Next myColumn
    Print #myFile,
Next myRow

This works perfectly !