VBA Type mismatch error when reading value from a cell

Question

I'm reading the value from a spreadsheet and adding 1 on the read value using the below code. The error occurs at ModelNumber = PreviousModel + 1 and it says type mismatch. LastRowPrevious shows correct but PreviousModel shows empty. Please help. Thanks!

Option Explicit
Public ModelNumber As String
Public PreviousModel As String
Public FinalModelNumber As String
Sub TrackerInput()
Dim ModelDescription As String
Dim LastRowPrevious As String
LastRowPrevious = Worksheets("Model Tracker").Cells(Rows.Count, 1).End(xlUp).Row
If LastRowPrevious = 2 Then
    ModelNumber = 2
    PreviousModel = 1
Else
    PreviousModel = Cells(LastRowPrevious, 1).Value
    ModelNumber = PreviousModel + 1
End If
MsgBox ("You are creating a new model. Model number is " & ModelNumber)
End Sub

Just FYI, your code will break (or behave erratically) when Worksheets("Model Tracker") isn't the ActiveSheet, because Rows is referring to the active sheet but you're trying to get the last row on the Model Tracker sheet. And Cells(LastRowPrevious, 1) also refers to the active sheet. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 22, 2016 at 17:50
PLEASE use Option Explict, compile, then define needed variables ... it will relieve stress like nothing you have ever seen :) Add Dim for PreviousModel and ModelNumber as Integer (or long or double). You may also want to check if the cell is 'empty' (or check if 'IsNumeric'...) — Wayne G. Dunn
– Wayne G. Dunn, Commented Nov 22, 2016 at 17:50
It looks like you're trying to use Excel as a database. FWIW it wasn't meant for this, and you'll inevitably run into the issues associated with using Excel as a database. Only a database ensures referential integrity and consistent ID/numbering of records. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 22, 2016 at 17:54
I had option explicit and all the variables defined. Forgot to include them here. Sorry. — vivi11130704
– vivi11130704, Commented Nov 22, 2016 at 17:54
Because Rows.Count is the number of rows in the active sheet. You want that to be Worksheets("Model Tracker").Rows.Count - gets really sloppy, really fast. hence most people would do With Worksheets("Model Tracker") and do LastRowPrevious = .Cells(.Rows.Count, 1).End(xlUp).Row inside that With block (notice the dots). — Mathieu Guindon
– Mathieu Guindon, Commented Nov 22, 2016 at 17:58

Doug Coats · Accepted Answer · 2016-11-22 18:07:04Z

2

This worked perfectly for me:

Option Explicit
Public ModelNumber$
Public PreviousModel$
Public FinalModelNumber$
Sub TrackerInput()
    Dim ModelDescription$
    Dim LastRowPrevious$
    With ThisWorkbook.Worksheets("Sheet1")
        LastRowPrevious = .Cells(.Rows.Count, 1).End(xlUp).Row
        If LastRowPrevious = 2 Then
            ModelNumber = 2
            PreviousModel = 1
        Else
            PreviousModel = .Cells(LastRowPrevious, 1).Value
            ModelNumber = PreviousModel + 1
        End If
    End With

    Debug.Print ; LastRowPrevious
    Debug.Print ; PreviousModel
    Debug.Print ; ModelNumber
    MsgBox ("You are creating a new model. Model number is " & ModelNumber)
End Sub

Noticed I fully referenced the worksheet instead of trying to get VBA to figure out what worksheet im referring too.

edited Nov 22, 2016 at 18:07

answered Nov 22, 2016 at 17:58

Doug Coats

7,2279 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vivi11130704 Over a year ago

@Mat's Mug was right, I have to stay on that worksheet or put everything in the with. Thank you guys for the help!

Collectives™ on Stack Overflow

VBA Type mismatch error when reading value from a cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related