Macro code from Excel 2003 doesn't work in 2007

Question

This code works well in Excel 2003 but fails in Excel 2007. What am I not seeing in it that crashes it? It errors out when it gets to the "LastRow =".. This is my error message: Run-Time Error 13 Type Mismatch

 Dim LastRow As Long
 Dim LastColumn As Integer
 Dim LastCell As Range, NextCell As Range

'  ****************************************************
    '  Finds LastRow and LastColumn
    With Worksheets("DB")
     '  Find Last Row/Col
          If WorksheetFunction.CountA(Cells) > 0 Then
     ' Search for any entry, by searching backwards by rows
             LastRow = Cells.Find(What:="*", After:=[A1], _
                 SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious).Row
     ' Search for any entry, by searching backwards by columns
             LastColumn = Cells.Find(What:="*", After:=[A1], _
                 SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious).Column
          End If
       Set NextCell = Worksheets("DB").Cells(LastRow + 1, (LastColumn))
    End With
 '  ****************************************************

Found an error. Guess I copied the Lastrow and was GOING to change the second one to columns. but that still doesn't solve the hang on the first chunk. Opps while editing that last part to columns I see that I may have typed an extra "s" in the .Rows Looks like it SHOULD be .Row I'll see when I get home since my hard copy at work shows no "s". Guess that's what I get when trying to "Remember" the code when I get home. To "s" or not to "s", that is the question. LOL At least I think I solved it with a little poke the the head. Thanks Siddharth.

`LastRow` is of type `Long` but it appears that the call to `Cells.Find()` is not coming up with a `Long` value. Has the spreadsheet against which you are running this VBA macro changed as well? — Tim Biegeleisen, Jul 24 '15 at 05:13
@Tim Nothing has changed. Just copying code for use at home, to allow for testing I have a populated a sheet I am using as a log similar to one at work, it just fails at that point. Going to try the suggestion from Siddharth when I get home. I know there are a few differences in the transportability from 2003 to 2007/10 and as a novice programmer I am still figuring that out. I wind up doing the same code at home on a newer version of Excel to proactively rewrite my code for work if and when we switch to a newer version, so I am ready for the changeover with relatively little headache. — Valhalla_33, Jul 24 '15 at 15:30

score 2 · Answer 1 · edited May 23 '17 at 11:51

Are you sure it works with Excel 2003?

You have to use .Row instead of .Rows. See This

Your code will also fail because your LastColumn=0

Is this what you are trying?

Sub Sample()
    Dim LastRow As Long
    Dim LastColumn As Integer
    Dim NextCell As Range

    With Worksheets("DB")
        If WorksheetFunction.CountA(Cells) > 0 Then
            '~~> Find Last Row
            LastRow = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

            '~~> Find Last Column
            LastColumn = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious).Column
        End If

        '~~> Set the cell to the first empty cell after the last cell
        '~~> which has data
        Set NextCell = Worksheets("DB").Cells(LastRow + 1, (LastColumn))

        '~~> Display the address of that cell
        MsgBox NextCell.Address
    End With
End Sub

@ Siddharth I am using this code at work on several files that save to separate log sheets. Needing the last row this works really well in 2003 Excel. But when I try using the code on my Excel 2007 it fails. I have been using the code for over 10 months now without fail until it was tried on 2007. I'll give the **.Row** to **.Rows** a try when I get home. — Valhalla_33, Jul 24 '15 at 15:21

Macro code from Excel 2003 doesn't work in 2007

1 Answers1