# Share your favourite posts



## Jon von der Heyden (Oct 12, 2007)

Hi   

I've just been browsing my library of favourites.  I have a fair few in there but it could do with some more.  Only too often do I browse this site with a particular search parameter, now I'm hoping to learn more cool tricks by visiting your top 5 favourite MrExcel posts:

Here are my (current) favourite 5:

Richard Schollar demonstrates VBA method to delete rows in a range based on a criteria.

Damon Ostrander's UDF to generate random numbers between x and y and yet exclude numbers in a specified range.

How to consolidate multiple workbooks into a singular workbook with multiple sheets.

Barry Houdini's explanation of how to use SUMPRODUCT() to calculate variable rates and commissions.

Barry Houdini's explanation on how to return the week number in a tax year.

Best Regards
Jon


----------



## NateO (Oct 12, 2007)

Dang nab-it! I didn't make the top 5?! What you want me to talk about? Put me in coach, I can play!

With respect to the following:



> Barry Houdini's explanation of how to use SUMPRODUCT() to calculate variable rates and commissions.


No disrespect to Barry, he's a very appreciated and respected member here, but be careful with this one. I've noticed that you have cited Barry as the author of the following:

http://www.mcgimpsey.com/excel/variablerate.html

Just to be sure, the author is actually John McGimpsey, Microsoft Excel MVP.


----------



## Jon von der Heyden (Oct 12, 2007)

Arrr never mind Nate    I sure you make many other's top 5   

I think your posts are typically around topics well beyond my current capabilities.  :wink: 

You are absolutely right, thanks for the correction.   

Jon


----------



## NateO (Oct 12, 2007)

No worries, I was kidding around about my posts...

I like this thread though, it might give us some ideas/good reading. 

My top five can be found in here, somewhere:

http://www.mrexcel.com/board2/viewforum.php?f=13

But I suspect it's missing a couple... :wink:


----------



## Greg Truby (Oct 12, 2007)

> Dang nab-it! I didn't make the top 5?! ...Put me in coach, I can play!
> ~Nate


Not to worry, lad.  You show up on one of my all time favorites.  Nate and I had some nice back and forth going on and I learned a thing or two.  And then we got humbled because neither of us fancy-pants hotshots thought to ponder the idea that what the OP was calling dashes were, in fact, zeros _formatted as dashes._


----------



## NateO (Oct 12, 2007)

Wow, three years ago.

Interesting to look back and note how my coding style/preferences have changed over the years...

Okay, in good faith, I've tried recreate 5 posts/threads that impacted me:

Dennis' post on trouble shooting ADODB:

http://www.mrexcel.com/board2/viewtopic.php?p=316665#316665

I was using this yesterday while querying a closed CSV file with ADODB (very fast/slick):


```
For i = 0 To cn.Errors.Count - 1
    With cn.Errors(i)
        Debug.Print "Error " & CStr(i), "Description " & .Description, _
            "Number " & CStr(.Number), "NativeError " & CStr(.NativeError), _
            "Source " & .Source, "SQLState " & .SqlState
    End With
Next i
```
Actually, I had no ADO connection errors, so I never really did test this rewrite...

Dan's Outlook post/thread:

http://www.mrexcel.com/board2/viewtopic.php?t=8251

Aladin's post on depedant data validation cells:

http://www.mrexcel.com/board2/viewtopic.php?p=154#154

Andrew Poulsom shows me to think outside of the box (and not to underestimate the mysterious powers of SumProduct()):

http://www.mrexcel.com/board2/viewtopic.php?p=267491#267491

rrdonutz showed me something I didn't think was possible, summing numbers in a string:

http://www.mrexcel.com/board2/viewtopic.php?p=248645#248645

So there's five. 

What post had the biggest impact on me, as an aspiring Excel user?

Look no further than Ivan F Moala's post, right here:

http://www.mrexcel.com/board2/viewtopic.php?p=3124#3124

That for me was an eye-opener, the imagined ceiling had been removed, the sky had become the limit!

This is outside the scope of this thread, but while we're at it, here's a humourous thread I took part in:

http://www.mrexcel.com/board2/viewtopic.php?p=68090#68090



> BANG! Right between the eyes!


----------



## Richard Schollar (Oct 12, 2007)

My favourite is by Barry and it's on ExcelForum not here (and a really recent one to boot!):

http://www.excelforum.com/showpost.php?p=1836814&postcount=7

His date formulas are, for me, the dog's testicles.


----------



## NateO (Oct 12, 2007)

Dare I ask...

Richard, aren't these generally discarded?


----------



## barry houdini (Oct 12, 2007)

> My favourite is by Barry



who's this daddylonglegs geezer??


----------



## NateO (Oct 12, 2007)

> Not to worry, lad.  You show up on one of my all time favorites.  Nate and I had some nice back and forth going on and I learned a thing or two.


That's a pretty wild thread now that I reread that, Greg! All sorts of different, moving parts to be found there. Thanks for the ping! 

Have nice weekend everyone, I'm off to watch NCAA Midnight Madness!!


----------



## barry houdini (Oct 12, 2007)

Thanks for the namecheck, Jon....and Richard.

For me there are almost too many favourites to pick from, especially from Aladin, Domenic, Hotpepper and Fairwinds.

I like this non-duplicate random number formula from pgc

and this recent Boller contribution

