Posts by burtonwang

    Re: Dynamic Cell Ranges Within Formulas

    PCI this works perfectly and was exactly what I was looking for. I was weary that it would have to be done in VBA because I'm trying to make this as simple as possible. I didn't know that the offset function would solve this issue! Learning something new everyday.

    Thanks everyone!

    Is it possible to select a dynamic range in formulas based on a variable? I'm looking to see if I'm able to enter a formula in any cell, find the row# of the cell that I'm entering the formula in and designate a column to it, then move up any # of rows that match my variable to complete the range selected.

    So for example to be more specific. I have my dynamic cell which will serve as my variable: "Lag = 8" let's say in cell B1. Then I randomly click on cell D20. Is there a way I can write a formula that would instantly select A20 as the starting range for my formula of =AVERAGE(A20:), and then subtract the # of rows of my "lag" variable from that, so A20-8 rows = A12, so =AVERAGE(A20:A12). If I change the lag to 15, it would be =AVERAGE(A20:A5). If I change the lag to a negative number such as -5, then the average if I select cell E30 would be =AVERAGE(A30:A35) because if it's a negative number it moves the range to select downward.

    I would rather this not be done in VBA, purely in one single cell formula, but I don't know if it's possible to make ranges within formulas dynamic like that.
    I would appreciate any advice!!

    Re: Adjust PowerSet Macro to User Defined Length


    Thank you for the reply, I had entered your piece of code

    Perhaps I had entered this code in the wrong place, but the results only pasted the first letter "Z" of the sequence in every combination that had a Z in it. The other letters did not show up. I played around with it a little to try to adjust but I cannot find a solution. Is the 2 in the Cells(i,2) used to select the variable I enter in B1? If so, changing the variable has no effect on the code currently, as far as I can tell. Is there something I'm doing wrong?

    Hi Everyone,

    I have a power set macro that spits out EVERY combination (non-repeating) of cells I have in Column A. However, I would like to limit the results by having the code reference a number, and only return the results of that number.

    Let me explain further. In column A I have Z, F, H, K, N in cells A1:A5. The way the macro works now, it spits out each combination. What I would like to change is to put a number in B1, and have the macro only output those length of combinations. So for example, if I enter 3 in B1, the macro will output:


    Likewise, if I enter 4 in B1, the results would be:


    Also, as it stands the results are in different cells, is there any way I can get those results to be concatenated within the macro instead of manually doing it? So instead of Z in C2, F in D2, H in E2, K in F2, just have ZFHK in cell C2, ZFHN in cell C3 etc etc

    Here's the code I have so far:

    I am a novice at VBA and would appreciate any help on this. Thank you.

    Please see the attached spreadsheet for a better idea.

    Re: Find Combinations, Delimit, and Paste into new Tab

    pike, now if I'm not mistaken, the code you have in post #47 is the newest form (shorter too) that will accomplish the same thing as the previous macro (generate the combination results pasted as numbers, but starting in row J in the months tab)?

    Just to clarify as this code is infinitely shorter, so I wanted to make sure that post #47 was the entire code and not a section I'm supposed to add to the existing code we were working on.

    Re: Find Combinations, Delimit, and Paste into new Tab

    Thank you so very much for all the help, and I'm grateful I could highlight a potential issue that you all seemed to learn something from and solve.

    pike, could you explain what the code in post #53 is for? Is this code for permutations only? I will test everything right now on my end to make sure I don't have any more errors.

    Re: Find Combinations, Delimit, and Paste into new Tab

    really odd, when I open it DOES give me green triangles and say "Number Stored as Text" even with that extra code. I saved it as a new file and everything to make sure, deleted that whole sheet and saved and closed and reopened and it still comes up as such. Hahaha this is just weird. You're not seeing it on your end?

    Re: Find Combinations, Delimit, and Paste into new Tab

    Clicking on a cell in sheet2 where the values are pasted, it says number in the format in the above ribbon. But the actual cells have that green flag in the upper left of each cell with "!" and they all say, "Number Stored as Text"

    Re: Find Combinations, Delimit, and Paste into new Tab

    Apo: ohh ok so for future reference just link spreadsheets thru this site and not going thru or any other file sharing sites? That way the thread can stay in tact in case that file is removed in the future. I got it, thanks for letting me know! I'll do so from now on.

    Pike: Since the values are "general", when I try to sort them in order of smallest to largest I can't because the sort table doesn't recognize them as #'s and instead tries to sort A-Z, which doesn't do anything.

    Re: Find Combinations, Delimit, and Paste into new Tab

    Yes!! You don't realize how long I've been trying to figure this out. Pike it works perfectly! Thank you so much!

    I do have another question. The output range is stored as text. How/where can I incorporate code into the macro to select this entire range (A4 to last nonblank row and over to last nonblank column), copy, and paste into the next tab over as numbers (because the current output is stored as text)?

    Re: Find Combinations, Delimit, and Paste into new Tab

    Oh I'm sorry, I'm an idiot and included the Month headers in that count. It is 125. 3 months 5 variables (5*5*5) = 125. duh

    It works perfectly using exactly what you gave me for 3 months! I changed it to 2 months using the -2,-1,0,1,2 already there and it tells me to debug this line: Range("A4").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

    Also when testing 4 months it only gives me 125 lines? When it should be 625 rows?

    Re: Find Combinations, Delimit, and Paste into new Tab

    Oh wow! That's a ton for me to browse through, thanks! I did get erroneous results however. When doing it the long way with a Month # of 3 and values of -2,-1,0,1,2 there's 126 different combinations. The code above only returns 64.

    Why is this?