Comments for Excel & VBA

Comment on Convert cell value to UPPERCASE or lowercase.. by Umair Aslam 22

Umair Aslam 22 — Sat, 25 Jul 2009 04:31:58 +0000

Thanks good work but how can only seleceted cells b converted into upper or lower case if no cell is selected .

Comment on Round a figure to the nearest / highest 10,100,500 etc. by hwsris

hwsris — Mon, 08 Jun 2009 04:54:49 +0000

Jon Peltier very helpfull for me.
I use the “CEILING(ABS(A2),500)*-1″ in IF formulas.

Thanks.

Comment on Round a figure to the nearest / highest 10,100,500 etc. by Jon Peltier

Jon Peltier — Sun, 07 Jun 2009 13:46:43 +0000

This doesn’t round the value to the nearest X, it rounds it up to the next highest X. This does what your title claims:

=ROUND(A2/500,0)*500

Comment on Round a figure to the nearest / highest 10,100,500 etc. by hwsris

hwsris — Sun, 07 Jun 2009 12:22:19 +0000

Thanks
Any Idea for A2 is Negative Number?

Comment on Simple loop trough rows.. by admin

admin — Fri, 29 May 2009 11:33:42 +0000

Thanks, I’ll look into the array option!
So far the looping down has worked for me, but you never know when it comes in handy!

Cheers!

Comment on Convert cell value to UPPERCASE or lowercase.. by admin

admin — Fri, 29 May 2009 11:32:32 +0000

Thanks for the tips Michael,

Yes, the second was intended for Lowercase..
Could you tell me why to use the x1TextValues instead of xlCellTypeConstants? As a learning curve?

Comment on Simple loop trough rows.. by Jon Peltier

Jon Peltier — Thu, 28 May 2009 13:02:17 +0000

It’s a quicker comparison to use

While Len(ActiveSheet.Cells(Row, 2).Value) > 0

Also, depending on the reason for looping, it might be better to loop from the bottom to the top, or to load the whole range into a VBA array and do the work there (limiting the calls crossing the VBA-Excel interface).

Comment on Convert cell value to UPPERCASE or lowercase.. by Michael Pierce

Michael Pierce — Wed, 13 May 2009 21:23:13 +0000

I’d also suggest using the constant xlTextValues instead of the hard coded value of 2 above.

Comment on Convert cell value to UPPERCASE or lowercase.. by Michael Pierce

Michael Pierce — Wed, 13 May 2009 21:12:18 +0000

Was the second item intended to be LowerCase() instead of repeating the UpperCase() macro?

Comment on Show a list in Excel with all files in a specific folder by 5 Excel Blogs You May Not Have Known About - Code For Excel And Outlook Blog

5 Excel Blogs You May Not Have Known About - Code For Excel And Outlook Blog — Tue, 28 Apr 2009 11:02:52 +0000

[...] ExcelTips has VBA code samples for routine tasks you may need to perform. In Show a list in Excel with all files in a specific folder, a technique is demonstrated for looping through a folder on the hard drive and making a list of [...]