Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hello All,
I am working on a project and head a road block with some code, there is a super manual way to do this but I know that this can be done with some form of Join code I am just not that familiar with the best way to approach this.
Here is my problem:
I have a loop that runs down row by row on my lookups sheet for some categories, when a non-blank cell is found, the code offsets two columns over to find a numeric variable which is a count for how many codes the formula will need to process which is defined by the string cCount which can be 1 - 10. Directly below that row I do the same defining of a variable gCount
which can also have the numeric value 1 -10.
an example of what I am doing with the variables below
Scenario 1#
cCount = 0
gCount = 1
Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"
In the example above, cCount was 0 which is why there is no variable for cItem included and only a gItem variable within the formula
*cItem and gItem are the values one column over from the respective Count variables.
Scenario 2#
cCount = 1
gCount = 1
Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ")*(cData = " & cItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"
In the example above, cCount was 1 and gCount was 1 which is why both are included in the formula
Scenario 3# Where things start getting more complex
cCount = 2
gCount = 1
Formula result should look like this: FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)*(CData = " & cItem.Value & ") * (Hdata = R7C) * DataTable)/1000+SUMPRODUCT((GData = " & gItem.Offset(0, 1).Value & ") *(IOHeader=RC4)*(CData = " & cItem.Offset(0, 1).Value & ") * (Hdata = R7C) * DataTable)/1000"
In this example, cCount was 2 which requires that the formula be written 2 times to include both the original cItem value as well as the next value in the adjacent cell, gCount would be repeated in both formulas, in the event that gCount was 2 as well then the formula would need to be written to account for all variations of the cItem and gItem variations.
This scenario could be played out up to 10 values for each cItem and gItem.
So, I am able to achieve the lookups of each scenario using a case statement. I am just applying some math and adding both cCount and gCount together, and processing them that way, the problem that I face is having to write out the formula x amount of times based on the various possibilities of count.
Any direction would be great. Thanks in advance.
FYI - I was able to think of a better way of doing this but I hit a wall with that approach hence this above method.
So using the macro recorder I went to my matrix where cItem and gItem are found and copied each variable and went to my Report data and filtered the contents by each of the variables appending each variable to the filter until all values were met then just summed up the $Amounts and pasted that into the cells where my formulas would have gone. This was fast and easy problem was when I attempted to modify the recorder code and do this dynamically with variables instead of manually selecting everything the code wouldn't work.
My brain is fried at this point.... LOL
I am working on a project and head a road block with some code, there is a super manual way to do this but I know that this can be done with some form of Join code I am just not that familiar with the best way to approach this.
Here is my problem:
I have a loop that runs down row by row on my lookups sheet for some categories, when a non-blank cell is found, the code offsets two columns over to find a numeric variable which is a count for how many codes the formula will need to process which is defined by the string cCount which can be 1 - 10. Directly below that row I do the same defining of a variable gCount
which can also have the numeric value 1 -10.
an example of what I am doing with the variables below
Scenario 1#
cCount = 0
gCount = 1
Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"
In the example above, cCount was 0 which is why there is no variable for cItem included and only a gItem variable within the formula
*cItem and gItem are the values one column over from the respective Count variables.
Scenario 2#
cCount = 1
gCount = 1
Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ")*(cData = " & cItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"
In the example above, cCount was 1 and gCount was 1 which is why both are included in the formula
Scenario 3# Where things start getting more complex
cCount = 2
gCount = 1
Formula result should look like this: FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)*(CData = " & cItem.Value & ") * (Hdata = R7C) * DataTable)/1000+SUMPRODUCT((GData = " & gItem.Offset(0, 1).Value & ") *(IOHeader=RC4)*(CData = " & cItem.Offset(0, 1).Value & ") * (Hdata = R7C) * DataTable)/1000"
In this example, cCount was 2 which requires that the formula be written 2 times to include both the original cItem value as well as the next value in the adjacent cell, gCount would be repeated in both formulas, in the event that gCount was 2 as well then the formula would need to be written to account for all variations of the cItem and gItem variations.
This scenario could be played out up to 10 values for each cItem and gItem.
So, I am able to achieve the lookups of each scenario using a case statement. I am just applying some math and adding both cCount and gCount together, and processing them that way, the problem that I face is having to write out the formula x amount of times based on the various possibilities of count.
Any direction would be great. Thanks in advance.
FYI - I was able to think of a better way of doing this but I hit a wall with that approach hence this above method.
So using the macro recorder I went to my matrix where cItem and gItem are found and copied each variable and went to my Report data and filtered the contents by each of the variables appending each variable to the filter until all values were met then just summed up the $Amounts and pasted that into the cells where my formulas would have gone. This was fast and easy problem was when I attempted to modify the recorder code and do this dynamically with variables instead of manually selecting everything the code wouldn't work.
My brain is fried at this point.... LOL
