$$Excel-Macros$$ How to send Lotus Notes e-mail using Excel VBA ?

Hi Friends, Read here --> http://www.excelitems.com/2011/01/send-lotus-notes-email-vba-excel.html A must read for Lotus Notes and VBA users :) -- Excel VBA Interview Questions

$$Excel-Macros$$ How to Prevent Blank Cells from plotting in Excel Charts?

Hi Friends, Read this, if you want to avoid plotting of blank cells as zeroes. http://www.excelitems.com/2011/01/prevent-blank-cells-plot-chart.html Esp. go through the comment of Jon Peltier who has provided great insight to it. -- Excel VBA Interview Questions<

$$Excel-Macros$$ Re: VBA course fee and contents - Pls suggest

Hi Manish See the standard course content of Excel VBA training here : http://www.excelitems.com/2009/03/callcontact-trainer-for-excelvbavsto.html To see 19 examples of all types on OFFSET Function, click here.

$$Excel-Macros$$ Re: Please help me

Hi Sudhir, Simply use Subtraction Operator.i.e. Hyphen(-) to do this. For e.g. if A1 is 1/12/2010 and B1 is 31/12/2010, then formula would be B1-A1+1 to calculate the total days between two dates. PS: If you need business working days, then use NETWORKDAYS(A1,B1) to calculate working days betw

$$Excel-Macros$$ Excel Charts/Pivots/VBA Newsletter

Hey Friends, Here is something interesting for you. *Love is in the air*, do some automation and *save your time for your loved ones*. Hope this helps you :) 1. Collection of VBA Macros 2. Access a search Engine lik

$$Excel-Macros$$ How to use OFFSET and SUM function together?

Create Excel Dashboard using OFFSET, SUM and MATCH functions As we learned in our previous article on OFFSET function, when we increase eithe

$$Excel-Macros$$ Re: ( ) for Array Must???

Hi Nihal, While declaring array () is a must, we can ignore the lower and upper bound in declaration statement but not the brackets as the brackets tells compiler to use the variable as array and leaving few spaces in the RAM which will be reconfigurable using redim statement. Declaring an arr

$$Excel-Macros$$ Re: Variant = Array ???

Hi Nihal, Variant is not working as array here. Moreover, it's logically incorrect to assign value of a range with multiple cells to a single variable. Since A is variant, it's not throwing any error at compile time but when you tried watching the value of A, it's not showing it. The only case

Re: $$Excel-Macros$$ Fwd: Help on formula

Hi Jorge, If you want to further reduce your formula size, you can use =IF(((P3>T3)+(Q3>U3)+(R3>V3)+(S3>W3))>=2,TRUE,FALSE) I only modified Paul's formula. So, Thanks Paul. -- Excel VBA Interview Questions

$$Excel-Macros$$ Re: Rename of files in specific format

*Replace* Characters in the Filenames of a Directory Hi Ankit, This is such a simple problem and hence the solution, that I don't need to provide long texts explaining that what it does. Simply, see the figure below and y

$$Excel-Macros$$ Re: Excel Interview Q & A

Hi Ayush, It might be of some help to you. 3 Things to remember before you go for Excel VBA Job Interview 50 Excel VBA Oral Interview Questions --

$$Excel-Macros$$ Re: how to increse rows in excel 2007 from 65 k to more

Hi, You don't need to increase rows in Excel 2007/2010 to 1048576 from 65536. Also, there is no way to accomplish it. The problem you're facing is due to the use of .xls file which are of Excel 2003 or prior format. Workaround: 1. Save your .xls file as .xlsx (if there are no macros in it) or

$$Excel-Macros$$ Re: need help in excel - data merge acorrs columns

Hi Krish, Use any of the formulas mentioned below in Cell D2. 1. =CONCATENATE(A2," ",B2," ",C2) or 2. = A2 & " " & B2 & " " & C2 -- Excel VBA Interview Questions Collection

$$Excel-Macros$$ Re: Open Excel addin is unavailable

Hi Bhushan, Files section has been removed from google groups. Go to http://www.openexcel.com and click on downloads. Here is the Direct Link to Download: http://openexcel.googlecode.com/files/OPEN_XL.zip Regards Ashish Jain -- -

$$Excel-Macros$$ Sort TCP/IP Address in Excel

Are you a reporting analyst in IT firm, network engineer, networking analyst or any one who works on network data? If yes, then you ever tried to sort TCP/IP Addresses, I'm sure something like red list in this figure, would have happened and you must have desired the green list. This is the issu

