Run-time error 424: Object Required. What object am I missing?

Question

trying to learn excel vba to help me with my job. I am creating a list of a dynamic inventory of parts. I could have the same part number listed several times in my inventory, on different rows for various reasons including different quantity. I want to sum quantities of each part on another sheet, providing a "Stats" sheet to give quick look at the inventory.

That being said, different part numbers will flow into and out of the inventory continuously. I am trying to code the stats page to show only part numbers that are in inventory and sums, rather than all part numbers and a bunch of zeros.

So far I have only tried to initialize the list of part numbers on the Stats sheet. Once I have that finished I will start working on summing the quantities. The top two rows are text, titles and headings.

Private Sub RefreshStatsButton_Click()

Dim FirstRowInventory As Integer
Dim LastRowInventory As Integer
Dim FirstRowStats As Integer    
Dim PartNoIndexCount As Integer    
Dim PartNoCol As Integer    



FirstRowInventory = 3    
FirstRowStats = 3    
PartNoIndexCount = 3    
PartNoCol = 1    

LastRowInventory = Worksheets("Inventory").UsedRange.Rows.Count    

For i = FirstRowInventory To LastRowInventory    
    If Worksheets("Inventory").Cells(i, PartNoCol).Value <> Worksheets("Inventory").Cells(PartNoIndexCount - 1, PartNoCol).Value Then    
        Worksheets("Inventory").Cells(i, PartNoCol).Value.Copy 
Destination:=Worksheets("Stats").Cells(PartNoIndexCount, PartNoCol)    
        'MsgBox ("InventoryCell: " & Worksheets("Inventory").Cells(i, PartNoCol).Value & " StatsCell: " & Worksheets("Stats").Cells(PartNoIndexCount, PartNoCol))
        PartNoIndexCount = PartNoIndexCount + 1    
        Else:    
    End If    
Next    


End Sub

I expected to get a list of each part number in inventory sheet without repeats. I get

Runtime Error 424 Object Required.

I then tried adding a Msg box to help debug (commented out) but didn't get any results. Thanks for your help!

I just realized that in my if statement comparison, the right hand side of the comparison should be Worksheets("Stats"), but still doesn't solve my problem. — calebcodes, Feb 02 '19 at 15:50
I have the part numbers formatted as text to retain leading zeros. I tried changing the .Value to .Text on everything with no success. — calebcodes, Feb 02 '19 at 15:55
`.Text` is also a property of a range, thus the error should be the same. The `.Copy` needs the range object itself to function. — Vityata, Feb 02 '19 at 16:00
@Vityata can I use the range object to access a single cell? — calebcodes, Feb 02 '19 at 16:11
@calebcodes Note that you must declare your row counting variables `As Long` because Excel has more rows than `Integer` can handle. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Feb 04 '19 at 07:50

Vityata · Accepted Answer · 2019-02-02T16:00:11.670

1

The problem is that the code is trying to copy a .Value property of a Range. This is not how VBA works. E.g., it is trying that:

Worksheets("Inventory").Cells(i, PartNoCol).Value.Copy

The Value makes it throw an error. Try without it, recording a macro and seeing how it generates the code for copy and paste. Or try this minimal working code and adjust your code to it:

Sub TestMe()

    With Worksheets(1)
        .Range(.Cells(1, 1), .Cells(5, 5)).Copy
        .Range("A10").PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False

End Sub

edited Feb 02 '19 at 16:00

answered Feb 02 '19 at 15:53

Vityata

42,633
8
55
100

Thanks for your help. I recorded a macro and looked at it. I changed everything that was previously `.Cells(i, 1).Value` to `.Range("A" & i).Value` and am still having issues. – calebcodes Feb 02 '19 at 16:31
1

That is because the `Value` property doesn't have the `Copy` method. The `Range` and `Cells` objects have the `Copy` method. – Paul Ogilvie Feb 02 '19 at 16:43
OH! I understand now. It wasn't clicking. I got it to work!! Thank you!! – calebcodes Feb 02 '19 at 16:48

Run-time error 424: Object Required. What object am I missing?

1 Answers1