By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Run-time error '13': Type mismatch

100+
P: 126
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
  1. Sub RunMacro()
  2. Dim xlApp As Excel.Application
  3. Dim xlWB As Excel.Workbook
  4. Dim xlWS As Excel.Worksheet
  5. Dim LastUsedRow As Long
  6. Set xlApp = New Excel.Application
  7.  
  8. With xlApp
  9. .Visible = True
  10. .Interactive = True
  11. Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
  12. Set xlWS = xlWB.Worksheets("SelectPolicies")
  13.  
  14. If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then
  15.   LastUsedRow = xlWS.Cells.Find(What:="*", After:=[A1], _
  16.                 SearchOrder:=xlByRows, _
  17.                 SearchDirection:=xlPrevious).Row
  18.   MsgBox "" & LastUsedRow
  19. End If
  20. End With
  21. End Sub
Jul 29 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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
  1. Function LastRow(Optional worksheetname As String = "") As Long
  2.     ' Returns the last row number in the active sheet,
  3.     ' or the specified worksheet if a worksheet name is provided
  4.     Dim rngLast As Range
  5.     If worksheetname = "" Then
  6.         Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)
  7.     Else
  8.         Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)
  9.     End If
  10.     LastRow = rngLast.Row
  11. End Function
  12.  
  13. Function LastColumn(Optional worksheetname As String = "") As Long
  14.     ' Returns the last row number in the active sheet,
  15.     ' or the specified worksheet if a worksheet name is provided
  16.     Dim rngLast As Range
  17.     If worksheetname = "" Then
  18.         Set rngLast = objExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell)
  19.     Else
  20.         Set rngLast = objExcel.Worksheets(worksheetname).Range("A1").SpecialCells(xlLastCell)
  21.     End If
  22.     LastColumn = rngLast.Column
  23. End Function
Feel free to extract/adapt the code as necessary.

-Stewart
Jul 29 '08 #2

ADezii
Expert 5K+
P: 8,638
I think your syntax is incorrect, I am referring to Line #15.
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlWB As Excel.Workbook
  3. Dim xlWS As Excel.Worksheet
  4. Dim LastUsedRow As Long
  5. Dim c
  6. Set xlApp = New Excel.Application
  7.  
  8. With xlApp
  9.   .Visible = True
  10.   .Interactive = True
  11.   Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
  12.   Set xlWS = xlWB.Worksheets("SelectPolicies")
  13.  
  14.   If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then
  15.     LastUsedRow = xlWS.Cells.Find(What:="*", After:=xlWS.Range("A1"), _
  16.                   SearchOrder:=xlByRows, _
  17.                   SearchDirection:=xlPrevious).Row
  18.     MsgBox "" & LastUsedRow
  19.   End If
  20. End With
Jul 30 '08 #3

100+
P: 126
I think your syntax is incorrect, I am referring to Line #15.
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlWB As Excel.Workbook
  3. Dim xlWS As Excel.Worksheet
  4. Dim LastUsedRow As Long
  5. Dim c
  6. Set xlApp = New Excel.Application
  7.  
  8. With xlApp
  9.   .Visible = True
  10.   .Interactive = True
  11.   Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
  12.   Set xlWS = xlWB.Worksheets("SelectPolicies")
  13.  
  14.   If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then
  15.     LastUsedRow = xlWS.Cells.Find(What:="*", After:=xlWS.Range("A1"), _
  16.                   SearchOrder:=xlByRows, _
  17.                   SearchDirection:=xlPrevious).Row
  18.     MsgBox "" & LastUsedRow
  19.   End If
  20. 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
  1. LastUsedRow = .Cells.Find(what:="*", after:=.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
Thanks anyway though!
Jul 30 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
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.
Aug 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.