Posts by powera86

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


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


    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.


    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: Sort by column D

    I believe you are missing some of the sort code:


    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

    Re: VBA Bubble Sort Method on Values Formatted as Currency


    This is what is building the array to be tested:

    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?