none
cells.find() return "Subscript out of range" error in excel.application RRS feed

  • Question

  • i want to get the last used cell in a sheet, through Hans's suggest:

    Dim excel As Object Set excel = CreateObject("Excel.application")
    excel.Workbooks.Open "d:\Book1.xlsx" MsgBox excel.Worksheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    excel.quit Set excel = Nothing

    but it return "Subscript out of range" error in the line of Celles.Find(...),
    if take off condition of "SearchOrder:=xlByRows", it's no error occured but the results is not I expect of course.

    so why cells.find() return this error? thanks!


    • Edited by itakeblue Sunday, May 13, 2012 5:28 AM
    Sunday, May 13, 2012 5:16 AM

Answers

  • Try

    SearchOrder:=1.

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:43 AM
    Sunday, May 13, 2012 7:50 AM
    Answerer
  • When you use late binding all of the excel constants need to be replaced with the value that they represent in excel.

    As per the earlier reply you replaced xlByRows with 1

    Also need to replace xlPrevious with 2


    Regards, OssieMac

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:43 AM
    Sunday, May 13, 2012 11:21 AM
  • 1.Either

    Worksheets(1).UsedRange.Column+Worksheets(1).UsedRange.Columns.count-1

    Worksheets(1).UsedRange.row+Worksheets(1).UsedRange.rows.count-1

    2.Or

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Row

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Column

    • Marked as answer by itakeblue Sunday, May 13, 2012 3:28 PM
    Sunday, May 13, 2012 3:13 PM
    Answerer

