Re: Help with a macro- 'Run-Time error '13' Type Mismatch

**pman** · 08-16-2012, 01:36 PM

Hi everyone, I'm new to this forum, but I'm really hoping someone can help me out here. There's a macro that someone created for a worksheet that basically takes info from a financial statement and puts it into a different format to be uploaded into our accounting system. This month, for some reason it isn't working. I tried helping out the person that uses it, but I can't seem to figure out what the problem is and the person that created the macro has left the company. The part that is a problem is the part that says If ActiveCell = "XXX" Then. That's what's being flagged in the debug. See below for full code. I don't think it's the macro though, I think the problem lies with the excel spreadsheet, but I don't see anything on the spreadsheet that is wrong. The file was too large to upload, so please let me know what you need to see and I'll figure out a way to attach it.

Thank you in advance!!!!!!!

Sub CREATE_MRI_UPLOAD()
     
    ThisUpd = Application.InputBox("Type in the password to start the Create MRI Upload macro")
    
    If ThisUpd <> "MMREF" Or ThisUpd = "" Then
        GoTo LineEnd
    End If
    
    
    
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
        .CalculateBeforeSave = False
    End With

    ActiveWorkbook.Unprotect
    Sheets("MRIUPLOAD").Visible = True
    Sheets("MRIUPLOAD").Select
    Range("A2:AA10000").Select
    Selection.ClearContents
    Range("I2").Select

    Sheets("Summary Budget").Select
    Application.Goto Reference:="PRTTYPE"
    ActiveCell.FormulaR1C1 = "3"
    Range("A10").Select

    check = False
    Do
    Do While check = False
        If ActiveCell = 1 Or ActiveCell = "" Then
            ActiveCell.Offset(1, 0).Select
        ElseIf ActiveCell = 0 And ActiveCell.Offset(0, 3) = "" Then
            ActiveCell.Offset(1, 0).Select
        ElseIf ActiveCell = 0 And ActiveCell.Offset(0, 3) <> "" Then
            Application.Run macro:="XLCOPYLINE"
            Sheets("Summary Budget").Select
            ActiveCell.Offset(1, -1).Select
        End If
        If ActiveCell = "XXX" Then
            check = True
    Exit Do
        End If

**Rhynocerous** · 08-16-2012, 02:28 PM

Can you post the rest of the code? And let me know what error you're getting specifically

Originally Posted by pman

I can't seem to figure out what the problem is and the person that created the macro has left the company.

Sounds familiar.

**pman** · 08-16-2012, 02:36 PM

Here is all of the code in the macro. The part that is being flagged by the debug is the end of the code in the original post:

 If ActiveCell = "XXX" Then

Loop
    Loop Until check = True

    Sheets("MRIUPLOAD").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]<""MM40000000"",RC[-1]*-1,RC[-1])"
    Selection.Copy
    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Calculate
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

    With Application
        .Calculation = xlAutomatic
        .MaxChange = 0.001
        .CalculateBeforeSave = False
    End With

LineEnd:
End Sub
Sub XLCOPYLINE()
'
' XLCOPYLINE Macro
' Macro recorded 7/31/2007 by Doug Speckmann
'

'
    Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 15)).Select
    Selection.Copy
    Sheets("MRIUPLOAD").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Range("A1:A1").Select
    Selection.Copy
    ActiveCell.Offset(0, -4).Select
    ActiveCell.Range("A1:A12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveCell.Offset(0, 5).Select
    Selection.Copy
    ActiveCell.Offset(0, -6).Select
    ActiveCell.Range("A1:A12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveCell.Offset(0, 8).Select
    Range(Selection, ActiveCell.Offset(0, 12)).Select
    Selection.Copy
    ActiveCell.Offset(0, -4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

    ActiveCell.Offset(0, -6).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""01"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""02"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""03"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""04"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""05"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""06"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""07"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""08"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""09"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""10"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""11"")"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT('Summary Budget'!R2C1,4),""12"")"
    ActiveCell.Offset(-11, 1).Select

    ActiveCell.FormulaR1C1 = "=LEFT('MRI Dump-ACTUAL'!R2C1,6)"
    Selection.Copy
    ActiveCell.Range("A1:A12").Select
    ActiveSheet.Paste

    ActiveCell.Offset(0, 3).Select
    ActiveCell.FormulaR1C1 = "A"
    Selection.Copy
    ActiveCell.Range("A1:A12").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 3).Select

    Range(Selection, ActiveCell.Offset(11, 16)).Select
    Selection.ClearContents
    ActiveCell.Offset(12, 0).Select
End Sub

**Rhynocerous** · 08-16-2012, 02:46 PM

Well that's pretty much what I expected, the code is meant to run until a cell containing "XXX" is reached, so "XXX" is being used to denote the end.

What is the actual error text? If you're getting a 1004 it's possible that there is no "XXX" so the code just runs until it hits the row limit of your Excel version.

My guess is that he's supposed to be entering "XXX" at the end of the "Summary Budget" sheet, but isn't. I'd need to see the workbook, the code is very wonky.

**pman** · 08-16-2012, 02:58 PM

Thanks Rhynocerous,

The message I'm getting is 'Run-Time error '13' Type Mismatch.

You are correct, basically he has a cell in column A that has XXX as a way of marking the end, and I verified that it is in fact there so I'm not clear why that would be the issue.

The macro in question is the "Create_MRI_Upload" macro and the password is MMREF. I'm usually pretty amazing with excel, but this macro seems unnecessarily complicated to me and I gave up

**Rhynocerous** · 08-16-2012, 03:31 PM

It doesn't look like it's tripping up on the "XXX."

When the macro runs down column A it snags on A16 and again on A53

Make column A some color other than white and you'll see what I mean. The formula in A16 snags on the "Note for accounting." As for A53, I bet someone accidentally hit the space bar with E53 selected and broke that formula too.

Delete A16 which as far as I can tell will never need to have contents, and replace E53 with =SUM(E54:E54) to restore it. Data validation would have been very useful in this spreadsheet.