VBA Command Error - Beginner's Advice

[danhenshy23]

Hi,

I am using a template for a data entry form that I found online and have added some additional fields. Since adding the new fields I am getting the following error:

"Run-time error '1004'
Method 'Range' of object_Worksheet failed.


Below is my code. Can anyone identify the issue?

Sub UpdateLogWorksheet()


Dim historyWks As Worksheet
Dim inputWks As Worksheet


Dim nextRow As Long
Dim oCol As Long


Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D15,D16,D20,D21,D25,D26,D30,D31,"


Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")


With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With


With inputWks
Set myRng = .Range(myCopy)


If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With


With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub

 

[Joe4]

Does it give you a "Debug" option? If it does, click it and see while line of code it tells you is causing the issues.

Have you tried stepping through your code to find where the error occurs, and that the values are at that point?

 

[danhenshy23]

Hi,

I have a Debug option and the line of code that is highlighted is

With inputWks
Set myRng = .Range(myCopy)

It looks like it's to do with the additional sections I have added to the form, however the "mycopy" part of the code is referencing the correct cells (see below)

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D15,D16,D20,D21,D25,D26,D30,D31,"



I'm very new to VBA, so how would I go about stepping through each part of the code?

 

[Joe4]

You have an extra comma at the end of your myCopy string (after D31).
Get rid of it, and that error will go away.

 

[danhenshy23]

Simple as that eh! Thank you

 

[Joe4]

You are welcome.

Hopefully, you now learned a debugging technique you can make use of going forward - use the method described to find which line is causing the error, and focus on that.