All replies

  • Try

    SearchOrder:=1.

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:43 AM
    Sunday, May 13, 2012 7:50 AM
    Answerer
  • Try

    SearchOrder:=1.

    thanks for your reply, it works and no error raise up.

    but use your method some sheets return correct, some sheets return

    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=1, SearchDirection:=xlPrevious).Column  --> 2
    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=1, SearchDirection:=xlPrevious).Row       --> 1

    actually, the sheet has many cell with data..so, it's a little strange :)


    • Edited by itakeblue Sunday, May 13, 2012 3:29 PM
    Sunday, May 13, 2012 9:05 AM
  • You are specifying the xlByRows so excel searches one row first then goes to next row.

    Suppose in C10 and Y1 has only data

    As your direction is xlPrevious Excel will serach either last used row or last used column.When used in UI then it refers to activecell but in VBA it is always A1 cell of sheet.

    Your Order is xlByRows.So excel searches the last used row first which is C10 and return is 3

    But if you specify xlByColumns then excel will search the last used column which is Y and return 25.

    Hope this clarifies.

    Sunday, May 13, 2012 10:36 AM
    Answerer
  • When you use late binding all of the excel constants need to be replaced with the value that they represent in excel.

    As per the earlier reply you replaced xlByRows with 1

    Also need to replace xlPrevious with 2


    Regards, OssieMac

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:43 AM
    Sunday, May 13, 2012 11:21 AM
  • thanks for your detailed explanation!

    but i have a little confused, direction is xlPrevious, why "in VBA it is always A1 cell of sheet" ?

    the last used cell's position is what i want to know exactly, when using:

    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=xlbyColumns, SearchDirection:=xlPrevious).Column
    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=xlbyRows, SearchDirection:=xlPrevious).Row

    it rasie up error '9', "Subscript out of range"

    when using:

    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=1, SearchDirection:=xlPrevious).Column
    excel.Worksheets(1).cells.Find(What:="*", SearchOrder:=1, SearchDirection:=xlPrevious).Row

    it returns unexpect results, eg: (2 : 1), however D47 has data!

    so what should i do now? thanks!


    • Edited by itakeblue Sunday, May 13, 2012 11:39 AM
    Sunday, May 13, 2012 11:32 AM
  • Why A1?

    Ans:- Find is method of Range and it starts searching from first cell of the parent range.Worksheet has one cells property which is the collection of all cell of a worksheet.So it starts searching from first cell of Worksheets(1).Cells which is A1.

    As I mentioned depending upon the SearchOrder the return range will vary.If you want to know the last cell then some other options are there.

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell)

    or

    Worksheets(1).UsedRange

    The problem with these methods is it will not update immediately if some formatting is there.

    Sunday, May 13, 2012 12:07 PM
    Answerer
  • Worksheets(1).UsedRange.Columns.Count
    Worksheets(1).UsedRange.Rows.Count

    they return both 1 however D47 has data.

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Rows.Count

    get an error "can't get SpecialCells property of Range"

    Sunday, May 13, 2012 3:09 PM
  • When you use late binding all of the excel constants need to be replaced with the value that they represent in excel.

    As per the earlier reply you replaced xlByRows with 1

    Also need to replace xlPrevious with 2

    Thanks, replace xlByRows with 1 cause if not do that it will raise up "Subscript out of range" error, but it's fine about do not replace xlPrevious with 2.

    but problem is they return (1:2) result however D47 has data.

    Sunday, May 13, 2012 3:12 PM
  • 1.Either

    Worksheets(1).UsedRange.Column+Worksheets(1).UsedRange.Columns.count-1

    Worksheets(1).UsedRange.row+Worksheets(1).UsedRange.rows.count-1

    2.Or

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Row

    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Column

    • Marked as answer by itakeblue Sunday, May 13, 2012 3:28 PM
    Sunday, May 13, 2012 3:13 PM
    Answerer
  • Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Row
    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Column

    get 'real-time error 1004, can't get SpecialCells property of Range'

    but

    Worksheets(1).UsedRange.Column+Worksheets(1).UsedRange.Columns.count-1
    Worksheets(1).UsedRange.row+Worksheets(1).UsedRange.rows.count-1

    they really working well! thank Learning and Learning very much to help me figure this out, thanks again!

    Sunday, May 13, 2012 3:28 PM
  • Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Row
    Worksheets(1).Cells.Specialcells(xlCellTypeLastCell).Column

    Thought you might like a little more explanation. The code you originally posted indicates that you are calling Excel from another application and without using Early Binding, that application does not know the value of the constant xlCellTypeLastCell (which is actually 11). Following code uses the SpecialCells method of assigning the last row and last column to variables.

    Dim lngLastCol As Long
    Dim lngLastRow As Long

    lngLastRow = Excel.Worksheets(1).Cells.SpecialCells(11).Row
    lngLastCol = Excel.Worksheets(1).Cells.SpecialCells(11).Column

    MsgBox "Last Column = " & lngLastCol & vbCrLf & _
            "Last Row = " & lngLastRow

    For interest what application are you calling Excel from?

    If you don't understand my terms "Early Binding" and "Late Binding" then Google the terms and I am sure that you will find a myriad of sites with explanations.

    When using Late Binding, if you don't know the value of a constant then you need to open the application and then go into the VBA editor and enter the constant name in Help and you should find the "enumeration" value of that constant. (Note that Help in the interactive mode and Help in VBA are 2 different helps. If you have Help open in the interactive mode, you cannot simply change window to it when in VBA editor; you must open help again from the VBA window and vice versa.)

    Going back to your original question, the following is an example of using the Find method for the last column and last row and assigning them to variables. (The enumeration values have been used for the constants.)

    Dim lngLastCol As Long
    Dim lngLastRow As Long

    lngLastRow = Excel.Worksheets(1).Cells.Find(What:="*", SearchOrder:=2, SearchDirection:=2).Column
    lngLastCol = Excel.Worksheets(1).Cells.Find(What:="*", SearchOrder:=1, SearchDirection:=2).Row

    MsgBox "Last Column = " & lngLastCol & vbCrLf & _
            "Last Row = " & lngLastRow

    Hope my explanation helps and feel free to get back if there is anything you do not understand and I will do my best to explain.


    Regards, OssieMac

    Sunday, May 13, 2012 10:05 PM
  • Thanks OssieMac.The last snippet you provided is the most assured way to know the last boundary cell with value.

    @itakeblue:

    The XlCellTypeLastCell will be perfect if only used with values with cells.If you delete the value from the lastcell then excel will update itself.

    But if used with formatted cell then may create some problem.

    If at any time some cell was formatted which is not default excel treats that also as used cell.Even if later on changed to default formatting excel will not update.Saving,closing and reopening will not  help.

    If that formatting was cleared using Clear methods under Home tab then after saving the file excel updates itself.

    For example in a new sheet go to F10.Format the background yellow and check with xlCellTypeLastCell.It will show F10.Now change F10 to "No Fill".See what retuns with xlcelltypelastcell.

    The same problem lies with UsedRange also.So pls use that property judiciously.

    Monday, May 14, 2012 7:15 AM
    Answerer
  • now i understood about Early Binding, thanks much for your clear explain.

    For interest what application are you calling Excel from?

    i'm try to make a program to crawle and analyze documents of LAN by VB6, it's like google mini, so i calling excel.application to get all text in a sheets.

    thanks.

    • Edited by itakeblue Tuesday, May 15, 2012 3:20 AM
    Tuesday, May 15, 2012 2:54 AM
  • thanks for about two property difference, that's clear.

    but sorry in practice, if a sheet with many like 1000+ rows, using 'for...next' to read every cell to get content it very  very slow.

    For x = 1 To (excel.Worksheets(i).UsedRange.Column + excel.Worksheets(i).UsedRange.Columns.Count - 1)
         For y = 1 To (excel.Worksheets(i).UsedRange.Row + excel.Worksheets(i).UsedRange.Rows.Count - 1)
                content = content & " " & excel.Worksheets(i).Cells(y, x)
         Next
    Next

    so i given up this method, and actually we can use 'excel.Worksheets(i).SaveAs' to save .xls as .txt (unicode), then read .txt and get all contents is more faster.

    thanks again, guys :)

    Tuesday, May 15, 2012 3:08 AM