Fix Broken Code

[Nick72310]

I could use some help fixing my broken code, there are some missing pieces that I'm not quite sure how to code. I try and explain what I am trying to accomplish in my code given below.

I am using two named ranges ([OIB_STD_OPS] & [Product_Line_To_Dept_Num]). I defined these using Name Manager. [OIB_STD_OPS] has duplicate data. I do not want these added line after line, but I do want the number captured in column D (See image below).
One thing to note is that the array [OIB_STD_OPS] will not always be sorted by department sequence number.

Capture.JPG

Sub Quick_Ref()
Dim i
Dim j
Dim k 'Selected Department
Dim d As Range 'Number of departments
Dim rd 'Number of rows with selected departments in named range [OIB_STD_OPS] (Could have multiple departments in product line)

d = Range("[Product_Line_To_Dept_Num]").Rows.Count
rd = ??? 'Number of rows with selected departments in named range [OIB_STD_OPS] (Could have multiple departments in product line)

For j = 1 To d
    k = WorksheetFunction.HLookup(Range("B1"), [Product_Line_To_Dept_Num], j + 1, 0)
    
        For i = 1 To rd
            Range("Cells(3+i, 1)") = ??? '"Code" with selected "variable k" department
            Range("Cells(3+i, 2)") = WorksheetFunction.VLookup(Range("Cells(3+i, 1)"), [OIB_STD_OPS], 2, 0)
            Range("Cells(3+i, 3)") = WorksheetFunction.VLookup(Range("Cells(3+i, 1)"), [OIB_STD_OPS], 3, 0)
            Range("Cells(3+i, 4)") = ??? '# of rows "Code:(Cells(3+i, 1)" appears in [OIB_STD_OPS]
        Next i
Next j
End Sub
'--------------------------
Sub Delete_Ref()
???
End Sub

[Paul_Hossler]

You didn't say what part wasn't working

That's not really the way I prefer to use named ranges. This is a little simpler

I put data in A1:A10 and called it 'TestName'

 
Sub TestNames()
    Dim i As Long
    
    MsgBox Range("TestName").Address
    MsgBox [TestName].Address
    MsgBox Range("TestName").Cells(3, 1).Value

    'fails ------------------------------------------------
'    For i = 1 To 10
'        MsgBox Range("Cells(i, 1)")
'    Next I

    For i = 1 To 10
        MsgBox Cells(i, 1).Value
        Cells(i, 1).Value = Cells(i, 1).Value & "abcdef"
    Next i

End Sub

[snb]

Sub M_snb() 
  sn=[OIB_STD_OPS].resize(,[OIB_STD_OPS].columns.count+1)
  
  With CreateObject("scripting.dictionary")
     For j = 1 To UBound(sn)
       sp = Application.Index(sn, j)
       If .exists(sn(j, 1)) Then sp(UBound(sp)) = .Item(sn(j, 1))(UBound(sp)) - (.Item(sn(j, 1))(UBound(sp)) = "") + 1
       .Item(sn(j, 1)) = sp
     Next
     
     Cells(20, 1).Resize(.count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
  End With
End Sub

[Nick72310]

Thank you for the reply.
Technically, none of it works. I haven't been able to test it because I don't know how to define "rd".

But all the code with ???, I need help with.

You didn't say what part wasn't working

That's not really the way I prefer to use named ranges. This is a little simpler

I put data in A1:A10 and called it 'TestName'

  
 Sub TestNames()
     Dim i As Long
     
     MsgBox Range("TestName").Address
     MsgBox [TestName].Address
     MsgBox Range("TestName").Cells(3, 1).Value

     'fails ------------------------------------------------
 '    For i = 1 To 10
 '        MsgBox Range("Cells(i, 1)")
 '    Next I

     For i = 1 To 10
         MsgBox Cells(i, 1).Value
         Cells(i, 1).Value = Cells(i, 1).Value & "abcdef"
     Next i

 End Sub

[Nick72310]

Thanks for the reply.
This code kind of works. It does retrieve data from my [OIB_STD_OPS] table. However, it does not filter based on named range [Product_Line_To_Dept_Num]. Also, the data retrieved shows nearly all my columns in [OIB_STD_OPS], when I only want the few specified above in my code.

Sub M_snb() 
  sn=[OIB_STD_OPS].resize(,[OIB_STD_OPS].columns.count+1)
  
  With CreateObject("scripting.dictionary")
     For j = 1 To UBound(sn)
       sp = Application.Index(sn, j)
       If .exists(sn(j, 1)) Then sp(UBound(sp)) = .Item(sn(j, 1))(UBound(sp)) - (.Item(sn(j, 1))(UBound(sp)) = "") + 1
       .Item(sn(j, 1)) = sp
     Next
     
     Cells(20, 1).Resize(.count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
  End With
End Sub

[snb]

You have my permission to adapt the code to your requirements.

[SamT]

You have declared "d" as a Range, but are using it as a number.
You have declared "k" as a Variant, and set it to a number or a numerical string (?), then you don't use it.

I, for one, cannot figure out either the structure of your Named Ranges, or what you are trying to do with them.

Maybe you are trying to list the unique "Codes" (Cut, Sand, & Fin) and the Departments they belong to (1234 & 43231), and the number of instances each "Code" is used.

[Nick72310]

k is indented to be used in the first line of my for I= 1 to rd loop. (As noted in my comment within the code)

My named ranges are just tables. [OIB_STD_OPS] is a table with my "Code" in the first column. The same code I want to retrieve, like seen in the image above. [Product_Line_To_Dept_Num] is a table that has the product lines in the first row, and the corresponding numbers below them. So referring to my image, Cell B1 is the product line I want to look up in named range [Product_Line_To_Dept_Num]. In return, it will retrieve department numbers 1234 & 4321 (See image).

I want the retrieved code to be based on the selected department, from [Product_Line_To_Dept_Num] (Cell B1). So every code with that department number should be retrieved.

You have declared "d" as a Range, but are using it as a number.
You have declared "k" as a Variant, and set it to a number or a numerical string (?), then you don't use it.

I, for one, cannot figure out either the structure of your Named Ranges, or what you are trying to do with them.

Maybe you are trying to list the unique "Codes" (Cut, Sand, & Fin) and the Departments they belong to (1234 & 43231), and the number of instances each "Code" is used.

[SamT]

Please stop quoting every post.