I would like to check if the sum of reach row per name is equal to the total in column Result
I could have several names/rows
I do a Read Range to read the dt with names and from col Jan to column Dec: dt_Annual
I create a second dt_Result with only column Result
First I need to check if the sum from Jan to Dec is what is written in col RESULT
Second if what is written in col RESULT is not correct, I need to update the cell.
I don’t know what is the most efficient way to do that.
With a LINQ query to check?
With a for each row and convert each col into Int32 and then, add each column?
1. Read Range (Excel file) --> dt_Annual
2. Build DataTable (dt_Result)
- Add Data Column: "RESULT"
3. For Each Row (in dt_Annual)
- Assign: sum = row.ItemArray.Skip(1).Take(12).Sum(Function(x) Convert.ToInt32(x))
- If: Convert.ToInt32(row("RESULT")) <> sum
- Assign: row("RESULT") = sum
- Add Data Row: {sum} to dt_Result
4. Write Range (Excel file) --> dt_Annual
5. If dt_Result.Rows.Count > 0
- Write Range (Excel file) --> dt_Result
Thank you both,
I will try with a For Each Row in all the dt and Convert to Cdbl (could be decimal) with Index column, not column name, because it could change. If the sum is not equal, I update the column in an assign
Output-> dt_Annual
→ Use below code in Invoke Code:
' Create new DataTable dt_Result
dt_Result= New DataTable()
dt_Result.Columns.Add("RESULT", GetType(Integer))
' Iterate through each row in dt_Annual
For Each row As DataRow In dt_Annual.Rows
Dim sum As Integer = row.ItemArray.Skip(1).Take(12).Sum(Function(cell) If(TypeOf cell Is DBNull, 0, Convert.ToInt32(cell)))
Dim resultValue As Integer = Convert.ToInt32(row("RESULT"))
If sum <> resultValue Then
' Add row to dt_Result
dt_Result.Rows.Add(sum)
Else
dt_Result.Rows.Add(resultValue)
End If
Next
