Posts by RichardSchollar

    Re: Copy/Fill Down Formula Macro Code


    Emma


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


    This line:


    Code
    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".


    Richard

    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.


    Richard

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


    Richard

    Re: Minimizing Size Of File


    Hi


    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:


    =IF(ISERROR(VLOOKUP()),"",VLOOKUP())


    is very wasteful.


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


    Richard

    Re: Compose A Date In VBA


    Hi


    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:


    Code
    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


    Richard

    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?


    Richard

    Re: Continuing Formulas In Vba


    Hello Emma


    Something like the following will work:


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


    Richard

    Re: File Format Different In Csv After Convert


    Hi


    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.


    Richard

    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?


    Richard

    Re: List Of Total Occurrences In A Column


    Hi


    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


    Richard

    Re: Save Template With New Name Off User Defined Entry


    Hi


    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:



    Richard

    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.


    Richard