Folks,
Need help to understand why introducing a new criteria in a code which basically executes sumifs function based on criterias defined in a config file(Sum range, Crit range & Crit is defined in a master excel), doesnt work?
With existing code works perfectly for a single criteria but if introduce a new criteria (CRT_rng1) and additional range (new column in config file) add them to the code in DATA_VAL, it returns 0 instead of expected results.
Sample structure of config file is provided below, basically I would like to introduce additional sumifs criteria below file by adding two columns.
<tbody>
</tbody>
Existing code which works fine with single criteria for sumifs is as below:
I tried testing sumifs formula in my test file and it returned results which is not 0, hence I am bit baffled. Any help in pointing me where I am going wrong would be highly appreciated, thanks in advance.
Cheers!
[/FONT]
Need help to understand why introducing a new criteria in a code which basically executes sumifs function based on criterias defined in a config file(Sum range, Crit range & Crit is defined in a master excel), doesnt work?
With existing code works perfectly for a single criteria but if introduce a new criteria (CRT_rng1) and additional range (new column in config file) add them to the code in DATA_VAL, it returns 0 instead of expected results.
Sample structure of config file is provided below, basically I would like to introduce additional sumifs criteria below file by adding two columns.
File Path | Sheet Name | Row Field Name | ColField Number | Sum_COLUMN | DATA | HEADER_ROW |
C:\Users\Bumrah\Documents\Test.xlsx | Sheet1 | AAA | 8 | 24 | ZZZ | 3 |
<tbody>
</tbody>
Existing code which works fine with single criteria for sumifs is as below:
Code:
[FONT=Arial]Sub SUMIFS_CONSOL_TEST()[/FONT]
[FONT=Arial]Dim THIS_FILE As Workbook[/FONT]
[FONT=Arial]Set THIS_FILE = ActiveWorkbook[/FONT]
[FONT=Arial]config = Range("CONFIG")[/FONT]
[FONT=Arial]For i = 2 To UBound(config)[/FONT]
[FONT=Arial] Dim SOURCE_FILE As Workbook[/FONT]
[FONT=Arial] Set SOURCE_FILE = Workbooks.Open(config(i, 1), UpdateLinks:=False, ReadOnly:=True)[/FONT]
[FONT=Arial] SOURCE_FILE.Activate[/FONT]
[FONT=Arial] SOURCE_FILE.Sheets(config(i, 2)).Activate[/FONT]
[FONT=Arial] On Error Resume Next[/FONT]
[FONT=Arial] DATA_ROW = Cells.Find(config(i, 3)).Row[/FONT]
[FONT=Arial] If Err.Number <> 0 Then[/FONT]
[FONT=Arial] GoTo Error_HAND:[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] Err.Clear[/FONT]
[FONT=Arial] End If[/FONT]
[FONT=Arial] DATA_COL = config(i, 4)[/FONT]
[FONT=Arial] HEAD_ROW = config(i, 6)[/FONT]
[FONT=Arial] Dim DATA_VAL As Double[/FONT]
[FONT=Arial] Dim SUM_RNG As Range[/FONT]
[FONT=Arial] Dim CRT_rng As Range[/FONT]
[FONT=Arial] Set SUM_RNG = Range("A:A").Offset(0, config(i, 5) - 1)[/FONT]
[FONT=Arial] Set CRT_rng = Range("A:A").Offset(0, config(i, 4) - 1)[/FONT]
[FONT=Arial] DATA_VAL = Application.WorksheetFunction.[/FONT]<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">[FONT=Arial]SumIfs(SUM_RNG, CRT_rng, config(i, 3))[/FONT]
[FONT=Arial] THIS_FILE.Activate[/FONT]
[FONT=Arial] Sheet2.Select[/FONT]
[FONT=Arial] DEST_ROW = Range("A1048576").End(xlUp).[/FONT]<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">[FONT=Arial]Row + 1[/FONT]
[FONT=Arial] Range("A" & DEST_ROW).Value = config(i, 2)[/FONT]
[FONT=Arial] Range("B" & DEST_ROW).Value = config(i, 3)[/FONT]
[FONT=Arial] Range("C" & DEST_ROW).Value = config(i, 6)[/FONT]
[FONT=Arial] Range("D" & DEST_ROW).Value = DATA_VAL[/FONT]
[FONT=Arial] Sheet1.Select[/FONT]
[FONT=Arial] Sheet1.Range("G" & i) = "SUCCESS"[/FONT]
[FONT=Arial]Error_HAND:[/FONT]
[FONT=Arial] If Sheet1.Range("G" & i) = "" Then Sheet1.Range("G" & i) = "FAILED"[/FONT]
[FONT=Arial] SOURCE_FILE.Close[/FONT]
[FONT=Arial] Set SOURCE_FILE = Nothing[/FONT]
[FONT=Arial] THIS_FILE.Activate[/FONT]
[FONT=Arial]Next i[/FONT]
[FONT=Arial]End Sub
I tried testing sumifs formula in my test file and it returned results which is not 0, hence I am bit baffled. Any help in pointing me where I am going wrong would be highly appreciated, thanks in advance.
Cheers!
[/FONT]
Last edited by a moderator: