Run-time error '13': Type mismatch

JFKJr

Hello everyone, the following code is working fine for the first time when I run it and giving "Run-time error '13': Type mismatch" at line #15 during the second time and so on....

The code is working sometimes and sometimes not!

Please kindly let me know what to do?

Thanks in advance.

Expand|Select|Wrap|Line Numbers

 Sub RunMacro()

Dim xlApp As Excel.Application

Dim xlWB As Excel.Workbook

Dim xlWS As Excel.Worksheet

Dim LastUsedRow As Long

Set xlApp = New Excel.Application
 
With xlApp

.Visible = True

.Interactive = True

Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)

Set xlWS = xlWB.Worksheets("SelectPolicies")
 
If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then

  LastUsedRow = xlWS.Cells.Find(What:="*", After:=[A1], _

                SearchOrder:=xlByRows, _

                SearchDirection:=xlPrevious).Row

  MsgBox "" & LastUsedRow

End If

End With

End Sub

Stewart Ross

Hi JFKjr. Not sure why this fails on second and subsequent passes. I suspect that the use of [A1] as a cell reference is part of the issue. However, there is no need to do a search at all to find the last row (and the last column).

The following two functions - part of an automated Excel query transfer module I developed - return the last row and column of object objExcel (which is global in scope within the class module concerned).

Expand|Select|Wrap|Line Numbers

 Function LastRow(Optional worksheetname As String = "") As Long

    ' Returns the last row number in the active sheet,

    ' or the specified worksheet if a worksheet name is provided

    Dim rngLast As Range

    If worksheetname = "" Then

        Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)

    Else

        Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)

    End If

    LastRow = rngLast.Row

End Function
 
Function LastColumn(Optional worksheetname As String = "") As Long

    ' Returns the last row number in the active sheet,

    ' or the specified worksheet if a worksheet name is provided

    Dim rngLast As Range

    If worksheetname = "" Then

        Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)

    Else

        Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)

    End If

    LastColumn = rngLast.Column

End Function

Feel free to extract/adapt the code as necessary.

-Stewart

ADezii

I think your syntax is incorrect, I am referring to Line #15.

Expand|Select|Wrap|Line Numbers

 Dim xlApp As Excel.Application

Dim xlWB As Excel.Workbook

Dim xlWS As Excel.Worksheet

Dim LastUsedRow As Long

Dim c

Set xlApp = New Excel.Application
 
With xlApp

  .Visible = True

  .Interactive = True

  Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)

  Set xlWS = xlWB.Worksheets("SelectPolicies")
 
  If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then

    LastUsedRow = xlWS.Cells.Find(What:="*", After:=xlWS.Range("A1"), _

                  SearchOrder:=xlByRows, _

                  SearchDirection:=xlPrevious).Row

    MsgBox "" & LastUsedRow

  End If

End With

JFKJr

I think your syntax is incorrect, I am referring to Line #15.

Expand|Select|Wrap|Line Numbers

Dim xlApp As Excel.Application

Dim xlWB As Excel.Workbook

Dim xlWS As Excel.Worksheet

Dim LastUsedRow As Long

Dim c

Set xlApp = New Excel.Application

With xlApp

  .Visible = True

  .Interactive = True

  Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)

  Set xlWS = xlWB.Worksheets("SelectPolicies")

  If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then

    LastUsedRow = xlWS.Cells.Find(What:="*", After:=xlWS.Range("A1"), _

                  SearchOrder:=xlByRows, _

                  SearchDirection:=xlPrevious).Row

    MsgBox "" & LastUsedRow

  End If

End With

Hello Stewart and ADezii, I solved the issue by changing the 15 line above to the following and it worked.

Expand|Select|Wrap|Line Numbers

 LastUsedRow = .Cells.Find(what:="*", after:=.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
 

Thanks anyway though!

NeoPa

Indeed. This is essentially what ADezii suggested in the previous post (although alternative wording was used).

When dealing with Application Automation in Office, I always recommend that the code for the foreign application be developed natively first (in this case within Excel) and only when it is working correctly port it across to the other application (in this case Access).

This can reduce the number of headaches considerably. Don't forget that some of the references to internal objects need to be modified for when they are referenced as a foreign application though.

Run-time error '13': Type mismatch

Post your reply

Similar topics