Gurus,
I'm trying to sum across different tables based on day. Below is the code and always return zero. after multiple tries i switched to vlookup and it returns error.
I broke down the parts: gas is not empty, abc is not empty and matches the day after transformation, aaa =0 but ABC is in all the tables,kkk counts all the columns not a zero.
Also the variable bbb that i wanna calculate using vlookup returns error.
what is wrong with my below code? Thanks!
I'm trying to sum across different tables based on day. Below is the code and always return zero. after multiple tries i switched to vlookup and it returns error.
I broke down the parts: gas is not empty, abc is not empty and matches the day after transformation, aaa =0 but ABC is in all the tables,kkk counts all the columns not a zero.
Also the variable bbb that i wanna calculate using vlookup returns error.
what is wrong with my below code? Thanks!
VBA Code:
For Each cell In Range("a1", "a300")
If Left(cell.Value, 3) = "I/B" Then
table_day = cell.Offset(-1, 0)
For Each tbl In Sheets("ss").ListObjects
If WorksheetFunction.Text(Replace(Replace(tbl.Name, "s", ""), "_", "/"), "ddd") = table_day Then
For Each cell1 In Range(cell.Offset(1, 0), cell.Offset(27, 0))
gas = cell1.Value
abc = tbl.Name
aaa = WorksheetFunction.SumIf(tbl.ListColumns(1).DataBodyRange, "ABC", tbl.ListColumns(tbl.ListColumns.Count).DataBodyRange)
kkk = tbl.ListColumns.Count
' bbb = WorksheetFunction.VLookup(cell.Value, tbl.DataBodyRange, tbl.ListColumns.Count)
cell1.Offset(0, 1).Value = cell1.Offset(0, 1).Value _
+ WorksheetFunction.SumIf(tbl.ListColumns(1).DataBodyRange, cell1.Value, tbl.ListColumns(tbl.ListColumns.Count).DataBodyRange)
Next cell1
End If
Next tbl
End If
Next cell