Search:

Type: Posts; User: Eric W; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.25 seconds.

  1. Re: Formula to find possible combinations to a target amount

    Hi,

    This problem comes up from time to time. I'm not aware of any formula to do this, but there are some programs that work. The Solver method would probably only work on a smaller number of...
  2. Replies
    8
    Views
    177

    Re: 2d array pasting into single column

    One of the members here, hiker95 , has compiled an enormous list of online resources for learning anything Excel-related, including VBA. Check out post 3 on this thread:
    ...
  3. Replies
    8
    Views
    177

    Re: 2d array pasting into single column

    Again, lots of ways to do this. Here's a slight modification of my original macro:



    Sub test2()
    Dim cols As Variant, r As Long, c As Long, r2 As Long


    cols = Array(2, 3, 4, 5, 6, 7)
    ...
  4. Replies
    8
    Views
    177

    Re: 2d array pasting into single column

    mohadin showed a few other options, which would be more efficient on large samples. Here's one more:



    For c = 2 To 7
    Range("B2:B17").Offset(, c - 2).Copy Range("K4").Offset((c -...
  5. Replies
    8
    Views
    177

    Re: 2d array pasting into single column

    When you say "write a loop" I assume you mean VBA. If so, here is a basic loop to do that:



    r2 = 4
    For c = 2 To 7
    For r = 2 To 17
    Cells(r2, "K") = Cells(r, c)
    ...
  6. Replies
    2
    Views
    74

    Re: One Column Confusion!

    You could try a macro.

    Open a copy of your workbook. Open to the sheet with the data. Press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. Paste this code in the window that...
  7. Re: How to indicate tell Excel that if someone has accomplished all training on certain training, mark an "X" in a box?

    jbirwin20 ,

    I hope you get this! I tried to send you a PM, but your inbox is full. You'll need to either clean out some messages, or open a thread I can reply to, or send me your regular email...
  8. Replies
    25
    Views
    1,183

    Re: Find next whole number in list

    Have you tried the formulas from posts 9 and 10? They do exactly what you're asking.
  9. Re: Repeat Values and Range of Dates Based on Cell Values

    If I read this correctly, a formula could work. Try putting this in B3 and drag down as far as needed.

    =IF(AND(B2=EOMONTH(B2,0),COUNTIF(B$2:B2,B2)<4),EOMONTH(B2,-1),B2)+1

    It would...
  10. Replies
    9
    Views
    266

    Re: VBA get target sum from the grid of 7*9

    I'm not entirely sure what you want, but try:



    Sub Sub97c()
    Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange As Range
    Dim s As Long, sc As String, target As...
  11. Replies
    25
    Views
    1,183

    Re: Find next whole number in list

    Maybe:

    <b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
  12. Re: How to find all combinations within a constraint

    What you're describing is a perfect example of the Knapsack problem.

    https://en.wikipedia.org/wiki/Knapsack_problem

    There is an algorithm described in that article, which could be easily...
  13. Re: Compare multiple column using dictionary VBA

    Try this:



    Sub Attendees()
    Dim s1 As Range, s2 As Range, i As Long, j As Long, mycol(1 To 5) As Variant
    Dim ix(1 To 5) As Long, mydict(1 To 5) As Object, x As Variant, y As Variant
    Dim...
  14. Replies
    9
    Views
    266

    Re: VBA get target sum from the grid of 7*9

    Try:


    Sub Sub97()
    Dim r As Long, c As Long, Dict As Object, grid As Variant, ix() As Long, outrange as Range
    Dim s As Long, sc As String, target As Long, Output(), x As Variant, y As Variant
    ...
  15. Re: Compare multiple column using dictionary VBA

    Happy to help. :cool:
  16. Replies
    1
    Views
    85

    Re: Help, i'm stumped

    Welcome to the Board!

    How about:

    =MID(" PPFFF",SUMPRODUCT(--(COUNTIFS(BPA!AK:AK,BN$4,BPA!AU:AU,$G8,BPA!$BF:$BF,{"**PP - 3**","**PP - 1**","**PP - 2**"})>0),{3,1,1})+1,1)
  17. Re: Running a Macro based on a Value through a Command Button

    Glad to help! :cool:
  18. Replies
    9
    Views
    266

    Re: VBA get target sum from the grid of 7*9

    What do you want to do with it? There's really no reason to display all 93,063 combinations. That's far too many to manually examine. If you have some way of sifting through the list and picking...
  19. Re: Running a Macro based on a Value through a Command Button

    There are several ways to do this, probably the easiest is to write another macro that does the selecting:



    Sub Email_Selector()


    Select Case Range("A1").Value
    Case "email 1":...
  20. Re: If cell has a currency with cents, show that value otherwise show the #

    Happy to help! :cool:
  21. Re: Compare multiple column using dictionary VBA

    Welcome to the Board.

    Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to open a new module. Paste the following code into the window that opens:



    Sub...
  22. Re: If cell has a currency with cents, show that value otherwise show the #

    There are a few ways to do this. Assuming you have A1 and A2 formatted as currency, you can use this formula in A2:

    =IF(INT(A1)<>A1,A1,TEXT(A1,"$#"))

    The downside is that when it is an even...
  23. Replies
    9
    Views
    266

    Re: VBA get target sum from the grid of 7*9

    It is similar to that, but this has the added constraint that you must take one value from each column. There are therefore 9^7 possible combinations, or 4,782,969. A big number, but not too bad...
  24. Replies
    6
    Views
    126

    Re: nth Composite Number UDF

    The problem is that the Worksheet function Substitute, which I used in the macro, only allows up to 32767 characters. I replaced it with the VBA Replace function, which is too bad, since it's...
  25. Re: Using Arrays to populated Matrices and other data

    The only thing that immediately comes to mind is to make sure that AvCorr is defined as Double (you can't change that on a ReDim statement). If you make it a Variant, VBA has to do some internal...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4