# Search:

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

Page 1 of 6 1

1. ## Thread: Formula to find possible combinations to a target amount

by Eric W
Replies
2
Views
122

### 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. ## Thread: 2d array pasting into single column

by Eric W
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. ## Thread: 2d array pasting into single column

by Eric W
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. ## Thread: 2d array pasting into single column

by Eric W
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. ## Thread: 2d array pasting into single column

by Eric W
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. ## Thread: One Column Confusion!

by Eric W
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. ## Thread: How to indicate tell Excel that if someone has accomplished all training on certain training, mark an "X" in a box?

by Eric W
Replies
20
Views
1,236

### 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. ## Thread: Find next whole number in list

by Eric W
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. ## Thread: Repeat Values and Range of Dates Based on Cell Values

by Eric W
Replies
13
Views
304

### 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. ## Thread: VBA get target sum from the grid of 7*9

by Eric W
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. ## Thread: Find next whole number in list

by Eric W
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. ## Thread: How to find all combinations within a constraint

by Eric W
Replies
4
Views
381

### 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. ## Thread: Compare multiple column using dictionary VBA

by Eric W
Replies
6
Views
141

### 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. ## Thread: VBA get target sum from the grid of 7*9

by Eric W
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. ## Thread: Compare multiple column using dictionary VBA

by Eric W
Replies
6
Views
141

### Re: Compare multiple column using dictionary VBA

Happy to help. :cool:
16. ## Thread: Help, i'm stumped

by Eric W
Replies
1
Views
85

### Re: Help, i'm stumped

Welcome to the Board!

=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)

by Eric W
Replies
3
Views
67

18. ## Thread: VBA get target sum from the grid of 7*9

by Eric W
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. ## Thread: Running a Macro based on a Value through a Command Button

by Eric W
Replies
3
Views
67

### 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. ## Thread: If cell has a currency with cents, show that value otherwise show the #

by Eric W
Replies
3
Views
58

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

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

by Eric W
Replies
6
Views
141

### 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. ## Thread: If cell has a currency with cents, show that value otherwise show the #

by Eric W
Replies
3
Views
58

### 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. ## Thread: VBA get target sum from the grid of 7*9

by Eric W
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. ## Thread: nth Composite Number UDF

by Eric W
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. ## Thread: Using Arrays to populated Matrices and other data

by Eric W
Replies
4
Views
158

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