Run Time Error '1004' object defined error

[VBAlife]

Hello Experts,

I 'am novice at vba programming , however am stuck with VBA code to update the Product names from two columns.
I have achieved the first task of updating product names if it exists in first column, now am trying to update the product names after matching the column names from second column.

But am getting stuck in the below line of the code

.Range(mgprodname).AutoFilter Field:=1, Criteria1:=TextToFind

with run time error 1004 object defined error.

I have also attached a sample excel file in the thread.

Kindly help me in fixing the error.

Sub MM2()
Dim r As Long, ws As Worksheet
Dim prodname As Long
Dim prodytpe As Long
Dim lastRow As Long
Dim lastrow1 As Long

prodtype = WorksheetFunction.Match("Producttype", Range("1:1"), 0)
Cells(1, prodtype + 1).EntireColumn.Insert
Cells(1, prodtype + 1) = "Product Names"

prodname = WorksheetFunction.Match("Product Names", Range("1:1"), 0)

Set ws = Sheets("Updateproductnames")
lastRow = ws.Cells(Rows.Count, prodtype).End(xlUp).Row

For r = 2 To lastRow

    If ws.Cells(r, prodtype).Value = "Desktops" Then
        ws.Cells(r, prodname).Value = "Desktops"
        ElseIf ws.Cells(r, prodtype).Value = "Laptops" Then
        ws.Cells(r, prodname).Value = "Laptops"
        ElseIf ws.Cells(r, prodtype).Value = "Workstations" Then
        ws.Cells(r, prodname).Value = "Workstations"
        ElseIf ws.Cells(r, prodtype).Value = "Printers" Then
        ws.Cells(r, prodname).Value = "Printers"
    End If
   
    Next r
   
        With ActiveSheet
   
   
    TagRows "Attach", "Printers"
    TagRows "Commercial Desktops", "Desktops"
    TagRows "Commercial Notebooks", "Laptops"
    TagRows "Consumer Desktops", "Desktops"
    TagRows "Consumer Notebooks", "Laptops"
    TagRows "Managed Services", "Printers"
    TagRows "LJ Supplies", "Printers"
    TagRows "IJ Supplies", "Printers"
    TagRows "LJ Supplies", "Printers"
    TagRows "Workstations", "Workstations"
    TagRows "Scanners", "Printers"
    TagRows "IPG Services Support", "Printers"
    TagRows "Supplies", "Printers"
 
    End With

    End Sub
   
  Sub TagRows(TextToFind As String, TagWithText As String)
   
    Dim mgprodname As Long
    Dim lastrow1 as long   
   
    With ActiveSheet
   
    mgprodname = WorksheetFunction.Match("ManagementProductnames", Range("1:1"), 0)
 
        'filter the column for "TextToFind"
      .Range(mgprodname).AutoFilter Field:=1, Criteria1:=TextToFind
        'find the last row
    lastrow1 = .Range(mgprodname & Rows.Count).End(xlUp).Row       

        'if any visible rows, fill in the new comment "TagWithText"
        If lastrow1 > 2 Then
          .Range(.Range(prodname), .Range(prodname & lastRow1)).SpecialCells(xlCellTypeVisible).Value = TagWithText
        End If
       
        .Range(mgprodname).AutoFilter Field:=1 'clear the filter
    End With
    ActiveSheet.AutoFilterMode = False
 
End Sub

 

[Jagdev Singh]

Hi

The error is because you are not following proper syntax for applying autofilter.

Your code should be somewhat like this.

Worksheets("Updateproductnames").Range("A1").AutoFilter _

field:=1, _

Criteria1:="Monitor", _

VisibleDropDown:=False

Regards,
JD

 

[VBAlife]

Hi

The error is because you are not following proper syntax for applying autofilter.

Your code should be somewhat like this.

Worksheets("Updateproductnames").Range("A1").AutoFilter _

field:=1, _

Criteria1:="Monitor", _

VisibleDropDown:=False

Regards,
JD

Thanks for the update could you please confirm where I have to change the line of code to get this done, before it worked by just giving the Column index number like BX, BY column, it is not working now as I have mentioned the range name in the filter column. Please confirm where I have to do the changes

 

[Jagdev Singh]

Hi

You should change.

.Range(mgprodname).AutoFilter Field:=1, Criteria1:=TextToFind

With

Worksheets("Updateproductnames").Range("A1").AutoFilter _

field:=1, _

Criteria1:="Monitor", _

VisibleDropDown:=False

To find the LastRow you can use this code as well


lastrow1 = .Range(mgprodname & Rows.Count).End(xlUp).Row

With

lastrow1 = Worksheets(“Updateproductnames”).cells(Rows.count, “A”).End(Xlup).Row

Regards,
JD

 

[NARAYANK991]

Hi ,

See your file now.

The problem is in the line :

.Range(mgprodname).AutoFilter Field:=1, Criteria1:=TextToFind

The variable mgprodname is being declared as of type Long ; this numeric value cannot be passed to the Range object , which expects a proper address as a string e.g. A1 , AB17. Thus , when using the Range keyword , you need to pass a string variable to it.

Narayan