Recent content by DougAJ4

I have an open source VBA sort function here: http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/ The function can be used either from within VBA, or as a UDF to provide a dynamic sort. There is also a link to a Chip Pearson article on sorting by transfer to the spreadsheet and...

Or to put the values into a variant array, rather than a range object: With Worksheets("Customers") rngBags = Range(.Range("B1"), .Range("B1").End(xlDown)).Value2 end with Doug Jenkins http://newtonexcelbach.wordpress.com/

Yes, stick to Longs with Excel VBA. Using Integers will just slow things down as they are converted to Longs anyway. Doug Jenkins http://newtonexcelbach.wordpress.com/

and hi electricpete :) Doug Jenkins http://newtonexcelbach.wordpress.com/

I didn't read what Chip had to say about it, but when you return an array as a UDF return value it doesn't have to be the same size as the selected range. If the selected range is smaller then the excess values just won't be displayed (they are still available to the =INDEX function), or if it...

If I understand you correctly, you can assign the array to the function return value, then enter the function as an array function (press ctrl-shift-enter). Doug Jenkins http://newtonexcelbach.wordpress.com/

Groves22 - You seem to be doing a lot of work on the spreadsheet using VBA; e.g. .Range("D7:D10").Value = scrap.Sheets(1).Range("D" & j + 1 & ":D" & j + 4).Value etc I'm suggesting you could read the contents of your scap sheet(s) into an array, process that entirely within VBA, then write the...

Rather than working with range objects I'd recommend reading in the data as a variant array, working on that, then writing it back to the spreadsheet: dim BigBlockofData as variant, BigBlockofData2 as variant BigBlockofData = Range("name or address").value2 ' Do some stuff on BigBlockofData...

A few more comments: If you enter the number as a decimal (100.0) the VB Editor will change it to 100# for you. If you step through the routine and click on help when it hits the problem the help gives the reason for the problem, as given earlier in the thread. (I confess to being amazed that...

That checks if aTime is no more than 2 hours less than lastaTime. You need: If (CDate(aTime) - TimeValue("02:00:00")) >= CDate(lastaTime)) then ... Doug Jenkins http://newtonexcelbach.wordpress.com/

An array of doubles does not have any format information. You can assign the array to a range, and then apply the formatting, or use a range rather than an array in the first place. Doug Jenkins http://newtonexcelbach.wordpress.com/

Watersprite - the first thing is if you are using a named range you don't need to qualify it with the Worksheets("sheet5"). Secondly if you have a named range for the output you won't need to hard code the worksheet and location. But the advantage of using an array is that you can do all the...

What is the objection to using an array? It's much faster than working on a range, for one thing. Doug Jenkins http://newtonexcelbach.wordpress.com/

The line Cell.value = cell.formula should do what you want: Sub ConvertEq() Dim textrange As Range, cell As Range Set textrange = Range("myrange") For Each cell In textrange.Cells cell.Value = cell.Formula Next cell End Sub Doug Jenkins http://newtonexcelbach.wordpress.com/

I tried the code posted by Skip in XL2007, and it seems to work, with ribbon visible or hidden, using a simple rectangular shape. I know that the way in which arcs are specified changed in 2007. Could that have anything to do with it? Doug Jenkins http://newtonexcelbach.wordpress.com/