Posts by RichardSchollar

    Re: Conditional Directory Path From Cells


    You need to specify the attributes of what you are looking for (a directory/folder): where you have Dir(s...) replace with:


    Code
    If Dir(s...,vbDirectory)="" Then


    Otherwise you are just looking for files, not folders.


    Richard

    Re: Remove Autofilters Before Save & Close


    Change that code to:


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.AutoFilterMode Then ws.ShowAllData
    Next ws
    End Sub


    Richard

    Re: Information For The Date Function


    Quote from Six Strings
    Code
    X = Application.WorksheetFunction.WORKDAY(SomeDate, SomeNumberOfDays)


    However, in this case, I tried it and got a run-time error. For some reason WORKDAY does not appear to be available here even though I can use in a workbook. Hopefully someone else can shine some light on this.


    Hi Sixstrings


    WORKDAY is an Analysis Toolpak function, so you need to set a reference to it in your VBE project before you can use it (via Tools>References>check against atpvbaen.xls in the VBE).


    As it isn't part of the Worksheetfunction object, you just use it directly eg:


    Code
    'set ref to atpvbaen.xls first!
    
    
    MsgBox Workday(Date,5)


    Richard

    Re: Remove Autofilters Before Save & Close


    Hi


    Place the following code in the ThisWorkbook module of the workbook concerned - do this by right-clicking the little Excel icon to the left of the File menu bar item and selecting View Code - paste the following into the code module that will open:


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.AutoFilterMode = False
    Next ws
    End Sub


    Richard

    Re: Concatenate Formula Rounding Decimals


    Hi


    The most useful option would be to wrap your numbers with the Text function (and then you can specify exactly how you want the value formatted) eg:


    =CONCATENATE(0.5," of the time")


    would give you:


    "0.5 of the time"


    But


    =CONCATENATE(TEXT(0.5,"0%"," of the time")


    would yield:


    "50% of the time"


    You should be able to build that into your formula.


    By the way, using Concatenate is unnecessary - just use &s to concatenate the data. As far as I am aware using Concatenate merely uses one one of your nested formulas (the limit being 7) so should be avoided.


    =TEXT(0.5,"0%") & " of the time"


    results in:


    "50% of the time"


    Richard

    Re: Reduce the time Macro Takes To Display Results


    If it's taking time just on the PrintPreview line then the match isn't causing you a problem (why I asked you to step thru the code). Using printing functions within VBA can be slow as frequently VBA talks to the print spooler (which is slow). I am surprised it is taking so long, however. I would try setting the print area first and then activating print preview. It may not save any time, however, it is just something to try...

    Re: Selecting Which Formula Without If/then


    Hi


    If you want to limit the amount of text in your formula then you could use named formulas (ie created via Insert>Name>Define). Ultimately though, you are going to need a conditional statement to choose between the two possibilities. If you don't like an IF function then you could use CHOOSE, but they are both similar.


    Richard


    PS I have assumed you were after a non VBA solution - if it is VBA then you have other options such as encapsulating the formula within a UDF.

    Re: Increase All Formulas Row/Column References


    I'm pretty sure you can do away with the rename altogether by either using defined names or using an Index/Match formula. I am a little confused as to why the formulas need to change though - it's probably obvious but can you take me thru it step by step why the formulas that currently point to column E now need to point to column F? Why do they get shifted one column to the right?


    Richard

    Re: Convert Text To Number After Removing Special Characters


    If you are convinced you are specifying the advanced filter settings correctly and yet no rows are being hidden, it suggests that Excel views all the rows as unique. Are you only selecting one column of data for the Advanced filter, or all columns in your table - you should only select a single column.


    Richard

    Re: Convert Text To Number After Removing Special Characters


    Hi Alexandra


    Excel is limited to a numerical precision of 15 digits - subsequent digits become 0 as you have found. Retaining the codes as text is the only way you can retain the full codes once you have stripped out the other characters.


    I am intrigued why you say you can't use Advanced Filter with textual values - what problems do you encounter. Advanced Filter should (and does for me) work without problems using textual data.


    Richard