VBA Help - Need to build an Range Array using the Find Method -Excel 2016

[Johnny Thunder]

Hi Group,

I am working on a project and have hit a road block. I need to build code to create a range array with a "," separator for each found instance (Example: A2,A5,A8 )

Using this array, I will add the string Formula = "-Sum(" & Code & ")" to create what I need.

Currently my data is across 22 Columns.

The functional data is in column (5) starting at Row (12:100) , if the value in column 5 is "Offset" then define the value adjacent to "Offset" in Column 4 defined as my search criteria and then to look at the entire column (4) excluding the row where the offset was found and to incrementally build an array with all the found values, and then in Column 11 enter my sum formula with the range array.

The reason for doing this in this way and not just using a SumIF is due to the possibility of having two Offset columns on top of each other and having the formulas looking at each other and causing a circular reference.

I have tried several methods and the above approach seems to be the only fool proof method to get this done.


Any help is appreciated. And if any clarification is needed please shoot a reply and I will clear anything up.

 

[Akuini]

Could you post your sample data as table?
And describe what you're trying to do using that sample.