Posts by burtonwang

burtonwang · May 5th 2014

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!

burtonwang · May 4th 2014

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!!

burtonwang · Mar 31st 2014

Re: Adjust PowerSet Macro to User Defined Length

Anyone? I've tried my best over here but it seems I've reached the limits of my vba knowledge.

burtonwang · Mar 27th 2014

Re: Adjust PowerSet Macro to User Defined Length

aju.thomas,

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

Code

Sub CombinationsNP(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Long
 
For i = iElement To UBound(vElements)
    vresult(iIndex) = Left(vElements(i), Cells(i, 2))
    If iIndex = p Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    End If
Next i
End Sub

Display More

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?

burtonwang · Mar 26th 2014

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:

ZFH
ZFK
ZFN
ZHK
ZHN
ZKN
FHK
FHN
FKN
HKN

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

ZFHK
ZFHN
ZFKN
ZHKN
FHKN

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:

Code

Option Explicit 
'Power Set
' Set in A1, down. Result in C1, down and accross. Clears C:Z.
Sub PowerSet()
Dim vElements As Variant, vresult As Variant
Dim lRow As Long, i As Long
 
vElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))
Columns("C:Z").Clear
 
lRow = 1
For i = 1 To UBound(vElements)
    ReDim vresult(1 To i)
    Call CombinationsNP(vElements, i, vresult, lRow, 1, 1)
Next i
End Sub
 
Sub CombinationsNP(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Long
 
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    End If
Next i
End Sub

Display More

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

Please see the attached spreadsheet for a better idea.
forum.ozgrid.com/index.php?attachment/59672/

burtonwang · Mar 17th 2014