VBA-Excel Overflow Error due to Long data type

Question

This may seem too easy, but I am so desperate.

What I need to do is get the last value of the column "D" which has a

big amount of number, ex. 987654321, the code is fine if the value is only two-digit. I just can't identify the problem.

Dim lastRow As Long
lastRow = Cells(Rows.Count, "D").End(xlUp).Value
Sheets("Sheet1").TxtBox1.Value = lastRow

Answer 1

Like I mentioned in my comment, for such large number you have to declare it as a double.

Dim lastRow As Double

Alternatively since you want to store it in a textbox you can do 2 things

1. Declare it as a string

2. Store it directly in the Textbox.

   Option Explicit
   
   Sub Sample1()
       Dim lastRow As String
   
       With Sheets("Sheet1")
           lastRow = .Cells(.Rows.Count, "D").End(xlUp).Value
           .TextBox1.Value = lastRow
       End With
   End Sub
   
   Sub Sample2()
       With Sheets("Sheet1")
           .TextBox1.Value = .Cells(.Rows.Count, "D").End(xlUp).Value
       End With
   End Sub