Posts by Kieran

    Re: InputMsgon/Off for ALL worksheets?

    Try this

    Re: Sorting multiple spreadsheets


    ActiveWorkbook.Worksheets("10.19") refers to a specific worksheet.
    You will have to repeat the sort for each worksheet or iterate through the Worksheets() collection (For each ws in Activeworkbook.worksheets...)

    Re: Named Range using Indirect does not work when Evaluated in VBA

    Try using this function in your VBA to calculate the range count.

    Usage : FnCountTheValues(Range("D8:D500"))

    Function FnCountTheValues(R As Range) As Integer
        For Each c In R
            If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then
                iCount = iCount + 1
            End If
        Next c
        FnCountTheValues = iCount
    End Function

    Re: Insert and Delete Rows with formula and formating in a table using VBA

    I am wondering why you are using VBA at all.

    Select your data and press Crtl-T. This will create an excel table.
    One of the proprieties of an excel table is that it will allow you to insert a row, and the formatting and formulas are inheritied on the new row.
    Table heading integrity for the table is maintained too.

    Give it a go, it looks like it will fit your needs.

    Re: Creating a copy of a Worksheet for each Autofilters checked

    An easy way to do this is to create a summary pivot table of your data.
    One of the default behaviors of a pivot table is that when you double click a value in the pivot table, it will open up a new worksheet with the values that were used to create the summary entry. provides a good intro into pivot tables if you have not used them before.

    Re: Picking up a range in a formula

    Highlight the range H1 to H9 and enter the formula =OFFSET(A2,0,MATCH(G1,$A$1:$E$1,0),9,1) and the press Crtl-Shift-Enter (instead of just enter, to create an array formula) to populate the range H1:H9 with the matching results.

    Re: VBA Find not working!

    The following code is UNTESTED, but may help.

    Re: Conditional format using IF formula

    Hi Lanky,

    you say "7 different cells that can contain either a "O" "X" or "N/A"." - I cannot see any reference to the "X' in your formula.
    If any of the referenced cells contain an "X", the result of the if statement will be "". Could this be why the formula isn't populating with the expected result?

    Re: Autofilter Hide Column

    Does the following help?

    Re: Filterable Dynamic Chart Range

    The no calculation in the chart series seems to be the case. But I am not 100% sure.

    You could however have the series refer to another named range that is the result of the calculation.
    See this page for clarification.