$$Excel-Macros$$ HaPpY BirthdaY - Sir Paul Schreiner

Wish you many many Happy Returns of the Day :) Enjoy :) HappY BirthdaY :) Best Regards Ashish Jain eXceLiTems -- -- Some important links for excel users: 1. Follow us on TWITTER for tips

$$Excel-Macros$$ Re: required open xl addin

http://openexcel.googlecode.com/files/OPEN_XL.zip Best Regards Ashish Jain http://www.eXceLitems.com http://www.OpenExceL.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and li

$$Excel-Macros$$ JOB OPENING: Research Analyst with McKinsey, Gurgaon (SAS/Excel/VBA)

o the undersigned (*ashishj...@openexcel.com*) with the following details. 1. Current Salary 2. Expected Salary 3. Joining Time 4. Current Location For any further query please get in touch with the under signed. Thanks & Regards Ashish Jain Research Analyst, McKinsey & Co. +91--40-

$$Excel-Macros$$ Re: JOB OPENING: Research Analyst with McKinsey, Gurgaon (SAS/Excel/VBA)

Please *send your resume* either to *ashishjain @openexcel.com* or * h...@openexcel.com* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com

$$Excel-Macros$$ JOB OPPORTUNITY: McKinsey, Gurgaon #SAS #Excel #VBA

o the undersigned (*ashishjain @OpenExcel.com*) with the following details. 1. Current Salary 2. Expected Salary 3. Joining Time 4. Current Location For any further query please get in touch with the under signed. Thanks & Regards Ashish Jain Research Analyst, McKinsey & Co. +91-

$$Excel-Macros$$ MS ACCESS MACRO HELP

Hi Friends, Remember 3 years back, I started solving your MS Excel & VBA queries, today I need your expertise in MS Access. Issue: Using the VBA sub-routine, I would like to modify the 'MS Access Macro Object'. If not modification, let me know about if we could create the 'MS Access Macro o

$$Excel-Macros$$ Excel & VBA Jobs (US only)

Hi, Please check and bookmark this link for MS Excel, Access & VBA Jobs. http://jobs.openexcel.com <http://jobs.openexcel.com> *Best Regards* <http://about.me/jainashish> *Photographer**, Odd Lens Blogger, eXceLiTems.com Traveler, MastWeekend.com Developer, OpenExce

$$Excel-Macros$$ Re: how to use colour index if the sheet have backgroud color

Hi Rajesh, Refer --> http://msdn.microsoft.com/en-us/library/aa206146(office.11).aspx ColorIndex - Returns or sets the color of the border. The color is specified as an index value into the current color palette, or as a XlColorIndex constant. This property specifies a color as an index into the w

$$Excel-Macros$$ Re: Count number of columns between two values in a row

Hi Kurt, Try this: =ABS(MATCH(MAX(A1:E1),A1:E1,0)-COUNT(A1:E1)) Thanks & Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com

$$Excel-Macros$$ Re: Request: A macro to check for my winning lotto numbers . . .

Hi Jim, Forgive me, if I've not understood it correctly but you can achieve the same task using Find method. Press Ctrl+F and search the number. Why you need a macro/custom formula ? Thanks & Regards Ashish Jain McKinsey India Knowledge Center (Microsoft C

$$Excel-Macros$$ Re: Hi Experts help needed for stock entry

Hi Rajesh, I modified your code. You may try these codes: For Sheet1: Event Macro Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Columns(3).Interior.ColorIndex = xlNone Cells(ActiveCell.Row, 3).Interior.Color = vbYellow Applica

$$Excel-Macros$$ Re: userform, shape and ....ontime ....

Mr. Wallace, Thanks for your query. Well, don't bother your English is fine. I'm taking your problem and hope my answer is useful to you. Please adjust it according to your needs, I tried and it was working as per my perception. Sub abc() For i = 1 To 45 ..Width = _ ..Width

Re: $$Excel-Macros$$ Help required for formule

Hi Jagadeesh, I find Paul formula to be 100% correct as per we both are able to understand your problem. If Paul's formula is not justifying your problem completely then please elaborate your problem efficiently. Thanks & Regards Ashish Jain McKinsey Indi

$$Excel-Macros$$ Re: how dectec the mouse over an userform

Hi Alfred, You may try MouseMove event of Userform as follows: Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) '--YOUR CODE-- End Sub Thanks & Regards Ashish Jain McKinsey Ind

