# Posts by GlennUK

If you create your Defined Names with underscore instead of space ( in fact doing Insert/Name/Create does this automatically ), then use =INDIRECT(SUBSTITUTE(cellref," ","_"))

Re: If And Syntax

Re: using column number to get time

jimmyp

For Each c In dayMonthRange

Re: If And Syntax

Code
1. If Not IsEmpty(Range("CF5") And Isempty(Range("CK5") Then

Re: using column number to get time

Well, the one that works is getting the value of a cell, and the code that fails has been written to try to get the value of a range ( but that doesn't make sense does it ). If you are trying to Set a range, you've missed off the keyword.

Re: using column number to get time

I'm not sure of exactly what you want, but this will give you the time based on a number between 1 and 48 ( assuming half-hour lumps ), assuming you want 1 to give you 0:00, 2 to give you 0:30 etc etc ...
=(number-1)/48
formatted as a time. Time/dates in Excel equate to 24 hours having a numeric size of 1, so if you have a number of half-hours you want to convert, divide by 48. ( reduced by 1, as you want the half-hours equalling midnight for 1 etc etc ).

Re: Combinations!

If you run my other code first to generate the 65 sets of 3, then run this:

you'll get what you want in columns J through R.

I have left the design of the code long-hand ( i.e. no searching loops or functions ), so that you can see the logic. You could shrink this code quite a bit by using searching loops or function code.

Cheers, Glenn.

Re: Combinations!

By combine, do you mean have sets of 3 of each 3 numbers?

Where do you get this magic number 45 from exactly?

Re: Combinations!

This code generates the numbers you want:

Re: Combinations!

I'm sorry, I still don't get what you want. I make all combinations of 3 from 9 to be 84 .... those with a greatest number difference of 6 reduces that number to 65. Are you talking about combinations of 3 from 9?

Re: Find Duplicates in a column

My pleasure Karen Re: Combinations!

How many source numbers do you have, and where are they stored ( a particular range maybe )?

Why are you grouping your permutations into groups of 3 numbers?

Re: Worksheet Functions

You are suppose to do

MRound(1234,5)

Application.WorksheetFunction.MRound(1234,5)

Re: Find Duplicates in a column

Look at the COUNTIF function. That does exactly what you want.

Cheers, Glenn.

Re: Worksheet Functions

You haven't followed the instructions ... you are supposed to refer to mround on it's own, not as part of Application.WorksheetFunction.

This is true of any other functions that are part of the Analysis Toolpak.