Runtime Error 13 : Type mismatch

Question

This is my first time coding from scratch and a very dumb question indeed.

Why do i keep getting this error?

Runtime Error '13' : Type mismatch

My value in B1 is an integer

Private Sub Workbook_Open()
xdate = Worksheets("sheet1").Range("A1")
lsdate = DateAdd("d", -7, Date)
'MsgBox lsdate
If Day(lsdate) > Day(xdate) Then
    Worksheets("sheet1").Range ("B1") * 1 = result
Else
    Worksheets("sheet1").Range ("B1") * 1.07 = result
End If
Range("c1").Value = result
End Sub

I have further edit my code to the following but not able to get the condition to work

'To create the following condition
'If less than 7 days interest = 0%
'if 8 to 30 days interest = 7%
'if more than 31 days interest = 9%

Sub Workbook_Open()

For i = 1 To 3 'Rows.Count
xdate = Cells(i, 1)
'MsgBox Cells(i, 1)
nulldate = DateAdd("d", -7, Date)
irate7late = DateAdd("d", -8, Date)
irate7early = DateAdd("d", -30, Date)


If Day(nulldate) < Day(xdate) Then
    result = Cells(i, 2) * 1
ElseIf Day(irate7early) <= Day(xdate) And Day(xdate) <= Day(irate7late) Then
            '30/9/2015      20/10/2015      20/10/2015      22/10/2015
    result = Cells(i, 2) * 1.07

ElseIf Day(irate7early) > Day(xdate) Then
    result = Cells(i, 2) * 1.09
End If

Cells(i, 3).Value = result

Next i

End Sub

Answer 1

variables need to get the values from sheet. You are trying opposite way which is why you getting error.

Private Sub Workbook_Open()
xdate = Worksheets("sheet1").Range("A1")
lsdate = DateAdd("d", -7, Date)
'MsgBox lsdate
If Day(lsdate) > Day(xdate) Then
result = Worksheets("sheet1").Range("B1") * 1
Else
result = Worksheets("sheet1").Range("B1") * 1.07
End If
Range("c1").Value = result
End Sub


Sub test1()

Dim var1 As Variant

' If you need to get values from the range("D1") Then use the below code
var1 = Worksheets("sheet1").Range("D1").Value

End Sub

Answer 2

You seem pretty new at programming, so I'll explain plainly :

When you are trying to assign a value to a variable in almost every language, you use :

variable = value

So you can do a lot of operations on the value, which is on the right of the equals =.

BUT you canNOT do operations on the left of the equals = when you simply assigning a value to a variable. You can do almost any operations when you are testing 2 variables but you'll have a keyword like If or While at the start of the line of code.

---

So the issue with your code is that you reversed the order of the value and the variable and tried to do value * 1 = variable

Here is your corrected code (I indented it so that it can be read easily) :

Private Sub Workbook_Open()
xdate = Worksheets("sheet1").Range("A1")
lsdate = DateAdd("d", -7, Date)
'MsgBox lsdate
If Day(lsdate) > Day(xdate) Then
   result = Worksheets("sheet1").Range ("B1") * 1
Else
   result = Worksheets("sheet1").Range ("B1") * 1.07
End If

Sheets("Sheet_name").Range("c1").Value = result

End Sub

And you should always reference the sheet you are working with, because previously, your code didn't specify on which sheet the C1 was supposed to be.

So here, just change Sheet_name to whatever the name of your sheet is!