Re: Excel Solver: Can This Problem Be Solved with a Linear Model?
bump
Re: Excel Solver: Can This Problem Be Solved with a Linear Model?
bump
Re: Excel Solver: Can This Problem Be Solved with a Linear Model?
Any guru's out there had any luck with this?
Even if the answer is simply "this cannot be done", I'd really appreciate some insight.
Thanks,
Hi there,
I have a general understanding of Solver, but have hit a wall with this project.
In the attached I have "loads" 1-10, and their corresponding weights in Columns A:B.
What I am asking solver to do is find me the optimal combination of loads that can be built by combining into "consolidated loads" that do not exceed 60,000 lbs.
The target that I have to determine optimality is to minimize the size of the last "consolidated load" that can be built (this is due to rate structure specific to the business).
I have been able to get solver to work under the Evolutionary model, but as we all know, this doesn't guarantee optimality. Hence I have been trying to figure out if this problem can be presented in a linear form to Solver to allow it to converge on an optimal result. My Solver/mathematic skills haven't allowed me to get any further than this.
Any and all help would be greatly appreciated.
Re: TextBox Read Only
In the properties box when you have the vba screen up. Simply click your textbox, then set the Enabled property to False.
Re: Strange problem with userform textbox changing focus back to worksheet cell.
I haven't been able to re-create the problem on my machine either.
As a possible quick workaround, could you adjust your Change events to Exit events so the code is only firing when the user leaves the textbox/changes focus? Not sure if live updating/writing is required?
Re: Use Excel for Inventory with a scanner
Vlookup will be the trick. You will need a complete table with all of your "scan numbers" in Column A and all of your corresponding product names in Column B.
Re: #NAME Error for Calculated Fields after adding columns to source data
Hi Rory,
Please see attached for a sample workbook that I have been able to re-create the issue with.
Sheet2 is the pivot table, Sheet1 is the source data.
Column G of pivot tabel (TEST FUNCTION) just divides the sum of E (column F of the pivot table) by the sum of B (column C of the pivot table).
Now if I go to the Sheet2 and insert a column inbetween column B and C and give header "TEST COL", and make the formula in C2 = sum(f2:g2) and fill down...
Now if I go back to Sheet1 and refresh...Column G all error out to "#NAME?"
Re: #NAME Error for Calculated Fields after adding columns to source data
Anyone have any luck solving this?
I am starting these workbooks from scratch in Excel 2010. Once the pivot table is created, and I have added calculated fields, if I add any other columns to the source data and refresh my pivot the calculated fields turn to #NAME errors.
From what I have been able to replicate, this only looks to be happening for calculated fields where division is being used.
Hi there,
Not sure if anyone is very familiar with using PowerPivot for Excel as I have searched the forums and didn't see much mention of it.
Anyway, it's an excellent tool and one that I have started to work with, however the major downfall for me is that most users of the tables I am generating will not have PowerPivot installed and thus won't be able to filter or perform other standard actions that they normally would on a regular pivot table.
So, I guess my question from a high level is...does anyone know how I can utilize PowerPivot to build my pivot tables, but then also be able to have other users who do not have PowerPivot installed still be able to use the pivot table as they normally would be able to.
Note: assume sharepoint, and purchasing/utilizing a powerpivot server are not acceptable solutions.
Thanks!
Good morning,
I've run into what seems a pretty silly issue, but can't seem to figure it out or find anything relevant on the net.
I have a range that has been formatted as a table. Once I've applied a filter, if I try to select all of the rows, and delete the visible rows the option is greyed out.
Is there no way, short of vba, that I can just simply delete the visible rows?
If there is no other way, would appreciate the code that will achieve the desired result.
Thanks,
Re: #NAME Error for Calculated Fields after adding columns to source data
Thanks goin4boge,
This has been created from scratch in excel 2010, and I have re-did the calculated fields numerous times but still this issue happens.
Anyone else have any info or run into this before?
Note: through some testing, it seems that the fields that are having issues are typically some form of a calculation within an iferror statement. Ex. a delta % calculation, to avoid errors when dividing by 0, I am wrapping my calculated field formula in an iferror to set the value to 0 in these instances.
Re: VBA Bubble Sort Method on Values Formatted as Currency
This seems to have fixed it...but some feedback would be appreciated
Private Sub SortIntegerArray(ByRef paintArray As Variant)
Dim lngX As Long
Dim lngY As Long
Dim intTemp
For lngX = LBound(paintArray) To (UBound(paintArray) - 1)
For lngY = LBound(paintArray) To (UBound(paintArray) - 1)
If IsNumeric((paintArray(lngY))) And IsNumeric((paintArray(lngY + 1))) Then
If CDbl(paintArray(lngY)) > CDbl(paintArray(lngY + 1)) Then
'exchange the items
intTemp = paintArray(lngY)
paintArray(lngY) = paintArray(lngY + 1)
paintArray(lngY + 1) = intTemp
End If
ElseIf IsNumeric((paintArray(lngY))) And Not (IsNumeric((paintArray(lngY + 1)))) Then
If CDbl(paintArray(lngY)) > 0 Then
'exchange the items
intTemp = paintArray(lngY)
paintArray(lngY) = paintArray(lngY + 1)
paintArray(lngY + 1) = intTemp
End If
ElseIf Not (IsNumeric((paintArray(lngY)))) And IsNumeric((paintArray(lngY + 1))) Then
If 0 > CDbl(paintArray(lngY + 1)) Then
'exchange the items
intTemp = paintArray(lngY)
paintArray(lngY) = paintArray(lngY + 1)
paintArray(lngY + 1) = intTemp
End If
End If
Next
Next
End Sub
Display More
Re: VBA Bubble Sort Method on Values Formatted as Currency
Lol...
This is what is building the array to be tested:
Function AutoFilterCriteria(lngFilterIndex As Long, objAFilter As AutoFilter) As String
Dim strCri1 As String, strCri2 As String
Dim rng As Range
Dim var As Variant
Dim lng As Long
Dim criObj As Variant
Dim blnIsDate As Boolean
With objAFilter
blnIsDate = IsDate(.Range.Columns(lngFilterIndex - 1).Cells(2).Text)
With .Filters(lngFilterIndex - 1)
If Not .On Then AutoFilterCriteria = "": Exit Function
On Error Resume Next
criObj = .Criteria1
Err.Clear: On Error GoTo 0: On Error GoTo -1
If Not IsEmpty(criObj) Or Not blnIsDate Then
If IsArray(.Criteria1) Then
strCri1 = Replace(Join(.Criteria1, ","), "=", "")
Else
strCri1 = .Criteria1
If blnIsDate Then
strCri1 = NumReplDate(strCri1)
End If
End If
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
If blnIsDate Then
strCri2 = NumReplDate(strCri2)
End If
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
If blnIsDate Then
strCri2 = NumReplDate(strCri2)
End If
End If
Else
For Each rng In .Parent.Range.Columns(lngFilterIndex - 1).Cells(2).Resize(.Parent.Range.Rows.count - 1)
If rng.Rows.Hidden = False Then
strCri1 = strCri1 & "," & CLng(CDate((rng.Text))) 'FormatDateTime(CDate(rng.Text), vbGeneralDate)
End If
Next rng
strCri1 = Mid(strCri1, 2)
var = Split(strCri1, ",")
SortIntegerArray var
ConvertValToDate var, vbGeneralDate
strCri1 = Join(var, ",")
End If
End With
End With
If Len(strCri1 & strCri2) > 2000 Then
AutoFilterCriteria = "TOO MANY CRITERIA TO DISPLAY ALL"
ElseIf Len(strCri1 & strCri2) <= 2000 Then
AutoFilterCriteria = strCri1 & strCri2
End If
End Function
Display More
And I would ideally want blanks treated as 0's
Re: VBA Bubble Sort Method on Values Formatted as Currency
Hey Rory,
I'm not actually building these arrays, I am pulling Autofilter criteria into an array, so unless I'm mistaken I don't have the Value2 option.
To your first point, if I understand, I should test the array values before I compare them, and if the Isnumeric returns false...then what, assume the non-numeric value is 0 perhaps?
Re: VBA Bubble Sort Method on Values Formatted as Currency
Doesn't seem to like the existence of blank cells, and will give a type mismatch error. Any suggestions?
Re: VBA Bubble Sort Method on Values Formatted as Currency
Perfect, worked like a charm!
Thanks as always