# Posts by Kieran

• ## Finding specific columns and counting in them

Re: Finding specific columns and counting in them

The SUMPRODUCT function can do this for you, or you can use the COUNTIF function; however without knowing the layout of your data, I can offer no specific solution.

• ## Subtotal & Min Circuit Breaker

Re: Subtotal &amp; Min Circuit Breaker

Try using a simple =SUM(G3:G8) in cell G9.

I am not sure why the subtotal formula didn't work though.

• ## VBA query: how to assign 2 Subroutines to an Active X combo box

Re: VBA query: how to assign 2 Subroutines to an Active X combo box

Try this

Code
``````Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
End Sub``````
• ## cumulative production that stops once order qty is met

Re: cumulative production that stops once order qty is met

One approach is shown in the attached.

• ## InputMsgon/Off for ALL worksheets?

Re: InputMsgon/Off for ALL worksheets?

Try this

HI,

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

• ## Countif or if statement with ? wildcard

Re: Countif or if statement with ? wildcard

The attached shows one solution using Mid()

• ## Named Range using Indirect does not work when Evaluated in VBA

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

Code
``````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``````
• ## Using RangeName instead of hard coded time in TimeValue Vba Macro

Re: Using RangeName instead of hard coded time in TimeValue Vba Macro

in the immediate window, this worked for me

print range(activeworkbook.names("thetime")).Value

it returned a value of 5.78703703703704E-05

it did fail however if "00:00:05" was entered as a text string, ensure it is entered as a time value without the quotes.

• ## Insert and Delete Rows with formula and formating in a table using VBA

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.

• ## Using RangeName instead of hard coded time in TimeValue Vba Macro

Re: Using RangeName instead of hard coded time in TimeValue Vba Macro

Try playing wit the follwoing

range(activeworkbook.names("timevalue")).Value to retrieve the value of the cell referred to by the name "timevalue" on the active sheet

• ## Picking up a range in a formula

Re: Picking up a range in a formula

My mistake - I forgot to test properly.,
See the attached workbook with the adjusted lookup range.

• ## Creating a copy of a Worksheet for each Autofilters checked

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.

http://www.mrexcel.com/archive/Pivot/ provides a good intro into pivot tables if you have not used them before.

• ## Picking up a range in a formula

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.

• ## getting the sum of filtered data without the use of formulas

Re: getting the sum of filtered data without the use of formulas

Considered using the SUBTOTAL function?

It can be placed anywhere on the spreadsheet, is automatic, and faster than any custom function.

Or, if needed, it can be used as the basis for any VBA solution.

• ## VBA Find not working!

Re: VBA Find not working!

The following code is UNTESTED, but may help.

• ## Conditional format using IF formula

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?

• ## VBA Macro Action Based On AutoFilter Criteria

Re: Autofilter Hide Column

Does the following help?

• ## Filterable Dynamic Chart Range

Re: Filterable Dynamic Chart Range

cmorgan,
Doesn't seem to be a way to do it. Refer to this.

• ## Filterable Dynamic Chart Range

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.