$$Excel-Macros$$ Validate an E-mail Address

Visit: http://www.excelitems.com/2010/11/validate-e-mail-addresses.html Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com

$$Excel-Macros$$ How to Schedule a VBA Macro?

Guys, ever wondered about setting Alarm or Scheduling VBA macros in MS Excel. Please visit: http://www.excelitems.com/2010/11/schedule-vba-macro-alarm-setting.html Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certifi

$$Excel-Macros$$ Re: Macro Commands to Create Pivot Table in Excel

HI Raj, You can use the below mention code: Focus on arguments and understand them, it's easy, you'll never forget. ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="Sheet2!R2C2:R6C6", _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:=

$$Excel-Macros$$ Re: Excel trainers?

Hi Prasanna, Contact us on 0-40-48-43 / 09986-615-632. Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com _

$$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

Hi Friends, Visit http://www.excelitems.com/2010/11/speed-up-excel-start-up-faster-excel.html on http://www.excelitems.com Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems

Re: $$Excel-Macros$$ : How to reduce the Excel File size

Hi Sundarvelan, First, the actual problem need to find out. Only then it can be solved in an efficient manner. 1. How many worksheets do you have? 2. How many rows and columns are occupied in each worksheet? 3. How many charts and pictures have been used? Reduction of file size depends on thes

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

items.com http://www.openexcel.com On Nov 25, 4:27 pm, roberto mensa wrote: > hi Ashish, > There are problems to use VBA.Environ("appdata") ? > regards > r > > 2010/11/25 OpenExcel.com <26may.1...@gmail.com> &

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

