Posts by tinyjack

Re: Output reading reference table data for given input

Could you post a small example file of your actual data?

TJ

Re: Basic "If, Then" Formula

Your ANDs are going to always fail as you either have the wrong references ie AND(F7=$F$15,F7<$F$15) or the wrong comparisons ie AND(F7=$F$14,F7<$F$15). I would expect that you need:

=IF(F7<$F$14,"Tier 0",IF(AND(F7>=$F$14,F7<$F$15),"Tier 1",IF(AND(F7>=$F$15,F7<$F$16),"Tier 2",IF(F7>=$F$16,"Tier 3","Error"))))

HTH

TJ

ps You might to have a look at VLOOKUP as this could be another way to go.

Re: Output reading reference table data for given input

You can use SUMPRODUCT to do this:

=SUMPRODUCT((Sheet2!$A$1:$A$7=Sheet1!A1)*(Sheet2!$B$1:$B$7=Sheet1!B1)*(Sheet2!$C$1:$C$7))

Adjust references as needed and put in Sheet1!C1

HTH

TJ

Re: Worksheets: Run-time Error 9

I would make sure you have no spaces in your worksheet names that you have missed.

TJ

Re: Averaging the 4 middle numbers of 7

Read the post and not the formula, so the agreed final version is:

=AVERAGE(SMALL(A1:G1,{3,4,5,6}))

or

=AVERAGE(LARGE(A1:G1,{2,3,4,5}))

TJ

Re: flow chart required

Why do you want to flowchart the code? Usually the flowchart would be done first.

TJ

Re: Averaging the 4 middle numbers of 7

Shades,

You would need to do:

=(SUM(data)-LARGE(data,1)-SMALL(data,1)-SMALL(data,2))/4

TJ

ps I would do it this way, to reduce the chance of someone messing up an array formula.

Re: find but cannot find next

I would have a look at the help on FindNext and also please use the code tags.

TJ

Re: Address of selected Cells

As Norie said "What are you trying to do?"

What are you going to use the return of this formula for?

TJ

Re: ActiveWorkbook

It depends on what you mean by get your code to work on the whole workbook.

There is 'ActiveWorkbook' which will the currently active workbook object, which you can then apply any of the Workbook object methods to or set any of the Workbook object properties.

However, if you mean work with all the the worksheets you could maybe use something like:

[vba]
Dim wsItem As Worksheet

For Each wsItem In ActiveWorkbook.Worksheets
'Do what you want, for example
Debug.Print wsItem.Name
Next
[/vba]

HTH

TJ

Edit - No buses for 15 minutes and then along come 3

Re: Month in Sentence

You could use:

="For the month of "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"mmmm")

TJ

Re: Workbook name as string

Is wb1 already Open?

TJ

Re: Formula for TIME to DECIMAL

If your data is in time format, then tou can sum them and then multiply by 24 to give you decimal hours. You will need to format the result to General or other number format.

TJ

Re: Reverse Dynamic Chart Headache

You are very close, use this for your named range AVERAGE:

=OFFSET(Sheet1!$A$1,7,MONTH(Sheet1!$A$10)+(YEAR(Sheet1!$A$10)-2003)*12,1,-12)

and adjust your other ranges on the same lines.

HTH

TJ

Re: Count number of decimal places

Try this one:

=REPT("IIIII ",INT(B1/5))&REPT("I",MOD(B1,5))

HTH

TJ

Re: Range

Please show your revised code (with code tags) and what error is and on what line.

TJ

Re: Using the INDIRECT Function

You could use something like this:

[vba]
Public Function FindReturn(rngValue As Range, rngSeek As Range, rngReturn As Range) As Variant

'rngValue : What are you looking for
'rngSeek : Which cell are you checking on each sheet
'rngReturn : Which cell do you wish to return from the found sheet

Dim wsItem As Worksheet

FindReturn = CVErr(xlErrNA)

For Each wsItem In ThisWorkbook.Worksheets

If wsItem.Cells(rngSeek.Row, rngSeek.Column).Value = rngValue.Value Then
FindReturn = wsItem.Cells(rngReturn.Row, rngReturn.Column).Value
End If

Next

End Function
[/vba]

You would need to add some error checking (ie ranges not 1 cell) and at present it is case sensistive.

HTH

TJ

EDIT - Posted before comment about Workbooks

Re: Using the INDIRECT Function

You do not need VBA to do this, put the following in A2 and copy across:

=INDEX(Sheet1!$A$1:$A$3,MATCH(Sheet2!A1,Sheet1!$B$1:$B$3,0))

HTH

TJ

Re: VBA vLookup

Vlookup will produce a runtime error if the value being search for cannot be found. It is something that you have to trap:

[vba]
On Error Resume Next

'Put Vlookup here

If Err.Number <> 0 Then
'Not found
'Write out error
Debug.Print Err.Number
Debug.Print Err.Description
Else
'Found
End If

On Error GoTo 0 ' or On Error Goto YourErrorTrap
[/vba]

HTH

TJ

Re: Combining IF and ERROR formula

Error.Type is returning #N/A if Q12 is not an error, so instead use ISNA().

TJ