$$Excel-Macros$$ Re: auto fill start/end serial numbers and date when qty entered

2009-08-06 Thread Ajit Navre
It is very much possible, In the Workshet_Change() event, see if the target cell is QUANTITY NUMBER Cell, if yes, then if the Start And End Serial numbers cells are empty, set the values for those cells. Regards Ajit -Original Message- From: excel-macros@googlegroups.com [mailto:excel-

$$Excel-Macros$$ Re: auto fill start/end serial numbers and date when qty entered

2009-08-05 Thread Ajit Navre
One way to do it by formulas would be StartSerial - Formula - "=1"Cell Format - Custom "0" EndSerial - Formula - "=Qty" Cell Format - Custom "0" Date- Cell Format - Custom "yymmdd" Having done this, you can programmatically read the cell contents as displ

$$Excel-Macros$$ Re: Shapes in Form

2009-08-05 Thread Ajit Navre
You can download the file from that link. And then suitably modify the code to suit to your requirements. Ajit From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Deepak Rai Sent: Wednesday, August 05, 2009 11:34 AM To: excel-macros@googlegroups.com Subje

$$Excel-Macros$$ Re: Shapes in Form

2009-08-05 Thread Ajit Navre
Take a look at the link below:- http://www.andypope.info/vba/userformdraw.htm The approach is, copying the shapes from the worksheet to clipboard, and then reading the clipboard and place the clipboard BitMap/metafile on the form as an OLE Picture. Regards Ajit -Original Message- Fro

$$Excel-Macros$$ Re: Query concerning Option/Radio Buttons and their captions

2009-08-04 Thread Ajit Navre
You can only place the radio button captions to the left or right of the option indicator. The Alignment property does that. When you have placed the labels (with caption) above the option button, you can use some code like - Private Sub OptionButton1_Enter() Label2.BorderStyle = fmBorderSty

$$Excel-Macros$$ Re: Overflow error in Excel 2007 VBA

2009-07-30 Thread Ajit Navre
I think, it is due to the implicit conversion of constants in expression. In this particular case, both the quantities i.e. 4 and 1 are interpreted as integers. After multiplication, the result actually is 4 which is beyond the max-min limits of an integer - +32767 to -32768. The fix in typ

$$Excel-Macros$$ Re: form follows user activity on sheet

2009-07-25 Thread Ajit Navre
Hi, Take a look at the following link-> http://www.oaltd.co.uk/Excel/Default.htm Download the file ModelessForm.zip This one demonstrates how to make the userform modeless (thereby allowing the user to select cells on the worksheet) and make the user-form understand current selected cell(s). O

$$Excel-Macros$$ Re: Problem with duplicating a Shape and defining OnAction property

2009-07-25 Thread Ajit Navre
Yet another way would be to use Event Class, store the reference to the new Image Control in the event class object and that takes care of the Click() Event of the Image object. Regards Ajit -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On

$$Excel-Macros$$ Re: Problem with duplicating a Shape and defining OnAction property

2009-07-23 Thread Ajit Navre
Well, in this case, the shape object in question is not merely a shape, instead an OLE Object (Image Control) is contained in it. The OLE Object (Forms Image Control) exposes its own events. One way (weird one) is to duplicate the Image1 shape on Click and in the Click Event handler programmatica

$$Excel-Macros$$ Re: referenced named range on a closed workbook

2009-07-21 Thread Ajit Navre
Hi, You need to specify the file path in the Define Name -> Refers To box, e.g. =''! Regards Ajit From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Luke Mercer Sent: Tuesday, July 21, 2009 6:19 PM To: excel-macros@googlegroups.com Subje

$$Excel-Macros$$ Re: Help with Attendance Tracker & Salary Calculation

2009-06-29 Thread Ajit Navre
quot; -- Mahatma Gandhi On 6/28/09, Ajit Navre wrote: HI, Your post itself has the answer. You say that even-if the person has worked all the days in a month, salary Is paid for 30 days. That is you are assuming a month to be of 30 days. For months with >30 days in them, the calculatio

$$Excel-Macros$$ Re: Help with Attendance Tracker & Salary Calculation

2009-06-28 Thread Ajit Navre
mpt response. Let me try your solution and revert back. (I thought I was missing something, just wasnt sure if it was relatively easy or a bit complicated) Thanks once again. Ashish "Of what use is freedom if it does not include the freedom to make mistakes" -- Mahatma Gandhi On 6/28

$$Excel-Macros$$ Re: Help with Attendance Tracker & Salary Calculation

2009-06-28 Thread Ajit Navre
HI, Your post itself has the answer. You say that even-if the person has worked all the days in a month, salary Is paid for 30 days. That is you are assuming a month to be of 30 days. For months with >30 days in them, the calculation of yours (Monthly Salary * (No. of Days Present / 30)), y

$$Excel-Macros$$ Re: Mail Merge Word Excel 2003

2009-06-25 Thread Ajit Navre
HI, This is more like a word forum issue? Anyways, here is the solution. You need to do it in word than in excel. After you start mail merge and before you update all the fields (merged fields), you need to get the individual field codes in the document. Once you get the field codes

$$Excel-Macros$$ Re: Print to pdf using sendkeys

2009-06-16 Thread Ajit Navre
Do you need to use sendkeys to specify the file name? In the Adobe PDF printer preferences box, if you set the prompt for file name option to an existing folder, then all the .PDFs generated are dumped there. The files are by default saved with the primary name and .PDF suffix. You can then progra

$$Excel-Macros$$ Re: A tricky scenario that may need a function or VBA.

2009-05-12 Thread Ajit Navre
there, Sorry it took too long to return your reply. I really appreciate your prompt reply. This was exactly what the person in the class needed. I really cannot thank you enough. Kindest Regards, Rod Urand. On May 8, 8:32 am, "Ajit Navre" wrote: > Hi, > > Find attached the fil

$$Excel-Macros$$ Re: general discussion on DDE OLE ActiveX in VBA project

2009-05-07 Thread Ajit Navre
Hi, 1. Microsoft Forms 2.0 Object lib. provides the MSForms2.0 Controls. Just a few. To know more about each of the referenced components, in VBE press F2 to bring up the object browser. In the first combo-box (upper-left - All Libraries), select the appropriate one, and you will know what object

$$Excel-Macros$$ Re: { } -his is a function?

2009-05-01 Thread Ajit Navre
HI, As per the previous comments on this, it essentially is an array formula, that is applied to a range of a cells instead of a single cell. The function DfListHolidays() is designed to return an array, and not a single value. So if you enter the function as usual in a cell, DfListHolidays() sti

$$Excel-Macros$$ Re: Use VBA to determine Windows Groups permissions for a folder

2009-04-29 Thread Ajit Navre
(There is a possibility that there is a user that is EXPLICITLY assigned to a folder rather than to a Group, I would need to report that also) Does that help? I appreciate your assistance... paul _ From: Ajit Navre To: excel-macros@googlegroups.com Sent: Wednesday, Apri

$$Excel-Macros$$ Re: Use VBA to determine Windows Groups permissions for a folder

2009-04-28 Thread Ajit Navre
Hi, Take a look at the following links... http://www.15seconds.com/issue/020130.htm http://www.computerperformance.co.uk/vbscript/wmi.htm I am still not very clear about what exactly your are trying to do. That is may be because I personally haven't done this before. Still I would like to assis

$$Excel-Macros$$ Re: Need help in calculating averages based on unique values

2009-04-20 Thread Ajit Navre
Hi, Why not use PivotTables? That would be the most natural way. However, there are more than one ways to do it, a macro and/or UDF or even formulae. Pivots: make Party Name, Order No. as Row Fields. And Average Of W. Days as data field. Regards Ajit From: excel-macros@googlegro

$$Excel-Macros$$ Re: Interrogating the file name used/selected in a query of an external CSV file

2009-04-18 Thread Ajit Navre
ot;TEXT;" Is there a way just to get the file name and not the file type and path? Thanks On 17 Apr, 15:09, "Ajit Navre" wrote: > Hi there, > > If you have loaded the query with the Data->Import External Data->Import > Data option then, following code would return

$$Excel-Macros$$ Re: Copy from pdf

2009-04-18 Thread Ajit Navre
Ajay, If you have Acrobat 7.0/8.0/9.0 installed on your computer, then a code like the following will do. You need to create a reference to Acrobat.TLB. '' Dim APDF As New AcroPDDoc Dim APG As AcroPDPage Dim APOINT As AcroPoint Dim ARect As New AcroRect

$$Excel-Macros$$ Re: Copy from pdf

2009-04-17 Thread Ajit Navre
Ajay, I am working on this. Pls let me know how you are going to specify the file name, page number and the area from the page that you want to copy and paste. Also let me know if you are using any ActiveX Controls that view PDF files.. Ajit From: excel-macros@googlegroups.com [mailt

$$Excel-Macros$$ Re: Interrogating the file name used/selected in a query of an external CSV file

2009-04-17 Thread Ajit Navre
Hi there, If you have loaded the query with the Data->Import External Data->Import Data option then, following code would return the source file name ActiveSheet.QueryTables(1).Connection Then probably try to manipulate the Workbook_BeforePrint Event to include the file name in the header or fo

$$Excel-Macros$$ Re: Copy none blank Cells

2009-04-17 Thread Ajit Navre
Hi Use the following code. '' Dim Rng As Range Dim TRng As Range Dim RRow As Range Dim DestSht As Worksheet Dim DestRng As Range Dim SrcRow As Long Dim ExcludeBlankCellsInRow As Boolean ' Change This As Per Your Preference Set DestSh

$$Excel-Macros$$ Re: required help

2009-03-16 Thread Ajit Navre
You can format the cell to display the number as 1.10.2.30. Format->Cells->Number->Custom Type in the format as 0\.00\.0\.00 Now, the underlying value of the cells will continue to be 110230 (a number), but will be displayed as 1.10.2.30, you can then suitably increment or decrement par

$$Excel-Macros$$ Re: help define a variable as text

2009-01-10 Thread Ajit Navre
Try using the conversion function Cstr() or, the format function Format(). Regards Ajit -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Rob Bunocore Sent: Saturday, January 10, 2009 8:16 AM To: MS EXCEL AND VBA MACROS Subject: $

$$Excel-Macros$$ Re: Help required

2008-12-27 Thread Ajit Navre
required Can u tell me anyother freeware which helps convert pdf into word, excel and jpg files? On Sat, Dec 27, 2008 at 11:29 AM, Ajit Navre wrote: Merry X'mas, If you are using Office 2003, or 2007, then MS itself provide for a PDF AddIn. You just need to download it from the Micr

$$Excel-Macros$$ Re: Help required

2008-12-26 Thread Ajit Navre
Merry X'mas, If you are using Office 2003, or 2007, then MS itself provide for a PDF AddIn. You just need to download it from the Microsoft download center. Alternatively, try googling for PDF Freeware and you will find a lot of freewares, to name a few SoftExpansion PDFlite, EasyPDF, CutePDF a

$$Excel-Macros$$ Re: formula help

2008-12-07 Thread Ajit Navre
Try the following formula =B2+B2*INDEX({-0.05,-0.03,0,0,0,0.4,0,0.5},MATCH(A1,{2000,1000,500,499,100,9 9,50,49},-1)) Values Of A1 Operation == Upto 49 B2 * 50% 50 Unchanged 51 to 99B2 * 40% 100 to 500 Un

$$Excel-Macros$$ Re: Excel

2008-12-06 Thread Ajit Navre
Could you please send the sample excel sheet... the data below looks clumsy... Regards ajit -Original Message- From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Phani Sent: Friday, December 05, 2008 10:45 PM To: MS Excel & VBA Macros Subject: $$Excel-Macros$$ Ex

$$Excel-Macros$$ Re: Paste Error 1004

2008-12-01 Thread Ajit Navre
Ideally you should not have got the same error, had you used the code that I gave you. Still I would urge you to use the following code Worksheets("Sheet1").Cells(1, 1).Select ActiveCell.EntireRow.Copy Worksheets("Sheet2").Cells(1, 1).PasteSpecial xlPasteAll If still the error persists, then pl

$$Excel-Macros$$ Re: Paste Error 1004

2008-11-30 Thread Ajit Navre
There is a typo in the last line. However, here is a better approach to accomplish it... Sub sample() Worksheets("Sheet1").Cells(1, 1).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A1") 'Worksheets("Sheet2").Range(1, 1).EntireRow.Paste End Sub The commented line is what you have. Reg

$$Excel-Macros$$ Re: VLOOKUP with multiple dependents

2008-11-17 Thread Ajit Navre
Hi, Pls see the attached file, the formulae you see in vlookup are the array formulae, you need to press CTRL+SHIFT+ENTER, instead of ENTER. If you want to know how the formulae work, please analyze those with Tools->Formula Auditing->Evaluate Formula. Regards ajit From: excel-m

$$Excel-Macros$$ Re: scheduling with excel

2008-11-07 Thread Ajit Navre
Could you please e-mail the file so I can work on it. Need to take a look at the data. If you send the file, then also please provide in detail the criteria for processing. Ajit -Original Message- From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTE

$$Excel-Macros$$ Re: Use of Array Formula or other method to create deduplicated list of text values?

2008-11-06 Thread Ajit Navre
Hi, There certainly is an array formula, that does give you unique values, except that it shows FALSE for duplicate ones. Use the following formula... =IF(COUNTIF(INDIRECT("A1:A"&ROW()),A2)=1,A2) Assume that col A contains names of Capitals And States (cell A1 is column header). Now Select an

$$Excel-Macros$$ Re: Pls help me..

2008-11-05 Thread Ajit Navre
Please find attached the file.. there are 2 ways it can be done and both the formulas are in the file.. Ajit From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of ram bhagath Sent: Tuesday, November 04, 2008 6:27 PM To: excel-macros@googlegroups.com Subject: $$Excel-Mac

$$Excel-Macros$$ Re: Number ranges in a cell

2008-10-29 Thread AJIT NAVRE
but I just can't work out an automatic > way to get all the course weeks in separate cells. > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to th

$$Excel-Macros$$ Re: VBA to extract data from 2 excels

2008-10-26 Thread AJIT NAVRE
VBA? Appreciate anyone sharing sample VBA > source on this > > Thanks > > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send

$$Excel-Macros$$ Re: Custom Workdays Function

2008-10-24 Thread AJIT NAVRE
n(D1 as date, i1 as integer) as date > > The problem is that if the function initially returns a Saturday, I > need to return the Friday before. If it initially returns a Sunday, I > need to return the Monday after. > > Any help would be appreciated. Thanks. &

$$Excel-Macros$$ Re: $$Excel-Macros$$

2008-10-22 Thread AJIT NAVRE
expressed in > this message are those of the individual sender and no binding nature of the > message shall be implied or assumed unless the sender does so expressly with > due authority of ICICI Group. Before opening any attachments please check > them for viruses and defects." > >

$$Excel-Macros$$ Re: Macor for formatting.

2008-10-22 Thread AJIT NAVRE
- Arial > Font size - 9 > Zoom-100% > and all the sheets cell should be A1. > > Could you pls help me with this and give a short cut key and send me the > exl file. > > -- > Vinu > > > (¨`*.*´¨) Always > `*.¸(¨`*.*

$$Excel-Macros$$ Re: Paste directly values of a matrix' column or row without any loop

2008-10-22 Thread AJIT NAVRE
nd > paste it into my excel sheet if it is possible. > > If anyone could help that would be nice. > > Thanks > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.

$$Excel-Macros$$ Re: Request for Formula

2008-10-21 Thread AJIT NAVRE
TEXT(B2:B13,"[EMAIL PROTECTED]/000\/00* > ")=B2:B13,IF(ISERR(SEARCH("/",TEXT(B2:B13,"[EMAIL > PROTECTED]/000\/00*")))=FALSE,D2:D13 > ))) > > Rgds > Mayank > On Sat, Oct 18, 2008 at 7:17 PM, AJIT NAVRE <[EMAIL PROTECTED]> wrote: > >

$$Excel-Macros$$ Re: Request for Formula

2008-10-18 Thread AJIT NAVRE
in reading > the file throughly. > > Hoping that there is a Prompt reply and I Thanks each and every individual > in advance for all your suggestions. > > Regards > -- > Ahmed Bawazir > > > > > -- Thank You, Ajit Navre --~--~-~--~~

$$Excel-Macros$$ Re: Can I get Day from date in excel.

2008-10-14 Thread AJIT NAVRE
l. > > Regards > > Mayank > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroup

$$Excel-Macros$$ Re: Can I get Day from date in excel.

2008-10-14 Thread AJIT NAVRE
m any given date in excel. > > Regards > > Mayank > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googl

$$Excel-Macros$$ Re: Need Help....!

2008-10-13 Thread AJIT NAVRE
er if we give the range as B:B in the > formula? > > Thanks, > Arjun > On Oct 10, 7:17 pm, "AJIT NAVRE" <[EMAIL PROTECTED]> wrote: > > Set up the table (2nd table) with headers (required Period Slabs) and use > > the following formulas, > > Less Tha

$$Excel-Macros$$ Re: How to use several variables at once in for loops correctly

2008-10-11 Thread AJIT NAVRE
accordingly: > > Range("ES3").Select + 1 (until the column is blank) > Range("W12").Select + 11 > Range("W13":W22").Select + 11 + 11 accordingly. > > I'm sure this is very easy, but I don't know where to begin. > > > > --

$$Excel-Macros$$ Re: How to run the macro only if data available else stop.

2008-10-10 Thread AJIT NAVRE
e or DST >> refers the values from different Excel file. >> >> Regards, >> Sarfaraz Ahmed >> Excel Help <http://findsarfaraz.blogspot.com/> >> >> >> >> On Thu, Oct 9, 2008 at 10:49 AM, AJIT NAVRE <[EMAIL PROTECTED]> wrote: >> >>

$$Excel-Macros$$ Re: Need Help....!

2008-10-10 Thread AJIT NAVRE
gt; > 01-08-2008 > > > > > > Through this the end result should be out in this format > > Total > > Less than 6 months > > 6 months to 1 year > > 1 year to 2 years > > 2 years to 3 years > > 4 > > 1 > > 1 > > 1 > > 1 >

$$Excel-Macros$$ Re: How to run the macro only if data available else stop.

2008-10-09 Thread AJIT NAVRE
hould run only if report is > available to update otherwise it should stop. > > Please revert if any more information required. > Please Help as soon as possible. > > > Rgds > Mayank > On Wed, Oct 8, 2008 at 1:19 PM, AJIT NAVRE <[EMAIL PROTECTED]> wrote: > >> Oh! I

$$Excel-Macros$$ Re: Calculate workdays between 2 dates

2008-10-08 Thread AJIT NAVRE
2008 at 12:45 AM, sanj <[EMAIL PROTECTED]> wrote: > >> >> I have a date in a1; in a2 I want to add 2 working days. >> As per example: 3rd Oct, 2008 + 2 working days = 7th October, 2008 >> >> It will be a great h

$$Excel-Macros$$ Re: Calculate workdays between 2 dates

2008-10-07 Thread AJIT NAVRE
2 working days = 7th October, 2008 > > It will be a great help from u.!!! > > Thanks > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to

$$Excel-Macros$$ Re: HOW TO CALCUATE THE NO. OF YEARS BETWEEN TWO DATES

2008-10-07 Thread AJIT NAVRE
t;> Say A1 is date of Acquisition (01/04/2000) >>>> In B1 I have applied the following formulae >>>> B1=2004-year(a1). >>>> But the problem in this formula is that if an asset has been acquired on >>>> 01/01/2004 it will show zero result whereas I want 1.

$$Excel-Macros$$ Re: HOW TO CALCUATE THE NO. OF YEARS BETWEEN TWO DATES

2008-10-06 Thread AJIT NAVRE
> But the problem in this formula is that if an asset has been acquired on >>> 01/01/2004 it will show zero result whereas I want 1. >>> Again if the asset has been acquired on 01/01/2005 it will show "-1" >>> whereas >>> I want it to be zero. >>&

$$Excel-Macros$$ Re: VBA Macro Excel Add Text In Cell

2008-10-04 Thread AJIT NAVRE
of text in Excel. My example is an item number f123456789 that I need > to look like f.123.456.789. Any suggestions? I have code written but > it is bulky and I know there is an easier way. Thanks for any and all > help. > > > > -- Thank You, Ajit Navre --~--~-

$$Excel-Macros$$ Re: concatenation problem

2008-09-27 Thread AJIT NAVRE
aries lots depending upon the type of data i am > looking at.and i really do not want pages and pages of the same code > when a loop can do this. > > Thank you > > > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~ Visit the blog t

$$Excel-Macros$$ Re: listbox in excel that is an option for sql query

2008-09-26 Thread AJIT NAVRE
x would populate with > all the city's that belong to the states chosen. These two would be > in a where clause of an sql statement that I would query a database to > return data that populates the 3rd listbox. How would I go about > doing this? > > Thanks > >

$$Excel-Macros$$ Re: Unable to open excel

2008-09-25 Thread AJIT NAVRE
Hi, > > I am not able to 1 excel file when i open that file than error showing > "Unable to read file" . Pelase help me. It is very urgent. > > -- > Ashok jain > > > -- Thank You, Ajit Navre --~--~-~--~~~---~--~~

$$Excel-Macros$$ Re: Sorting Groups

2008-09-23 Thread AJIT NAVRE
ped each supplier details using > Outline grouping, The Column Rejection changes every week and i need > excel to sort the list on the basis of higest value providing the > grouping should not be shuffled or disturbed. Is it possible??? > > Thanks in advance. > Chetan > > On Sep

$$Excel-Macros$$ Re: Sorting Groups

2008-09-23 Thread AJIT NAVRE
lass 1 > > Supplier B > Product C Class3 > Product A Class2 > > I need to sort the Groups ( Supplier A , Supplier B . so on) But > when i sort using Data> Sort function Excel Sorts all the contents in > the column. > >

$$Excel-Macros$$ Re: Stack error problem

2008-09-23 Thread AJIT NAVRE
cost(I + > > 1, K + Z - Demand(I)) > > If Min_cost(I, K) > Hold Then > > Min_cost(I, K) = Hold > > Opt_Prod(I, K) = Z > > End If > > Next Z > > End If > > > > Next K > > Next II > >

$$Excel-Macros$$ Re: HOW TO CALCUATE THE NO. OF YEARS BETWEEN TWO DATES

2008-09-22 Thread AJIT NAVRE
f an asset has been acquired on >> 01/01/2004 it will show zero result whereas I want 1. >> Again if the asset has been acquired on 01/01/2005 it will show "-1" >> whereas >> I want it to be zero. >> Is there any other formula I can use. >> >> Than

$$Excel-Macros$$ Re: change from small letter to capital

2008-09-22 Thread AJIT NAVRE
D]> wrote: > >> dear all, >> >> i want to know how to change from small letters to capital >> letters as i have some 500 persons name which i have typed in small letters >> now wants to change those things in the Big letters . reply fast its >