Re: $$Excel-Macros$$ Hi
Hi Pankaj, Please find attached excel for solution. Regards, Swapnil. On Thu, Sep 30, 2010 at 9:15 PM, Pankaj Kumar rajputpanka...@gmail.comwrote: Hi, exper it urgent i need u r help i attached my excel sheet -- *Warm Regards Pankaj kumar M: 9899816107 e-mail: rajputpanka...@gmail.com rajputpank...@yahoo.in* * rajputpankaj1...@rediffmail.com* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Match Data - Solution.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Increment flags
Hi, I need some HELP with increment flagging. By flag I mean a One appears when an even happens, and a Zero appears when it doesn't And by incremental flags I mean that the even occurs at a fixed frequency but only after the event has occured. Let me explain, what I have in my excel sheet. Assuming in Row-2 I have my dates (In row-1 I have my inputs which I will explin in a minute): Jan 2010(in cell A2), Feb 2010(in B2), March 2010(in C2).. November 2050 , December 2050 If I say that a certain event will occur in March 2010 and from this date it will reoccur after every 5 monthly increments. I would like to insertdrag a formula in row-3 where in cell C3 (under the date of March 2010) there is a 1 and then there 5 cells later in cell H3 under date of Aug 2010 there is a 1, then in M3 under Jan 2011 etc etc... and there is a zero under all the other dates. Now the inputs I was talking about earlier, in Cell A1 I want to be able to input the date on which the event first occurs that is in above case this is March 2010, and in Cell A2 I would like to input my frequency or increments, i.e. in this case 5. How can I do this, any suggestions? I tried using the mod function but it did not work. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Hi
PFA Vaibhav J www.excel.blog.com Sent on my BlackBerry® from Vodafone -Original Message- From: Pankaj Kumar rajputpanka...@gmail.com Sender: excel-macros@googlegroups.com Date: Thu, 30 Sep 2010 21:15:12 To: excel-macrosexcel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Hi Hi, exper it urgent i need u r help i attached my excel sheet -- *Warm Regards Pankaj kumar M: 9899816107 e-mail: rajputpanka...@gmail.com rajputpank...@yahoo.in* * rajputpankaj1...@rediffmail.com* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Book2.xlsx Description: Binary data
Re: $$Excel-Macros$$ Excel Macro - Need help using Find and Selecting a range
Hi Paul - Thank you for your reply. I have added in your suggestion, but unfortunately I am still having a bit of an issue. The find now activates the correct cell, and the offset moves the active cell one to the left, but it is only selecting that particular row to copy and paste into the other tab. I am trying to figure out how to get it to select everything from the active cell to the bottom of the file, and copy and paste all of those rows. I keep poking and prodding with it, but I have had no luck. After adjustment, my code is: Selection.Find(What:=u, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Range(ActiveCell.Offset(0, -1), Cells(ActiveCell.Row, ActiveCell.SpecialCells(xlLastCell).Column)).Select Selection.Copy Sheets(Sheet3).Select Range(A1).Select ActiveSheet.Paste Thank you again! On Sep 30, 12:14 pm, Paul Schreiner schreiner_p...@att.net wrote: First of all, your code is very selection intensive. That is, really what you're doing is automating keystrokes. as long as you don't have a LOT of data to process, it should work. It's just not efficient. You COULD rewrite it using VBA variables and not select as much. But we'll work with what you have. What does your Selection.Find return? Selection.Find(What:=u, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt :=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Activate it returns the .Activate method... that is, it activates the cell. Now, if you want to select the cell to the left of the active cell. you can use the Offset() method: ActiveCell.Offset(0, -1).Select or, you can use something similar to what you did by using the cells() method. Now, your use of ActiveCell.SpecialCells(xlLastCell) is close... but the problem is that you only want the COLUMN of the last cell, and the current row. So, you could use: Cells(ActiveCell.Row, ActiveCell.SpecialCells(xlLastCell).Column) combining the two you get: Range(ActiveCell.Offset(0, -1), Cells(ActiveCell.Row, ActiveCell.SpecialCells(xlLastCell).Column)).Select try using that and see what you can do.. if you need more help, let me know. Paul - Original Message From: Erick C boricua2...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, September 30, 2010 12:10:35 PM Subject: $$Excel-Macros$$ Excel Macro - Need help using Find and Selecting a range Hi everybody - I am hoping someone can help me out with a problem I am having. I am very new to writing and recording macros, I have been trying to look at other macros that I use for different functions and tips. I have a macro that I am trying to get to do a few different things. The macro works up until a point and then it does not do what I am hoping it would. I first do a text-to-columns on the data and insert a column and fill the cells, which works fine, and then I am trying to do a Find on column B to identify the first cell with a U in it. Once this cell is found I am trying to move the cursor one cell to the left, select everything from that cell to the end, copy and paste the data into sheet 3. In sheet 3, another Find is done in column B to find the first cell with a us in it, move the cursor one cell to the left, select everything from that cell to the end and delete all of the data. I am having the biggest problem right now getting the cursor to move over after the Find has been done, as it is probably apparent in my code below. I have been trying a few different things with no luck. I saw a sendkeys command in one of my other macros and tried to integrate that, but I do not believe I did it correctly at all. I am hoping someone can show me how to get the macro to use the find to identify the correct cell and select the cell next to the one identified with the Find function. Here is my current code, any help would be greatly appreciated! Columns(A:A).Select Selection.TextToColumns Destination:=Range(A1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(13, 1), Array(19, 1), Array(27, 1), _ Array(28, 1), Array(36, 1), Array(66, 1), Array(81, 1), Array(105, 1), Array(131, 1)), _ TrailingMinusNumbers:=True Selection.Insert Shift:=xlToRight Range(A1).Select ActiveCell.FormulaR1C1 = 1 Range(A1).Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Stop:=3, Trend:=False Cells.Select Selection.Sort Key1:=Range(B1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns(B:B).Select
$$Excel-Macros$$ Help Req: average
Hi experts, how to eleminate zeros while selective range for average function.Please find the attachement here fro your reference. Regards, Rao -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts average.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$
Hi Ramesh, I guess if you only want time stamp then you can also use the below formula in column B. =IF(A10,TEXT(TODAY(),mm/dd/yy),) Regards, Deepak On Thu, Sep 30, 2010 at 9:02 PM, Paul Schreiner schreiner_p...@att.netwrote: Where did you place it? It belongs in the Sheet module for the specific sheet. Not a standard module, or the This workbook module. *From:* Ramkesh Maurya sunscel...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, September 30, 2010 10:51:38 AM *Subject:* Re: $$Excel-Macros$$ Hi Dave, I copied the code but unable to execute it, Can u please tell me where I am wrong? Regards Ramkesh On Thu, Sep 30, 2010 at 5:58 PM, Paul Schreiner schreiner_p...@att.netwrote: I would use a sheet change event: ' Private Sub Worksheet_Change(ByVal Target As Range) Dim Targ As Range For Each Targ In Target If Targ.Column = 1 Then Application.EnableEvents = False If (Targ.Value X = X) Then Cells(Targ.Row, B).ClearContents Else If (Cells(Targ.Row, B) X = X) Then Cells(Targ.Row, B) = Now End If Application.EnableEvents = True End If Next Targ End Sub ' Paul *From:* Ramkesh Maurya sunscel...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Wed, September 29, 2010 2:36:06 PM *Subject:* Re: $$Excel-Macros$$ Dear Dave, Thanks for paying attention, Yes I am allowed to use Macro I want the date in Col B only when the adjecent cell Col A received some text for the first time Regards Ramkesh On Wed, Sep 29, 2010 at 7:50 AM, Dave Bonallack davebonall...@hotmail.com wrote: A further question: Do you want the date in Col B to enter only when the adjacent cell in Col A receives text for the first time, or any time the adjacent Col A cell is changed? Regards - Dave. -- Date: Tue, 28 Sep 2010 23:40:09 +0530 Subject: $$Excel-Macros$$ From: sunscel...@gmail.com To: excel-macros@googlegroups.com Dear All, Xl Mania(s) I really appreciate this group for learning I have lrarnet a lot of tricks from this group which enabled me to come of with flying colours.Today i am posting my first query briefing in below lines--- 1- I fill some text in column A (say) 2- I want that the date of filling data should appear in column B (beside the column A) Now condition is - 3 -Date in column B should not be updated by re-calculation nature of Excel or user I know that anybody will help me so thanks in advance ! -- Ramkesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Ramkesh 9990260398 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER
Re: $$Excel-Macros$$ Urgent Querry
Hi Pankaj, In your case the attached formula can also help. Regards, Deepak Rai On Thu, Sep 30, 2010 at 8:58 PM, Venkat venkat1@gmail.com wrote: Dear Pankaj, Look attached resolved formula Best Regards, Venkat On Thu, Sep 30, 2010 at 6:28 PM, Pankaj Kumar rajputpanka...@gmail.comwrote: Hi, All Experts, Its Urgent -- I need u r help i have two column in excelsheet Data1 Data 2 we have to match both data if its Match show Match or not Match shows Mismatch in next coloum,,,Pls provide me formulas I have attached my excelsheet *Warm Regards Pankaj kumar M: 9899816107 e-mail: rajputpanka...@gmail.com rajputpank...@yahoo.in* * rajputpankaj1...@rediffmail.com* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Regards, Deepak Rai -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Solved by Deepak.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Macro for part scanning
MODELNUMBER123 Part 1A 123 Part 1B 0 Part 2A 234 Part 2B 0 Part 3A 456 Part 3B 432 I'm implementing a part scanning system For some models we only use Part 1A and some models Part 1A and Part 1B. Same thing for Parts 2 and 3. In such cases I'll have a dummy barcode that will read 0 The scanning should always start in cell C3, where I will scan the model number of the unit That will populate values from B4 to B9. Cursor will move to cell C4 where I will scan Part 1A and cursor will move to cell C5 and so forth. ( (I can do this far. I need help from the next step.) After scanning all parts (upto C9)the cursor should move from cell C9 to cell C3 and delete all the values from B4 to C9. I need a mcro for this step. I attached the file. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Macro_Scanning.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Help required to copy selective folders from various subfolders.
HI Team, I am looking for a macro for copy selective folders by name from various subfolders. Instance: Folder A has 3 subfolders named (aaa, bbb, ccc) Each subfolder has super subfolders as (Input Output) Requirement: I have to copy all Output folders from Each Parent folder by replacing the name as parent Folder. Example: The Output folder from Folder AAA has to be stored in a location by the name AAA. ( I dont want the Input Folder to be copied.) By Mariappan. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Importing data into excel
Hi, I am quit new to excel. I like to know if it is possible to import data from one excel document into another. The most ideal solution would be that after starting up an excel file you can somehow specify another excel file and import the data from that file into the file you opened. The data of the datafile is on one worksheet of the file. BR Peter -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: Help require to understand the Formula of the attached sheet
Hi Hiren, Well here's the thing. What you have here is a classic one variable data table. Essentially what it is doing is, well lets just say you've done your calculation on something. (which in this case is a payroll calculation in A1 to B11. Now what a one-variable data table does is it lets you carry out a sensitivity analysis on one of the variables, esentially asking excel to try alternative numbers in your calculation and tabulate the results.. The alternative variable in this case happens to be the number of employees which are being run through your calculation to give the total payroll cost. To MAKE a 1-variable data table: Try the following to understand how its done: 1. Delete everything in cells B15 to B22 2. You've have already typed the number of employees in cells A17 to A22 (This could have been any one of your basic assumptions) 3. Link the cell directly above where you would want your first result to appear to the result in the calculation i.e in cell B16 give the following formula =B11 4. now select the entire area of A16 to A22 and goto data table (You can access the data table menu by pressing Alt then D and then T) 5. now in the Column input link it it cell B3 Riiight, and thats about it. Hope that helps On 30 Sep, 16:17, Hiren Sheth hiren.sh...@viteos.com wrote: Hi Experts, Can you please explain formula of cell B17, B18, B19 and B20 of the attached sheet. Thanks Hiren This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mis-transmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Viteos Capital Market Services Ltd.and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity Book8.xlsx 13KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: Need next number with a twist
Joshua, Thank you so much for your response. Unfortunately this isn't working. The formula in col I is referencing col H which is where I originally wanted the formula. There will be no data in col H unless I can't get a formula to work in there, then it will be manually entered. Can you help me with how the formula should look in cell H2 so that it will return the same numbers I manually entered there to show what the formula result should be? Thank you. Nadine From: Joshua Leuthauser leu...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:32:44 PM Subject: $$Excel-Macros$$ Re: Need next number with a twist Build a key in column g. The formula for the key should be: =a2b2c2f2 Populate that down for all of the rows. Basically what I built says -- look at the column of keys, if you don't find a match then take the max of all transactions used thus far and increment by one. If you do find a match, use the same transaction number that was used by that key (the combination of a-c2 f2). You'll notice that it doesn't reserve the next transaction that should be used, it just assigns the next transaction to whichever key shows up that is unique. After you have your column built with the key, here is the formula I put in I2: =IF(ISNA(VLOOKUP(G2,G1:H1,2,FALSE)),MAX(H:H) +1,VLOOKUP(G2,G1:H1,2,FALSE)) Fill that down the remainder of column I and you have a working formula that will either give the same transaction number if a key match is found otherwise give you the next available transaction number. On Sep 28, 10:16 pm, None n8dine4ma...@yahoo.com wrote: Hi there. I need some help finding the next number but there's a twist. Attached is a file that shows what I need. There's a sheet titled Transactions where the data is continually added to and the order cannot be changed so sorting the data is out of the question. The next sheet is called Need Formula and this is where I need a formula to be entered into cells H2 - H24 for this sample spreadsheet. The actual file will have more rows. The formula needs to look at the number in cell G2 and increment it by one UNLESS, and here's the trick, there is already a number in this column (H) for the combination of cells A2-C2 and F2. The sample will give you a better idea as I've entered the end result in column H already. I just need a formula that will give me the same result. Thank you so much for your help. next numbers.xls 43KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: Help require to understand the Formula of the attached sheet
Dear Mr Hiren Sheth,
Perhaps you are refering to {=TABLE(,B3)} listed in the cells. This is
not an actual function but a table which is part of excel what if
analysis. In Excel 2007 you can go to Data tab What if analysis
Table and with older versions this feature is available in Tools menu.
Hope that helps,
Anand Kumar
anand...@gmail.com
On Sep 30, 4:17 pm, Hiren Sheth hiren.sh...@viteos.com wrote:
Hi Experts,
Can you please explain formula of cell B17, B18, B19 and B20 of the attached
sheet.
Thanks
Hiren
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mis-transmission. If
you receive this message in error, please immediately
delete it and all copies of it from your system, destroy any hard copies of
it and notify the sender. You must not, directly or
indirectly, use, disclose, distribute, print, or copy any part of this
message if you are not the intended recipient.
Viteos Capital Market Services Ltd.and any of its subsidiaries each reserve
the right to monitor all e-mail communications through
its networks. Any views expressed in this message are those of the individual
sender, except where the message states otherwise and
the sender is authorized to state them to be the views of any such entity
Book8.xlsx
13KViewDownload
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Importing data into excel
Hi, You Can import Data from Web ,Other Excel sheets ,Text, etc by using Get External Data in The DATA MENU of Excel. Regards, Vijay Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ macro to save it to a particular drive / file location.
I noticed that the email was off-line instead of to the group.. so I included it here. -- My solution seems to work if the file hasn't been named, so I got more elaborate: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileSaveName Cancel = True ChDrive H ChDir H:\data FileSaveName = Application.GetSaveAsFilename( _ fileFilter:=Excel Files (*.xls*), *.xls) If FileSaveName False Then MsgBox Save as FileSaveName Application.EnableEvents = False ActiveWorkbook.SaveAs FileSaveName End If Application.EnableEvents = True End Sub --- Hi Paul Thanks - it works - but only if its on C drive or sub folders etc. It doesn't like it if I use and S drive or a memory stick drive F etc any suggestions John On 30 September 2010 18:43, Paul Schreiner schreiner_p...@att.net wrote: right-click on the sheet tab name. select View Code This will open the VBA editor the panel on the right side SHOULD list the sheet in the workbook as well as a sheet called ThisWorkBook (if you don't see this panel, hit Ctrl-R) double-click ThisWorkbook At the top of the large editor window, you'll see two pull-down lists. The left one says: (General) change it to Workbook By default it creates a Workbook_Open() event. you can delete this one. in the right-hand pull-down, select BeforeSave it will create: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub add the Chdir line with the appropriate folder name so it looks like: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Chdir C:\temp End Sub save the template and you should be on your way! Paul - Original Message From: Johnnyboy5 intermediatec...@gmail.com To: Paul Schreiner schreiner_p...@att.net Sent: Thu, September 30, 2010 1:18:09 PM Subject: Re: $$Excel-Macros$$ macro to save it to a particular drive / file location. Thanks, I understand the idea but dont know how to write the macro to do it. many thanks John On 30 Sep, 13:19, Paul Schreiner schreiner_p...@att.net wrote: If you're allowing the users to define the name of the file they're saving (instead of having the program do it) then, in someplace like the Workbook_BeforeSave event, use: Chdir C:\temp (or whatever you want the default folder to be) Paul -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Macro hangs
Hi Paul, This code is excellent! I've looked up the Help for the Dictionary object, which clarified it's use quite well. At first I wondered why you had decided to work backwards, up through the code; then realised (I think) that it's so you can delete a row (Delete Shift:=xlUp) without having to account for the lost row number as you would if you were working down the data. Very cool! Wish I'd thought of that. Thanks also for the notations, which really helped me understand your process. And thanks for your time. Regards - Dave. Date: Wed, 29 Sep 2010 05:15:54 -0700 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro hangs To: excel-macros@googlegroups.com Dave, keep in mind that Excel can do several HUNDRED comparisons each SECOND. If you're adding an Application.wait for only ONE second EACH LINE for 13,000 lines, you're adding 13,000 SECONDS, or 216 minutes, or 3.6 hours of WAIT TIME! so, I suspect that you're not hanging, but simply waiting a LONG time. and, during the seconds of waiting, the escape characters used to interrupt aren't being received. Now.. personally, I like using excel dictionaries to store unique data. I've done some pretty elaborate things. I wrote a script to compare the fields and sum the columns. It runs (on my machine) in 1 minute, 19 seconds... try this (watch for wrapping from email): it also displays a status line in the status bar. Sub DeleteDuplicateDict() Dim RowCnt, R, Datainx, stat, msg Dim Dict_E, Dict_F Dim tstart, tstop, TMin, TSec, TElapsed tstart = Timer Application.ScreenUpdating = False Set Dict_E = CreateObject(Scripting.Dictionary) Set Dict_F = CreateObject(Scripting.Dictionary) stat = Dict_E.RemoveAll stat = Dict_F.RemoveAll ' Count the number of rows in sheet RowCnt = ActiveCell.SpecialCells(xlLastCell).Row 'Starting in the last row, process upwards For R = RowCnt To 2 Step -1 If (R Mod 500 = 0) Then Application.StatusBar = Processing: R Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value If (Datainx X X) Then 'If the data row is not blank If (Not Dict_E.exists(Datainx)) Then 'new data, add new record to dictionaries Dict_E.Add Datainx, ActiveSheet.Cells(R, E).Value Dict_F.Add Datainx, ActiveSheet.Cells(R, F).Value Else 'Existing records, update dictionaries Dict_E.Item(Datainx) = Dict_E.Item(Datainx) + ActiveSheet.Cells(R, E).Value Dict_F.Item(Datainx) = Dict_F.Item(Datainx) + ActiveSheet.Cells(R, F).Value Rows(R).Delete Shift:=xlUp End If End If Next R ' Count rows remaining RowCnt = Application.WorksheetFunction.CountA(Range(A:A)) For R = 2 To RowCnt If (R Mod 500 = 0) Then Application.StatusBar = Updating: R of RowCnt Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value 'update rows with Dictionary values If (Dict_E.exists(Datainx)) Then ActiveSheet.Cells(R, E).Value = Dict_E.Item(Datainx) ActiveSheet.Cells(R, F).Value = Dict_F.Item(Datainx) Else Cells(R, A).Select MsgBox Missing data for row: R End If Next R 'display processing time tstop = Timer TMin = 0 TElapsed = tstop - tstart TMin = TElapsed \ 60 TSec = TElapsed Mod 60 msg = msg Chr(13) Chr(13) If (TMin 0) Then msg = msg TMin mins msg = msg TSec sec MsgBox msg Application.StatusBar = False Application.ScreenUpdating = True End Sub Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:14:06 AM Subject: $$Excel-Macros$$ Macro hangs Hi group, I'm hoping someone can help me with the attached workbook. I've written a macro that makes XL freeze. The need is to check the data for duplicates based on Columns B, C D. If duplicates are found, their totals in Columns E F are to be sumed, then the duplicate row deleted. I concatonate Cells B2, C2 D2, then compare that with a concatonation of cells B3, C3 D3, then B4, C4 D4, and so on to the end of the data, dealing with duplicates as they come up. Then I start again with row 3, and so on until all the data is checked. The macro takes a long time to run, so I report progress in Cells G1 and H1. Whenever I run this macro, it never gets past about line 10 before XL freezes, and I have to use the Windows Task Manager to close it. There may be a better way of doing this, but my question is, why does it cause XL to freeze?
RE: $$Excel-Macros$$ Visible Row Below Freeze Pane
Hi, How would I prove row 32 is the first visible row through VBA A = Activewindow.VisibleRange.Row If i then wanted to make row 50 the first visible row, how could it be done through VBA Range(A2).Select ActiveWindow.SmallScroll Down:=48 There's probably a better way of doing the second one, but this is all I could think of tonight. Regards - Dave. Date: Tue, 28 Sep 2010 16:08:14 -0700 Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane From: spa...@corbetteer.co.uk To: excel-macros@googlegroups.com With the top row frozen and the sheet scrolled down so that the first visible row below row 1 is row 32. How would I prove row 32 is the first visible row through VBA If i then wanted to make row 50 the first visible row, how could it be done through VBA -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
RE: $$Excel-Macros$$ Visible Row Below Freeze Pane
Hi, A simpler alternative for your second requirement: ActiveWindow.ScrollRow = 50 Regards - Dave. Date: Tue, 28 Sep 2010 16:08:14 -0700 Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane From: spa...@corbetteer.co.uk To: excel-macros@googlegroups.com With the top row frozen and the sheet scrolled down so that the first visible row below row 1 is row 32. How would I prove row 32 is the first visible row through VBA If i then wanted to make row 50 the first visible row, how could it be done through VBA -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ bypass savepdf dailogue
Hi ALL am in a process of coding a macro that converts excel to pdf..this has to perform for an automation process..am almost close to the result..but need to bypass the savepdf dialogue ..can any body help me on this... earlier help is highly appreciated... Global Const dhcRegSz = 1 Public Declare Function RegOpenKeyEx Lib advapi32.dll Alias RegOpenKeyExA (ByVal hKey As Long, ByVal lpSubKey As String,ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long Public Declare Function RegSetValueEx Lib advapi32.dll Alias RegSetValueExA (ByVal hKey As Long, ByVal lpValueName As String, ByVal dwReserved As Long, ByVal dwType As Long, pData As Any, ByVal cbData As Long) As Long Public Declare Function RegCloseKey Lib advapi32.dll (ByVal hKey As Long) As Long sub MyMacro() dim strDefaultPrinter as string, strOutFile as string strDefaultPrinter = Application.ActivePrinter Application.ActivePrinter = Adobe PDF on Ne00: lngRegResult = RegOpenKeyEx(dhcHKeyCurrentUser,Software \Adobe\Acrobat PDFWriter, 0, dhcKeyAllAccess, lngResult) lngRegResult = RegSetValueEx(lngResult, bExecViewer, 0,dhcRegSz, ByVal 0, 1) lngRegResult = RegSetValueEx(lngResult, bDocInfo, 0, dhcRegSz,ByVal 0, 1) strOutFile = C:\check.pdf lngRegResult = RegSetValueEx(lngResult, PDFFileName, 0,dhcRegSz, ByVal strOutFile, Len(strOutFile)) lngRegResult = RegCloseKey(lngResult) ActiveWindow.SelectedSheets.PrintOut -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Importing data into excel
Dear Vijay, In the DATA menu of Excel I have options for Web, Text, Acess, and other source, I do not have a button for excel-files. I already tried several option in the menupath but I couldn't let it work . In the attachment an example. In practice there are several 100's of datafiles. The problem I have is that the real file Import_and_Calc is under construction and is changed frequently. For that reason I want to have separate Data-files and one file Import and Calculate were I can do calculations,which depend on the data in the datafiles. Kind regards Peter 2010/10/1 P.VIJAYKUMAR vijay.4...@gmail.com Hi, You Can import Data from Web ,Other Excel sheets ,Text, etc by using Get External Data in The DATA MENU of Excel. Regards, Vijay Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Data_1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Data_2.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Import_and_Calc.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$
Hi dave, Thank you so much I pasted in right place and now it is working Thanks once again Regards Ramkesh On Thu, Sep 30, 2010 at 9:02 PM, Paul Schreiner schreiner_p...@att.netwrote: Where did you place it? It belongs in the Sheet module for the specific sheet. Not a standard module, or the This workbook module. *From:* Ramkesh Maurya sunscel...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, September 30, 2010 10:51:38 AM *Subject:* Re: $$Excel-Macros$$ Hi Dave, I copied the code but unable to execute it, Can u please tell me where I am wrong? Regards Ramkesh On Thu, Sep 30, 2010 at 5:58 PM, Paul Schreiner schreiner_p...@att.netwrote: I would use a sheet change event: ' Private Sub Worksheet_Change(ByVal Target As Range) Dim Targ As Range For Each Targ In Target If Targ.Column = 1 Then Application.EnableEvents = False If (Targ.Value X = X) Then Cells(Targ.Row, B).ClearContents Else If (Cells(Targ.Row, B) X = X) Then Cells(Targ.Row, B) = Now End If Application.EnableEvents = True End If Next Targ End Sub ' Paul *From:* Ramkesh Maurya sunscel...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Wed, September 29, 2010 2:36:06 PM *Subject:* Re: $$Excel-Macros$$ Dear Dave, Thanks for paying attention, Yes I am allowed to use Macro I want the date in Col B only when the adjecent cell Col A received some text for the first time Regards Ramkesh On Wed, Sep 29, 2010 at 7:50 AM, Dave Bonallack davebonall...@hotmail.com wrote: A further question: Do you want the date in Col B to enter only when the adjacent cell in Col A receives text for the first time, or any time the adjacent Col A cell is changed? Regards - Dave. -- Date: Tue, 28 Sep 2010 23:40:09 +0530 Subject: $$Excel-Macros$$ From: sunscel...@gmail.com To: excel-macros@googlegroups.com Dear All, Xl Mania(s) I really appreciate this group for learning I have lrarnet a lot of tricks from this group which enabled me to come of with flying colours.Today i am posting my first query briefing in below lines--- 1- I fill some text in column A (say) 2- I want that the date of filling data should appear in column B (beside the column A) Now condition is - 3 -Date in column B should not be updated by re-calculation nature of Excel or user I know that anybody will help me so thanks in advance ! -- Ramkesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Ramkesh 9990260398 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :
Re: $$Excel-Macros$$ Macro hangs
Yeah, back about 100 years ago (ok, maybe that's exaggerating) I discovered that when moving top-to-bottom and delete a row, it makes the next row the current row, then the next loop iteration caused it to skip that row. You handled that by reducing the loop counter. But I decided that working bottom-up would eliminate the problem. I just had to figure out how VBA loops handled decreasing steps. very observant of you recognize the value! (I've had people correct my step counter and complain because it doesn't work. I mean after all: Excel should recognize that if you're going from 10 to 1 it's going down! ... yeah.. that would be nice, but it doesn't) good luck! Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Fri, October 1, 2010 10:00:46 AM Subject: RE: $$Excel-Macros$$ Macro hangs Hi Paul, This code is excellent! I've looked up the Help for the Dictionary object, which clarified it's use quite well. At first I wondered why you had decided to work backwards, up through the code; then realised (I think) that it's so you can delete a row (Delete Shift:=xlUp) without having to account for the lost row number as you would if you were working down the data. Very cool! Wish I'd thought of that. Thanks also for the notations, which really helped me understand your process. And thanks for your time. Regards - Dave. Date: Wed, 29 Sep 2010 05:15:54 -0700 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro hangs To: excel-macros@googlegroups.com Dave, keep in mind that Excel can do several HUNDRED comparisons each SECOND. If you're adding an Application.wait for only ONE second EACH LINE for 13,000 lines, you're adding 13,000 SECONDS, or 216 minutes, or 3.6 hours of WAIT TIME! so, I suspect that you're not hanging, but simply waiting a LONG time. and, during the seconds of waiting, the escape characters used to interrupt aren't being received. Now.. personally, I like using excel dictionaries to store unique data. I've done some pretty elaborate things. I wrote a script to compare the fields and sum the columns. It runs (on my machine) in 1 minute, 19 seconds... try this (watch for wrapping from email): it also displays a status line in the status bar. Sub DeleteDuplicateDict() Dim RowCnt, R, Datainx, stat, msg Dim Dict_E, Dict_F Dim tstart, tstop, TMin, TSec, TElapsed tstart = Timer Application.ScreenUpdating = False Set Dict_E = CreateObject(Scripting.Dictionary) Set Dict_F = CreateObject(Scripting.Dictionary) stat = Dict_E.RemoveAll stat = Dict_F.RemoveAll ' Count the number of rows in sheet RowCnt = ActiveCell.SpecialCells(xlLastCell).Row 'Starting in the last row, process upwards For R = RowCnt To 2 Step -1 If (R Mod 500 = 0) Then Application.StatusBar = Processing: R Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value If (Datainx X X) Then 'If the data row is not blank If (Not Dict_E.exists(Datainx)) Then 'new data, add new record to dictionaries Dict_E.Add Datainx, ActiveSheet.Cells(R, E).Value Dict_F.Add Datainx, ActiveSheet.Cells(R, F).Value Else 'Existing records, update dictionaries Dict_E.Item(Datainx) = Dict_E.Item(Datainx) + ActiveSheet.Cells(R, E).Value Dict_F.Item(Datainx) = Dict_F.Item(Datainx) + ActiveSheet.Cells(R, F).Value Rows(R).Delete Shift:=xlUp End If End If Next R ' Count rows remaining RowCnt = Application.WorksheetFunction.CountA(Range(A:A)) For R = 2 To RowCnt If (R Mod 500 = 0) Then Application.StatusBar = Updating: R of RowCnt Datainx = ActiveSheet.Cells(R, B).Value ActiveSheet.Cells(R, C).Value ActiveSheet.Cells(R, D).Value 'update rows with Dictionary values If (Dict_E.exists(Datainx)) Then ActiveSheet.Cells(R, E).Value = Dict_E.Item(Datainx) ActiveSheet.Cells(R, F).Value = Dict_F.Item(Datainx) Else Cells(R, A).Select MsgBox Missing data for row: R End If Next R 'display processing time tstop = Timer TMin = 0 TElapsed = tstop - tstart TMin = TElapsed \ 60 TSec = TElapsed Mod 60 msg = msg Chr(13) Chr(13) If (TMin 0) Then msg = msg TMin mins msg = msg TSec sec MsgBox msg Application.StatusBar = False Application.ScreenUpdating = True End Sub Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wed, September 29, 2010 5:14:06 AM Subject: $$Excel-Macros$$ Macro hangs Hi
Re: $$Excel-Macros$$ Urgent Querry
Sheet attached for your query. On Thu, Sep 30, 2010 at 5:58 PM, Pankaj Kumar rajputpanka...@gmail.comwrote: Hi, All Experts, Its Urgent -- I need u r help i have two column in excelsheet Data1 Data 2 we have to match both data if its Match show Match or not Match shows Mismatch in next coloum,,,Pls provide me formulas I have attached my excelsheet *Warm Regards Pankaj kumar M: 9899816107 e-mail: rajputpanka...@gmail.com rajputpank...@yahoo.in* * rajputpankaj1...@rediffmail.com* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts 0010.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Importing data into excel
Hi Peter, For this you need to have a common folder where you can store all your Data files after that through VBA macro you can consolidate all those N numbers of Data files into a consolidated excel file. You would not able to do this by manual approach. Regards, Deepak Rai On Fri, Oct 1, 2010 at 5:57 PM, Peter Konijn peter@gmail.com wrote: Dear Vijay, In the DATA menu of Excel I have options for Web, Text, Acess, and other source, I do not have a button for excel-files. I already tried several option in the menupath but I couldn't let it work . In the attachment an example. In practice there are several 100's of datafiles. The problem I have is that the real file Import_and_Calc is under construction and is changed frequently. For that reason I want to have separate Data-files and one file Import and Calculate were I can do calculations,which depend on the data in the datafiles. Kind regards Peter 2010/10/1 P.VIJAYKUMAR vijay.4...@gmail.com Hi, You Can import Data from Web ,Other Excel sheets ,Text, etc by using Get External Data in The DATA MENU of Excel. Regards, Vijay Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Regards, Deepak Rai -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Importing data into excel
Peter, I have this macro I will customize it as per your need will soon share the same with you. Regards, Deepak Rai On Fri, Oct 1, 2010 at 11:35 PM, Deepak Rai daksh1...@gmail.com wrote: Hi Peter, For this you need to have a common folder where you can store all your Data files after that through VBA macro you can consolidate all those N numbers of Data files into a consolidated excel file. You would not able to do this by manual approach. Regards, Deepak Rai On Fri, Oct 1, 2010 at 5:57 PM, Peter Konijn peter@gmail.comwrote: Dear Vijay, In the DATA menu of Excel I have options for Web, Text, Acess, and other source, I do not have a button for excel-files. I already tried several option in the menupath but I couldn't let it work . In the attachment an example. In practice there are several 100's of datafiles. The problem I have is that the real file Import_and_Calc is under construction and is changed frequently. For that reason I want to have separate Data-files and one file Import and Calculate were I can do calculations,which depend on the data in the datafiles. Kind regards Peter 2010/10/1 P.VIJAYKUMAR vijay.4...@gmail.com Hi, You Can import Data from Web ,Other Excel sheets ,Text, etc by using Get External Data in The DATA MENU of Excel. Regards, Vijay Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- Regards, Deepak Rai -- Regards, Deepak Rai -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
