Posts by powera86

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.

forum.ozgrid.com/index.php?attachment/55460/

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: Unable to get the Vlookup property of the Worksheet Functtion class - Run-time er

First,

I would remove the "Address" part from this section. I don't believe it is necessary:

Set Lrange = WSI.Range(Cells(2, 46).Address, Cells(LastRowI, 47).Address)

Next, i think you mixed up an "R" with an "F":

For Each c In Frange 
         
         'On Error Resume Next
         
        c.Value = Application.WorksheetFunction.VLookup(Lref, Lrange, 2, 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

Very strange indeed.

I'm hoping there's a solution available other than naming my headers to Field1 etc. Seems odd that this wouldn't function better inherently.

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?"

forum.ozgrid.com/index.php?attachment/50180/

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.

Hi there,

Does anyone know why my calculated fields in a pivot table would #NAME error out after I add new columns to my data set and refresh? I am not changing the column headings.

Thanks,

Re: Sort by column D

I believe you are missing some of the sort code:

ex:

ActiveWorkbook.Worksheets("compare").Sort.SortFields.Add Key:=Range("D2:D14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

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

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

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