Posts by RichardSchollar

    Re: Copy/Fill Down Formula Macro Code


    Did you remove the spurious space that the Board software inserts for some reason in some of the posts.

    This line:

    lngLastRow = ActiveSheet. Cells(Rows.Count,"K").End(xlUp).Row

    should not have a space between "Activesheet." and "Cells" ie it should be written "Activesheet.Cells".


    Re: Minimize Size Of File

    Pretty much the only impact it will have is it will mean you have to refresh the pivot table before you do anything with it when you open up the file. It's worth doing to see if it will save you much sapce.


    Re: Filter On Pivot Table Without Showing That Field

    Hi David

    You don't even need to filter it first - select the cell containing the Page Field button (ie the one that says Indicators) - so you are NOT using the Page Field dropdown - and right-click and select Field Settings. In the dialog that will open you have the option to Hide the items you don't want to see (eg A or B & C as appropriate).


    Re: Minimizing Size Of File


    Do you have pivot tables in this workbook? If so, right-clicking on the pivot table and selecting Table Options and unchecking "Save data with table layout" can help reduce your file size.

    Having many Vlookup formulas in adjacent columns all referencing the same table source is wasteful and can be limited by judiciously using INDEX/MATCH. Having a lot of formulas of the type:


    is very wasteful.

    Using multiple array formulas and or Sumproduct/Sumif formulas can be wasteful.


    Re: Compose A Date In VBA


    Here's a function that will return the last friday in the given month 9you need to supply the year and the month (1 to 12) you are interested in:

    Function LastFridayInMonth(lngYear As Long, lngMonth As Long) As Date
        Dim lngLastDay As Long
        'define last day of the month of interest:
        lngLastDay = DateSerial(lngYear, lngMonth + 1, 0)
        'use to get last friday:
        LastFridayInMonth = lngLastDay - Weekday(lngLastDay, vbFriday) + 1
    End Function


    Re: Macro To Increase All Formulas By 1 Digit Or Character

    Conceptually speaking, you could probably do it by inserting a column at Range("A:A") as this will bump the formulas over by a column. Then you would need to convert all the formulas to text (easy enough as you can locate the formulas thru using the SpecialCells method) eg by applying a leading # or some other character. Then delete the inserted A column, and convert the 'formulas' back to true formulas by doing a find replace on #= and relace with =.

    Is that enough to get you started?


    Re: Continuing Formulas In Vba

    Hello Emma

    Something like the following will work:

    Dim lngLastRow As Long
    lngLastRow = ActiveSheet.Cells(Rows.Count,"K").End(xlUp).Row
    Activesheet.Range("L2:L" & lngLastRow).FormulaR1C1 = "=LEN(RC[-1])"


    Re: File Format Different In Csv After Convert


    How are you checking the contents of the csv file after conversion?

    The wrong way is to open the file back up in Excel (which is when you will seem Excel automatically processing the csv contents as per its General number format).

    The right way is to open the file up in a text editor such as Notepad and if you do this you will probably see the data is still held as 11-5107 etc.


    Re: Setting Workbooks As Public Object Variables

    I presume the declarations are in the same VBA Project that all the macros in which you wish to use them are located? Assuming this is the case, then potential reasons why this could happen is a loss of state. This can be caused by a varierty of things - what is the rest of your code doing?


    Re: List Of Total Occurrences In A Column


    The easiest (and best) way would be to use a pivot table - select your list (including the header) and go Data>Pivot Table and pivot Chart and follow the dialog prompts to create a pivot table. You want the name column to be both a Row field AND a data field - this will give you your unique list and the count of the number of times each appears.

    Best regards


    Re: Save Template With New Name Off User Defined Entry


    What exactly is the user-defined data? Is it just the workbook name or is there additional data that needs to be placed in the workbook?

    Assuming you just want the user to input a name to save it under then something like the following will work:


    Re: Count Of Text Associated With Date By Weekday

    Hi Upside

    For the pivot table approach, I think you need to use a helper column in the source data to return what day of the week the date represents. There's nothing wrong with the formula approach, especially given your limited dataset.