ion Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 25, 4:47 pm, "OpenExcel.com" <26may.1...@gmail.com> wrote: > Hi Robert, > > What kind of problem? > VBA.Environ("appda

Re: $$Excel-Macros$$* ***Can anyonce help me with VBA-Excel interview Questions?****

Hi Sreekanth, This is not confirmed but ya they are general Excel VBA interview questions. Might be of your help. 1. Difference between ByVal and ByRef 2. Meaning of Option Explicit and Option Base 3. Size of Integer and Long Data Type 4. Various Lookup formulas(HLOOKUP,VLOOKUP,LOOKUP,MATCH,INDEX

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

5:13 pm, roberto mensa wrote: > 2010/11/25 OpenExcel.com 26may.1...@gmail.com > > > A reference to 'Visual Basic for Applications' must be > > made in order to access VBA Object model.

Re: $$Excel-Macros$$ Re: Macro Commands to Create Pivot Table in Excel

into 2003 version and > replaces the method Add. > regards > r > > 2010/11/24 OpenExcel.com > > > > > > > > > HI Raj, > > > You can use the below mention code: > > Focus on arguments and understand them, it's easy, you'

Re: $$Excel-Macros$$ : How to reduce the Excel File size

Hi Sundarvelan, Read this article or use VBA Macro mentioned thereby:- http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http:/

$$Excel-Macros$$ SHRINK/REDUCE EXCEL FILE SIZE

Hi Friends, A useful post on reducing the excel workbook size. This may reduce the file size dependently from 10% to 500% . http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html Thanks & Regards Ashish Jain (Microsoft Certified Application S

Re: $$Excel-Macros$$ Speed up Excel Start Up (FASTER EXCEL)

gt; where come you want to run the script? > something goes wrong permissions? > > regards > r > > 2010/11/25 OpenExcel.com > > > > > > > > > Ok Robert, then what is the problem? > > > > > Thanks &

Re: $$Excel-Macros$$ Nonlinear Regression Analysis

View --> http://processtrends.com/toc_trend_analysis_with_Excel.htm Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com _

$$Excel-Macros$$ Re: Ctrl + Tilde

Hey Shreedhar, It seems some program/macro has been assigned to this key combination. To disable the key combination and return to default key combination, use: Application.OnKey "^~" Run this one line code on your colleague system and let us know, if it solves your issue or not. _

$$Excel-Macros$$ Re: Top Poster of the month Nov'10 :- OpenExcel.com (Managed by Ashish Jain)

Certified Professional) http://www.excelitems.com http://www.openexcel.com On Dec 3, 8:45 pm, Ayush wrote: > Dear Group, > > I am proud to announce that Ashish Jain who runs OpenExcel.com is the > Top Poster of the Nov'10. I encourage you all to k

$$Excel-Macros$$ Re: 'Automatic' change to Line Chart from XY Scatter Chart

That's something interesting work around. Good one Daniel :) Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com

$$Excel-Macros$$ MS Excel VBA Interview Questions & Answers

Hi Friends, Click here --> http://www.excelitems.com/2010/12/excel-vba-interview-questions.html These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This questio

$$Excel-Macros$$ Are you going for EXCEL VBA JOB INTERVIEW?

Hi Friends, Are you or your friend is going for an EXCEL VBA JOB INTERVIEW? Yes, then how can you miss these two useful links: 1. 3 Things to remember before you go for Excel VBA Job Interview http://www.excelitems.com/2010/12/excel-vba-job-interview-questions.html 2. 50 Excel VBA Oral Interview

$$Excel-Macros$$ Re: VVIMP Interview help

Hi Hanumant, Are you going for an EXCEL VBA JOB INTERVIEW? Ya, then how can you miss these two useful links: 1. 3 Things to remember before you go for Excel VBA Job Interview http://www.excelitems.com/2010/12/excel-vba-job-interview-questions.html 2. 50 Excel VBA Oral Interview Questions http://w

Re: $$Excel-Macros$$ Open Internet Explorer automatically from Excel

Hope this helps --> http://www.excelitems.com/2009/03/access-search-engines-on-browser.html. Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com __

$$Excel-Macros$$ Re: Reqd : Add in to convert Numbet to text in Excel 2007

Hi Radhe, It seems you don't know about 'Open Excel'. Download and you can do many things other than this. Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.ope

$$Excel-Macros$$ Re: cumulative sum in pivot table

Hi Alisha, Use the 'Running Total in' option in 'Value Field' settings (Go to 'Show Value as' tab) to generate the cumulative sum in pivot table. Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional)

Re: $$Excel-Macros$$ Re: Reqd : Add in to convert Number to text in Excel 2007

in is not working in Excel 2007 > > Looking forward to your kind help please > > Many thanks and best regards > Radhe > On Tue, Dec 7, 2010 at 3:57 PM, OpenExcel.com wrote: > > > > > > > > > Hi Radhe, > > > It seems you don't know about

$$Excel-Macros$$ Reqd : Add in to convert Number to text in Excel 2007

Hi Manjunath, Try this:http://openexcel.googlecode.com/files/OPEN_XL.zip Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com

$$Excel-Macros$$ Excel 2003 style menu in Excel 2007/2010

Hi, I really don't want to write this article as I'm in love with new interface of Excel 2007 and wants my blog readers and every excel user to understand the new Ribbon Menu structure than to go back and use Excel 2003 style menu in Excel 2007. However I don't want to write but I'm not writing

$$Excel-Macros$$ 17 ways to Optimize VBA Code for FASTER Macros

17 ways to Optimize VBA Code for FASTER Macros Here is a summary of the article: 1. Analyze the Logic 2. Turn off ScreenUpdating 3. Turn off 'Automatic Calculations'

$$Excel-Macros$$ Re: Method 'Calculation' of object '_Application' failed

Dear Excel User, Move your code to module and then call it in Auto_Open or Workbook_Open. ___ Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com

$$Excel-Macros$$ How to use 'Custom' Format for Numbers, Dates and Text in Excel?

Hi Friends, *What is 'Custom Format' ?:* Microsoft Office Excel provides many built-in number formats, but in some cases they do not meet our needs, we can customize a built-in number format to create our own. *Why 'Custom Format' and not 'Conditional Formatting'?:* because to cure 'Common Col

$$Excel-Macros$$ How to use OFFSET Function in Excel? (Not VBA)

To see 19 examples of all types on OFFSET Function, click here. The OFFSET function is a kind of lookup function in Excel that allows us to find a value in a table based on a particular reference point. This is different

$$Excel-Macros$$ Re: How to turn on the auto list for Excel Function?

Hi Mao, This is not possible in Excel 2003 but in Excel 2007/2010 Go to Ms Office button/File --> Excel Options and then Choose Formula and then Check the "Formula AutoComplete". In 2003, try your luck with Tools --> Options --> Edit --> "Enable AutoComplete for cell values" ---

$$Excel-Macros$$ Re: Formula of the week - share your best formula

I agree with Dave & MS-EXL_LEARNER. It's not about favourite function but the best formula, which means combination of existing functions, functions with arrays, nested functions. i.e. THE BEST PURPOSEFUL FORMULA - we ever came across. -- Excel VBA Interview Quest