VBA Type Mismatch error

[tmdgus]

Hi, there are three columns(open date, close date and months).
I need to use VBA to automate date adding process.
If there is a value in column A, I want to add column A and C together and put the result in column B.
For example, in cell A2, I want to input date 6 months after 02/08/2021(cell A1), which will be 02/02/2022.

I have tried to code VBA, but it keeps getting type mismatch error.
If anyone knows how to fix this, please help.

Here is the mini sheet:

Practice.xlsm
	A	B	C
1	Open Date	Close Date	Months
2	2/8/2021		6
3	9/10/2022		3
4	3/3/2022		9
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sheet1

Here is the VBA code I wrote:

Sub AddDate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim startcell As Range
Dim startcolumn As Range
Dim endcell As Range
Dim endcolumn As Range
Dim monthcell As Range
Dim monthcolumn As Range
Set monthcolumn = ws.Range("C1:C65565")
Set startcolumn = ws.Range("A1:A65565")
Set endcolumn = ws.Range("B1:B65565")

For Each startcell In startcolumn
For Each monthcell In monthcolumn
For Each endcell In endcolumn
    If Not IsEmpty(startcell) Then
        endcell = DateAdd("m", monthcell, startcell)
    End If
Next
Next
Next

End Sub

I am not quite sure if this is the right way to do, since I am new to VBA.
I'd appreciate a lot if anyone could help.

 

[Alex Blakenburg]

You are not going to have any trouble getting help, you certainly ticked all the boxes. XL2BB provided, Code provided using tags, error message provided. The only thing missing is when you click on Debug in the error box it highlights a line in the code which we normally also need.
In this case the issue is pretty clear.
It is erroring out because you are starting your range at row 1 which is the heading row and trying to apply a date calculation in DateAdd to the text in the heading causing a mismatch.
You are however fortunate it didn't run because you are running 3 loops which will run 65565 x 65565 x 65565 times.

Below is a modified version that tries to stay close to the method you were using.

Sub AddDate()
Dim ws As Worksheet
Dim startcell As Range
Dim startcolumn As Range
Dim lastRow As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set startcolumn = ws.Range("A2:A" & lastRow)

For Each startcell In startcolumn
    If Not IsEmpty(startcell) Then
        startcell.Offset(0, 1) = DateAdd("m", startcell.Offset(0, 2), startcell)
    End If
Next

End Sub

 

[steve the fish]

You have quite a few problems there. If you need explaining then i can but heres one that will work.

Sub AddDate()

Dim lr As Long

With ThisWorkbook.Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        .Range("B2:B" & lr).FormulaR1C1 = "=IF(RC[-1]="""","""",EDATE(RC[-1],RC[1]))"
        .Range("B2:B" & lr).Value = .Range("B2:B" & lr).Value
    End If
End With

End Sub

 

[tmdgus]

Thank you so much for your help!!
I get my issues now and things work perfectly fine.
Have a great day