+ Reply to Thread
Results 1 to 3 of 3

Run time error 13 - Type mismatch

  1. 11-18-2013, 08:27 AM #1
    Sc0tt1e
    Sc0tt1e is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    640

    Run time error 13 - Type mismatch

    The below code all seems to work except for the first IF statement (VBA doesn't seem to like the sheet/Range). I think I have to dim sonething as string but dont know how to do it.

    Any help please I am on a tight deadline


    Sub Sophistication()
    ' Sophistication Macro
    ' Sophistion Script is produced for either sophisticated or non sophisticated customers

    If Sheets("Sheet1").Range("M17:N17") = None Then
    Sheets("Sheet2").Select
    Range("D2:R31").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .MergeCells = True
    .WrapText = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.ClearContents
    With Sheets("Sheet2").Range("D2:R31")
    End With
    .Value = "“Your case is currently at the sophistication testing phase. I cannot provide you with a precise timeframe as to how long it will take for your case to complete this stage as each case is different. However on average once a case reaches this stage it typically takes X weeks until an outcome is reached. As soon as the outcome is known we will write to you.“"
    .Font.Name = "Arial"
    .Font.Size = 26
    Range("A1").Select
    End With

    ElseIf Sheets("Sheet1").Range("AB16") = 1 Then
    Sheets("Sheet2").Select
    Range("D2:R31").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .MergeCells = True
    .WrapText = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.ClearContents
    With Sheets("Sheet2").Range("D9:R26")
    End With
    .Value = "Your case has been deemed Sophisticated which means you are not in scope of the review." & vbNewLine _
    & vbNewLine _
    & "We wrote to you on (Date) to advise you of this and to ask whether you wanted to provide further information" & vbNewLine _
    & "Have you received this letter?"
    .Font.Name = "Arial"
    .Font.Size = 26
    Range("A1").Select
    End With

    ElseIf Sheets("Sheet1").Range("AB17") = 1 Then
    Sheets("Sheet2").Select
    Range("D2:R31").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .MergeCells = True
    .WrapText = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.ClearContents
    With Sheets("Sheet2").Range("D9:R26")
    End With
    .Value = "Your case has been deemed Non-Sophisticated which means you are in scope of the review." & vbNewLine _
    & vbNewLine _
    & "We wrote to you on (Date) to advise you of this and to ask whether you wanted to provide further information" & vbNewLine _
    & "Have you received this letter?"
    .Font.Name = "Arial"
    .Font.Size = 26
    Range("A1").Select
    End With

    End If

    End Sub
    Reply With QuoteRegister To Reply
  2. 11-18-2013, 08:29 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Run time error 13 - Type mismatch

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 11-18-2013, 08:30 AM #3
    cytop
    cytop is offline
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Run time error 13 - Type mismatch

    Duplicate of above.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run Time error 13 : Type Mismatch
    By sugaprasad in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2013, 02:06 PM
  2. [SOLVED] Run Time Error 13 Type Mismatch
    By pankaj8219 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-02-2013, 10:07 AM
  3. [SOLVED] Please help with Run-Time error 13 Type Mismatch
    By alec66317 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2012, 08:29 AM
  4. [SOLVED] Run-time error '13': Type mismatch
    By btmtdk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2012, 06:32 PM
  5. Run-Time error 13: type Mismatch Help!
    By Fellhouse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2011, 04:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1