Finding current SUM from Table

[AilingParticiple]

I have a sheet that I am using for bulk inventory. The Table is a list of all transfers into and out of each bin.
Currently what I am trying to do is make a list of all the bins with their current totals. I found the below code on this forum which I was able to get the total from the entire column.

Is there a way to modify the code to start at a specific line in the column? And is there a way to replace the search term with a variable?
Other than changing the table/header names and replacing "In Use" with a bin number manually, everything else that I have tried just results in errors.

Sub Into_Array()
  Dim Ary As Variant
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""In Use"",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

 

[dreid1011]

I have a sheet that I am using for bulk inventory. The Table is a list of all transfers into and out of each bin.
Currently what I am trying to do is make a list of all the bins with their current totals. I found the below code on this forum which I was able to get the total from the entire column.

Is there a way to modify the code to start at a specific line in the column? And is there a way to replace the search term with a variable?
Other than changing the table/header names and replacing "In Use" with a bin number manually, everything else that I have tried just results in errors.

Sub Into_Array()
  Dim Ary As Variant
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""In Use"",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

This at least for the variable:

Sub Into_Array()
  Dim Ary As Variant
  Dim bin As String

  bin = Sheets("xx").Range("yy").Value 'Replace xx with sheet name and yy with cell address where bin identifier is entered
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#="&bin&",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

 

[AilingParticiple]

This at least for the variable:

Sub Into_Array()
  Dim Ary As Variant
  Dim bin As String

  bin = Sheets("xx").Range("yy").Value 'Replace xx with sheet name and yy with cell address where bin identifier is entered
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#="&bin&",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

I did not think to try separating it with ampersands, but this still gives me "expected list separator or )".

 

[dreid1011]

I did not think to try separating it with ampersands, but this still gives me "expected list separator or )".

I'm not sure then. I imagine there are not enough "". Might play around with how many "" around it then, but you still need & around it.

Sub Into_Array()
  Dim Ary As Variant
  Dim bin As String

  bin = Sheets("xx").Range("yy").Value 'Replace xx with sheet name and yy with cell address where bin identifier is entered
 
  Ary = Application.Index(Range("Table1[ID Number]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#="""&bin&""",Row(#)-" & Range("Table1[#Headers]").Row & ",""x"")", "#", Range("Table1[Status]").Address))), "x", False), 1)
End Sub

 

[AilingParticiple]

Ok, figured that part out, just needed some extra quotes, joining them with the & put me on the right path thanks!


Ary = Application.Index(Range("Database[GR LBS]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""" & (lookup) & """,Row(#)-" & Range("Database[#Headers]").Row & ",""x"")", "#", Range("Database[TO]").Address))), "x", False), 1)

Now I just need to figure out how to start at a specific line, if that's possible.

 

[AilingParticiple]

I think I just figured it out, at least it seems to work. Had to add a .offset to the last range and some error handling.
Below is the code with the changes I made for anyone else that may be interested.

Thanks again for the help dreid

​

On Error GoTo handler1
Ary = Application.Index(Range("Database[GR LBS]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""" & (lookup) & """,Row(#)-" & Range("Database[#Headers]").Row & ",""x"")", "#", Range("Database[TO]").Offset(start).Address))), "x", False), 1)
handler1:
   If IsError(Ary) Then Ary = 0

 

[dreid1011]

I think I just figured it out, at least it seems to work. Had to add a .offset to the last range and some error handling.
Below is the code with the changes I made for anyone else that may be interested.

Thanks again for the help dreid

​

On Error GoTo handler1
Ary = Application.Index(Range("Database[GR LBS]").Value, Filter(Application.Transpose(Evaluate(Replace("if(#=""" & (lookup) & """,Row(#)-" & Range("Database[#Headers]").Row & ",""x"")", "#", Range("Database[TO]").Offset(start).Address))), "x", False), 1)
handler1:
   If IsError(Ary) Then Ary = 0

You're welcome, and I am glad you were able to figure it out.