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.

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).

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.

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.

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.

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.