Macro will not copy values due to Run Time Error 438

Question

I am trying to make a macro that will copy data from one sheet, Form, to another sheet, Log. I quickly came up with a bit of code that I thought would do the trick, but at the point of copying the values, I get a "Runtime Error 438" and after searching the Web, I still cannot make sense of it.

    Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Log")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'copy the data to the log sheet

With ws

  .Cells(iRow, 1).Value = Worksheets("Form").Value(D3)            'this is where runtime error occurs.
  .Cells(iRow, 2).Value = Worksheets("Form").Value(D5)
  .Cells(iRow, 3).Value = Worksheets("Form").Value(D10)
  .Cells(iRow, 4).Value = Worksheets("Form").Value(D12)

End With

'clear the data
Worksheets("Form").Value(D3) = ""
Worksheets("Form").Value(D5) = ""
Worksheets("Form").Value(D10) = ""
Worksheets("Form").Value(D12) = ""

End Sub

I was just wondering if anyone understood the error, how it applies to my code, and could give me a quick rundown of why this is happening? It would be greatly appreciated. Thank you.

Change `.Cells(iRow, 1).Value = Worksheets("Form").Value(D3)` to `.Cells(iRow, 1).Value = Worksheets("Form").Value("D3")`. If you don't use the quotes, it will presume that **D3** is a variable and as you haven't declared it, it will be of type **Variant** — Zac, Sep 27 '19 at 11:30
This is a clear example of benefits about using **[Option Explicit](https://stackoverflow.com/a/2454583/9199828)** — Foxfire And Burns And Burns, Sep 27 '19 at 11:44
@Zac it obviously should be `Worksheets("Form").Range("D3").Value` — BrakNicku, Sep 27 '19 at 11:50
@BrakNicku, you are correct. I did spot that but forgot to change it in my post :) — Zac, Sep 27 '19 at 11:52

Macro will not copy values due to Run Time Error 438

0 Answers0