It's strange to go back and look at things you posted in the past....I think I can improve on that financial year formula, perhaps I'll go back and edit...


----------



## Oaktree (Oct 12, 2007)

For me, it's

VLOOKUPNTH (from Peter Moran via Zack) http://www.mrexcel.com/board2/viewtopic.php?t=116279

The ancient thread about printing to a PDF (shivers at the thought of having to do that again [pre xl2007]) from SteveF http://www.mrexcel.com/board2/viewtopic.php?t=119332

Any of Aladin's posts that involve a 9.99999999999999E+307, a SUBTOTAL within a SUMPRODUCT, or a MATCH(2,1/...) really, any of Aladin's posts in general

Ekim's combination generator here http://www.mrexcel.com/board2/viewtopic.php?t=65520

But, my all-time favorite post was Fairwinds' "that's sad".


----------



## Richard Schollar (Oct 13, 2007)

> Dare I ask...
> 
> Richard, aren't these generally discarded?



There's a reasonable explanation here:

http://www.phrases.org.uk/meanings/dog's ********.html

Ah, reading Viz in the late Eighties...


----------



## Richard Schollar (Oct 13, 2007)

Not a favourite thread as such, but just great programming practice (for VBA) is espoused by the Natester in just about every code post he makes:

Using "Let" for scalar variable assignments rather than simply not using it (it's optional unlike "Set" with variable object assignments) eg:


```
Dim myLong As Long
Let myLong = 65000
```

Trivial?  Maybe, but when you (or at least when I) start(ed) investigating custom Classes, it just made all the Property Let/Set/Get statements make _so much_ sense.


----------



## Lewiy (Oct 13, 2007)

Quite a recent one this, but some awesome outer world powers are at work here I'm sure:
http://www.mrexcel.com/board2/viewtopic.php?t=296159&start=10


----------



## barry houdini (Oct 13, 2007)

I hadn't seen that one before but.....

....to "AND" 2 8-bit binary numbers as required, rather than use

=DEC2BIN(SUMPRODUCT(MID(A1,9-ROW(1:8),1)*MID(A2,9-ROW(1:8),1)*2^(ROW(1:8)-1)),8)

you could use this formula

=TEXT(SUBSTITUTE(SUBSTITUTE(A1+A2,1,0),2,1),"00000000")


----------



## Richard Schollar (Oct 13, 2007)

> =TEXT(SUBSTITUTE(SUBSTITUTE(A1+A2,1,0),2,1),"00000000")




!!!  Oh stop it - I'm going to be really disappointed now if I see you just doing a 'regular' Sumproduct or the like


----------



## NateO (Oct 13, 2007)

> Not a favourite thread as such, but just great programming practice (for VBA) is espoused by the Natester in just about every code post he makes:
> 
> Using "Let" for scalar variable assignments rather than simply not using it (it's optional unlike "Set" with variable object assignments) eg:
> 
> ...


Hey, thanks Richard.

I've had people ask me why I actually use the Let Statement in my procedures and functions.

Bruce McKinney makes my argument, here:

http://vb.mvps.org/hardcore/html/let.htm

Except, unlike Bruce, I happen to like and use this "pedantic nonsense". The few keystrokes required to assign an Intrinsic Data Type for me is worth it.


----------



## pgc01 (Oct 14, 2007)

> I hadn't seen that one before but.....
> 
> ....to "AND" 2 8-bit binary numbers as required, rather than use
> 
> ...



Barry, why do you think your formula is better than mine?

Is it just because it's cleverer, smaller and more efficient?


----------



## barry houdini (Oct 14, 2007)

> Barry, why do you think your formula is better than mine?
> 
> Is it just because it's cleverer, smaller and more efficient?



Yes   

.....although don't get me wrong, I agree with the sentiments expressed by jonmo and Lewiy, the SUMPRODUCT approach particularly comes into its own, I think, for your follow-up to do the binary "AND" directly in decimal....


----------



## Lewiy (Oct 14, 2007)

There always has to be someone who comes up with a better way of doing things!!


----------



## Jon von der Heyden (Oct 15, 2007)

Wow, this has attracted some great responses.  

I also frequently refer to Aladin's posts on dependent data validation.  This is something I use almost daily since our reporting is compiled according to a complex organisation hierarchy.

I know I have already listed my favourite 5, but another I use regularly in my work is adding values up from every nth row.  See  here .
This was my introduction to MOD()...

Regards
Jon


----------



## Krishnakumar (Oct 16, 2007)

One of my favourites  bosco's contribution on EVALUATE


----------



## daniels012 (Oct 16, 2007)

This link was a very interesting one that I use every once in awhile.  It passes an image using a formula.
Kady added a very interesting twist to make this work:
http://www.mrexcel.com/board2/viewtopic.php?t=85068&postdays=0&postorder=asc&&start=20

Michael


----------



## hatman (Oct 26, 2007)

New addition to my Favorites list is this gem where both BrianB and Rigt_Click discuss SendKeys methods (API's versus the VBA Function).  Still waiting to see who else weighs in on this topic... but even if the thread ends here, it's a HUGE resource for several approaches to sending keystrokes/messages to other applications.

Huh, I guess Tom, gets 2 for 2 with this other thread that I have had occassion to use often enough that I have actually printed out the code, annotated it, appended several pages of my own commentary and variations, and filed in my cabinet.


----------

