vba run-time error 13 - when trying to execute a loop

Question

I have this code

Sub fillinvalues()

Dim interest As Double
Dim rate As Double
Dim payment As Double
Dim period As Double
Dim ws As Worksheet
Dim i As Object
Dim begbal As Double

Set ws = Worksheets("Sheet1")
payment = ws.Range("H3").Value
rate = ws.Range("H4").Value
begbal = ws.Range("E3").Value
begbal = 80000

Worksheets("Sheet1").Range("A2").Value = "PaymentNumber"
Worksheets("Sheet1").Range("B2").Value = "Payment/period"
Worksheets("Sheet1").Range("C2").Value = "Principal"
Worksheets("Sheet1").Range("D2").Value = "Interest"
Worksheets("Sheet1").Range("E2").Value = "RemainingBal"

ws.Range("A1:G1").Value = "Monthly Payments at effective monthy interest rate for 25-years"

ws.Range("A3").Value = 0
ws.Range("A3").Select
ws.Range("A3").Activate
period = 0

Do Until period = 301
' period = 0
ActiveCell.Offset(period, 0) = period
ActiveCell.Offset(period, 1) = payment
period = period + 1
Loop

Cells(3, 2).ClearContents

Cells(4, 4).Select
 Do Until ws.Columns(1) = Empty
ActiveCell.Value = ActiveCell.Offset(-1, 1).Value * rate
ActiveCell.Offset(0, -1).Value = ActiveCell.Offset(0, -2).Value - ActiveCell.Value
'For Each i In ws.Columns(4)
'i = ActiveCell.Offset(-1, 1).Value * rate
'ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value - ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(1, 0).Activate
'Next i

Loop
End Sub

But I am getting the type mismatch error on cell D4 (where the 2nd loop begins after Cells(4,4). I don't understand why as I have declared rate as a double and the current cell's value is a double (see image attached). Thanks.

ws.Columns(1) = Empty should be ActiveCell = vbNullString but you should avoid using activecell... This is super slow Try using ws.Cells(4,i) with For i = 4 to LastRow and see this for lastrow : stackoverflow.com/documentation/excel-vba/918/… — R3uK
I tried using a For..each.. with an object declaration, but didn't have any luck with it. I am still a rookie when it comes to developing VB programs so I'm tyring to keep it simple for now. Thanks for helping. — Mike Mirabelli

Brandon Barney Brandon Barney · Accepted Answer · 2017-02-09T15:02:46

I can't comment because I dont have enough rep, but I would wager that something is going on with:

Do until ws.Columns(1) = Empty

When I tested this in a new workbook I always got true when testing if column = empty. I believe this is because "Empty" means a value hasn't been assigned and it is not a property of the range.

I also agree with R3uK. Avoid using activecell and offsets. If you don't know how there are plenty of resources out there for this problem. It is much better to refer by cell references, or to use an array.

Lastly, you may also encounter an issue when subtracting values without any kind of error handling or value checks. As is, a cell could contain a string for example. If you then tried to subtract or multiply this string you would encounter an error.

I hope this helps!

EDIT: Completely missed the fact that R3uK handled the empty thing before I did.

vba run-time error 13 - when trying to execute a loop

1 Answers