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 To: MS EXCEL AND VBA MACROS 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: =a2&b2&c2&f2 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 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=wall&ref=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=wall&ref=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 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=wall&ref=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=wall&ref=ts
$$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=wall&ref=ts
$$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=wall&ref=ts Macro_Scanning.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
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 wrote: > Dear Pankaj, > > Look attached resolved formula > > Best Regards, > > Venkat > On Thu, Sep 30, 2010 at 6:28 PM, Pankaj Kumar > wrote: > >> >> 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=wall&ref=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=wall&ref=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=wall&ref=ts Solved by Deepak.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
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(A1>0,TEXT(TODAY(),"mm/dd/yy"),"") Regards, Deepak On Thu, Sep 30, 2010 at 9:02 PM, Paul Schreiner wrote: > 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 > *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 wrote: > >> 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 >> >> *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=wall&ref=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=wall&ref=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 Trick
$$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=wall&ref=ts average.xls Description: MS-Excel spreadsheet
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 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
> > To: MS EXCEL AND VBA MACROS
> > 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, Orientat
Re: $$Excel-Macros$$ Hi
PFA Vaibhav J www.excel.blog.com Sent on my BlackBerry® from Vodafone -Original Message- From: Pankaj Kumar Sender: excel-macros@googlegroups.com Date: Thu, 30 Sep 2010 21:15:12 To: excel-macros 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=wall&ref=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=wall&ref=ts Book2.xlsx Description: Binary data
$$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 insert&drag 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=wall&ref=ts
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 wrote: > 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=wall&ref=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=wall&ref=ts Match Data - Solution.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Macro hangs
Hi Paul,
Thanks for spending time on this. I saw another post of yours about using
dictionaries which got my attention at the time, but then I got distracted by
something shiny, and the moment was lost. I'll have a look at your reply
(below) in detail on the weekend.
But, being a curious lad, I'd like to know why my current macro hangs XL. The
macro has a "progress report" row counter which updates cell G1 every time a
row is checked, so I know when the macro has actually stopped running. Also,
when I then press Escape, the whole screen sort of fades to white.
If I could inspire you to share my curiosity, perhaps you could open my
workbook and run it (with the timer section disabled), and let me know if you
have any ideas on what I've done wrong. But if your macro really does do 13000
lines in just over a minute (and I have no reason to doubt you) then I will
definitely be impressed, and dictionaries may even rise above SumProduct (in my
eyes).
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
> >To: "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 conc
Re: $$Excel-Macros$$ Hi
Hi Pankaj, In your attached example put the following formula in cell C2 and copy it down: =IF(NOT(ISERROR(VLOOKUP(B2,$A$2:$A$8,1,0))),"Match","Mismatch") The above formula matches the entries in the Column B with the column A. Pl let me know if this works for you. Regards, Andy On Thu, Sep 30, 2010 at 9:15 PM, Pankaj Kumar wrote: > 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=wall&ref=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=wall&ref=ts
Re: $$Excel-Macros$$ Excel Macro - Need help using Find and Selecting a range
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
> To: MS EXCEL AND VBA MACROS
> 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
> Selection.Find(What:="u", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt _
> :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> SendKeys "({LEFT})", [FALSE]
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.Copy
> Sheets("Sheet3").Select
> Range("A1").Select
> ActiveSheet.Paste
> Columns("B:B").Select
> Selection.Find(What:="us", After:=ActiveCell, LookIn:=xlFormulas,
> LookAt _
> :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> SendKeys "({LEFT})", [FALSE]
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Application.CutCopyMode = False
> Selection.ClearContents
> Sheets("Sheet1").Select
> Range("A1").Select
>
> --
>--
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://
Re: $$Excel-Macros$$ Please tell me what is meaning of this code
I think I may have answered the first question "off-line":
the listbox is an array.
array indexes start at 0
so, if your list is: line1, line2,line3 it looks like:
list(0) = line1
list(1) = line2
list(2) = line3
the total number of elements in the array (or listbox) is 3,
but the index is 0 to 2.
So, to loop through, you start at 0 and end at count - 1.
Now, assuming the item selected represents a row in your worksheet,
and your worksheet INCLUDES a header row:
row1 = header
row2 = line1
row3 = line2
row4 = line3
let's say you've selected item 2, representing the second row of data (on row
#3)
In the first loop, it starts at i = 0 (value "line1")
the second loop, i = 1 (value "line2") is what you've selected.
So, you want to select the cell in column A, line THREE (second line of data
after header)
so you use the loop counter i + 2
or: Range("A" & i + 2)
note: If you can only select one item in your listbox (multiselect =false)
then you should add:
Exit For
after the Worksheets line.
otherwise, if you have 50 items in your listbox and select item 2, it still has
to
check the other 48 items.
once you found the selected item, you should exit the loop.
Unless you CAN select multiple items.
Paul
>
>From: neil johnson
>To: talk2mar...@gmail.com; villager.g...@gmail.com
>Cc: excel-macros@googlegroups.com
>Sent: Thu, September 30, 2010 11:49:43 AM
>Subject: $$Excel-Macros$$ Please tell me what is meaning of this code
>
>Hi Sir,
>
>I am sending attached excel sheet in which user form display the dynamic
>range.
>When we select range in user form with the help of list box data goes to
>another
>sheet. Sir My question is the highlighted.: Why we do listcount -1 and other
>question is that highlight cell .Sir I need your help. Because i am making
>project . That is my first project .
>
>
>
> Dim i As Long, j As Long
> For i = 0 To Me.ListBox1.ListCount - 1( Why We do listcount -1)
> If Me.ListBox1.Selected(i) Then
>
> Worksheets("Sheet1").Range("A" & i + 2).Resize(1, 6).Copy
>Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
> End If
> Next i
>--
>--
>
>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=wall&ref=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=wall&ref=ts
$$Excel-Macros$$ Please tell me what is meaning of this code
Hi Sir,
I am sending attached excel sheet in which user form display the dynamic
range. When we select range in user form with the help of list box data goes
to another sheet. Sir My question is the highlighted.: Why we do listcount
-1 and other question is that highlight cell .Sir I need your help. Because
i am making project . That is my first project .
Dim i As Long, j As Long
* For i = 0 To Me.ListBox1.ListCount - 1( Why We do listcount -1)*
If Me.ListBox1.Selected(i) Then*
Worksheets("Sheet1").Range("A" & i + 2).Resize(1, 6).Copy
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i*
--
--
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=wall&ref=ts
Copy of listbox_multiselect.xls
Description: MS-Excel spreadsheet
$$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
Selection.Find(What:="u", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
SendKeys "({LEFT})", [FALSE]
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Find(What:="us", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
SendKeys "({LEFT})", [FALSE]
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet1").Select
Range("A1").Select
--
--
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=wall&ref=ts
$$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=wall&ref=ts Book2.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Urgent Querry
Dear Pankaj, Look attached resolved formula Best Regards, Venkat On Thu, Sep 30, 2010 at 6:28 PM, Pankaj Kumar wrote: > > 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=wall&ref=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=wall&ref=ts 0010 Venkat Resolved.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Urgent Querry
PFA sheet with formula Vaibhav J Sent on my BlackBerry® from Vodafone -Original Message- From: Pankaj Kumar Sender: excel-macros@googlegroups.com Date: Thu, 30 Sep 2010 18:28:53 To: excel-macros Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent Querry 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=wall&ref=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=wall&ref=ts 0010.xlsx Description: Binary data
Re: $$Excel-Macros$$
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 >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 wrote: > >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 >>> >>>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 >>>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=wall&ref=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=wall&ref=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 fo
$$Excel-Macros$$ Urgent Querry
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=wall&ref=ts 0010.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ macro to save it to a particular drive / file location.
Please post a NEW topic instead of "hijacking" another discussion thread. P > >From: Rakesh Sharma >To: excel-macros@googlegroups.com >Cc: intermediatec...@gmail.com >Sent: Thu, September 30, 2010 4:19:19 AM >Subject: Re: $$Excel-Macros$$ macro to save it to a particular drive / file >location. > >Hi , > > >I am rakesh from Jammu and i want to know more about macro, and i also want to >know how to use macro and whats the functions of macro, > > >and my another question is can we add 2 values in vlookup . > >please get the attached sheet for exp. > > >Rakesh Sharma > > >On Thu, Sept 30, 2010 at 1:00 PM, Johnnyboy5 wrote: > >I have a workbook "template" which when team members use it and >>then >>when they go to save it defaults to “my documents” and I would like to >>set >>up a macro to save it to a particular drive / file location. This is >>needed to stop team members saving to the wrong location etc. >> >>Below is one that works for a word template that I use, but I don’t >>think it will work with an Excel file. >> >> >>Excel 2003 >> >> >>Any Ideas ? >> >> >>John >> >> >> Credit to Graham Mayor >> >> >> >>For these macros. >> >>Sub FileSaveAs() >>Dim sPath As String >>sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path >>for the document >>On Error Resume Next >>ChDir sPath 'Change to the directory you wish to save in >>If Err.Number = 76 Then 'that folder doesn't exist >> >> >> MsgBox sPath & " is not available on this PC?" & vbCr & "Select >>the correct folder to save the document" >>End If >>With Dialogs(wdDialogFileSaveAs) >> >> >> .Name = sPath & "\" >> .Show 'show the save dialog >>End With >>End Sub >> >> >>Sub FileSave() >>Dim sPath As String >>sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path >>for the document >>On Error Resume Next >>If Len(ActiveDocument.Path) > 0 Then 'the document has been >>previously >>saved >> ActiveDocument.Save 'so save the changes >>Else 'The document has not been saved >> ChDir sPath 'Change to the directory you wish to save in >> If Err.Number = 76 Then 'that folder doesn't exist >> >> >> MsgBox sPath & " is not available on this PC?" & vbCr & >>"Select >>the correct folder to save the document" >> End If >> With Dialogs(wdDialogFileSaveAs) >> >> >> .Name = sPath & "\" >> .Show 'show the save dialog >> End With >>End If >>End Sub >> >> >>-- >>-- >> >>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=wall&ref=ts >> > > >-- > >Regards >Rakesh Sharma > >-- >-- > >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=wall&ref=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=wall&ref=ts
Re: $$Excel-Macros$$
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 >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 >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=wall&ref=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=wall&ref=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=wall&ref=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/discuss
Re: $$Excel-Macros$$ macro to save it to a particular drive / file location.
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 - Original Message > From: Johnnyboy5 > To: MS EXCEL AND VBA MACROS > Sent: Thu, September 30, 2010 3:30:17 AM > Subject: $$Excel-Macros$$ macro to save it to a particular drive / file >location. > > I have a workbook "template" which when team members use it and > then > when they go to save it defaults to “my documents” and I would like to > set > up a macro to save it to a particular drive / file location. This is > needed to stop team members saving to the wrong location etc. > > Below is one that works for a word template that I use, but I don’t > think it will work with an Excel file. > > > Excel 2003 > > > Any Ideas ? > > > John > > > > >> Credit to Graham Mayor >> > > > For these macros. > > Sub FileSaveAs() > Dim sPath As String > sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path > for the document > On Error Resume Next > ChDir sPath 'Change to the directory you wish to save in > If Err.Number = 76 Then 'that folder doesn't exist > > > MsgBox sPath & " is not available on this PC?" & vbCr & "Select > the correct folder to save the document" > End If > With Dialogs(wdDialogFileSaveAs) > > > .Name = sPath & "\" > .Show 'show the save dialog > End With > End Sub > > > Sub FileSave() > Dim sPath As String > sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path > for the document > On Error Resume Next > If Len(ActiveDocument.Path) > 0 Then 'the document has been > previously > saved > ActiveDocument.Save 'so save the changes > Else 'The document has not been saved > ChDir sPath 'Change to the directory you wish to save in > If Err.Number = 76 Then 'that folder doesn't exist > > > MsgBox sPath & " is not available on this PC?" & vbCr & > "Select > the correct folder to save the document" > End If > With Dialogs(wdDialogFileSaveAs) > > > .Name = sPath & "\" > .Show 'show the save dialog > End With > End If > End Sub > > > -- >-- >- > 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=wall&ref=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=wall&ref=ts
$$Excel-Macros$$ Help require to understand the Formula of the attached sheet
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 -- -- 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=wall&ref=ts Book8.xlsx Description: Book8.xlsx
Re: $$Excel-Macros$$ macro to save it to a particular drive / file location.
*Hi , I am rakesh from Jammu and i want to know more about macro, and i also want to know how to use macro and whats the functions of macro, and my another question is can we add 2 values in vlookup . please get the attached sheet for exp. Rakesh Sharma * On Thu, Sept 30, 2010 at 1:00 PM, Johnnyboy5 wrote: > I have a workbook "template" which when team members use it and > then > when they go to save it defaults to “my documents” and I would like to > set > up a macro to save it to a particular drive / file location. This is > needed to stop team members saving to the wrong location etc. > > Below is one that works for a word template that I use, but I don’t > think it will work with an Excel file. > > > Excel 2003 > > > Any Ideas ? > > > John > > > > >> Credit to Graham Mayor >> > > > For these macros. > > Sub FileSaveAs() > Dim sPath As String > sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path > for the document > On Error Resume Next > ChDir sPath 'Change to the directory you wish to save in > If Err.Number = 76 Then 'that folder doesn't exist > > >MsgBox sPath & " is not available on this PC?" & vbCr & "Select > the correct folder to save the document" > End If > With Dialogs(wdDialogFileSaveAs) > > >.Name = sPath & "\" >.Show 'show the save dialog > End With > End Sub > > > Sub FileSave() > Dim sPath As String > sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path > for the document > On Error Resume Next > If Len(ActiveDocument.Path) > 0 Then 'the document has been > previously > saved > ActiveDocument.Save 'so save the changes > Else 'The document has not been saved > ChDir sPath 'Change to the directory you wish to save in > If Err.Number = 76 Then 'that folder doesn't exist > > > MsgBox sPath & " is not available on this PC?" & vbCr & > "Select > the correct folder to save the document" > End If > With Dialogs(wdDialogFileSaveAs) > > > .Name = sPath & "\" > .Show 'show the save dialog > End With > End If > End Sub > > > -- > > -- > 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=wall&ref=ts > -- Regards Rakesh Sharma -- -- 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=wall&ref=ts RAKESH SDFASD.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ macro to save it to a particular drive / file location.
I have a workbook "template" which when team members use it and then when they go to save it defaults to “my documents” and I would like to set up a macro to save it to a particular drive / file location. This is needed to stop team members saving to the wrong location etc. Below is one that works for a word template that I use, but I don’t think it will work with an Excel file. Excel 2003 Any Ideas ? John >> Credit to Graham Mayor >> For these macros. Sub FileSaveAs() Dim sPath As String sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path for the document On Error Resume Next ChDir sPath 'Change to the directory you wish to save in If Err.Number = 76 Then 'that folder doesn't exist MsgBox sPath & " is not available on this PC?" & vbCr & "Select the correct folder to save the document" End If With Dialogs(wdDialogFileSaveAs) .Name = sPath & "\" .Show 'show the save dialog End With End Sub Sub FileSave() Dim sPath As String sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path for the document On Error Resume Next If Len(ActiveDocument.Path) > 0 Then 'the document has been previously saved ActiveDocument.Save 'so save the changes Else 'The document has not been saved ChDir sPath 'Change to the directory you wish to save in If Err.Number = 76 Then 'that folder doesn't exist MsgBox sPath & " is not available on this PC?" & vbCr & "Select the correct folder to save the document" End If With Dialogs(wdDialogFileSaveAs) .Name = sPath & "\" .Show 'show the save dialog End With End If End Sub -- -- 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=wall&ref=ts
Re: $$Excel-Macros$$ Fwd: कविता :- उस Expert का शुभ नाम है......पाण् डेय MR. दिलीप जी.
Dilip is very helpful person with blazingly meritorious and zenithal brilliancy in excel Keep it up Dilip. My all wishes with you. Kalyan 2010/9/29 SUMIT VYAS > Dear All R/s Friends > > Dilip Pandey is great personality . That is truth. He is genius and great > knowledge of excel vba. I salute to you . > > > D = Data ( Good Data Method ) > I = Input ( All input Knowledge ) > L = Logic ( Good Logic ) > I = International ( International Khiladi ) > P = Platform ( In Excel - Macro Platform ) > > Formula > > *=VlookupData+input+logic+international) , ( world,moon,sun,galaxy) (all > talent ) True * > > Ans > > Ans =*Mr. DILIP* > > Regards > > Sumit Vyas > > > > On 9/28/10, ayush jain wrote: >> >> Hi Dilip, >> >> You deserve this appreciation in the form of poem. we all love you and >> your solutions. KEEP IT UP. >> >> Hi Noorain, >> >> I think we have a great poet with us...Now I will bother you to write more >> poems(or chaalisa) for our excel enthusiasts.. :) >> Thank you for awesome poem for wonderful person. May be you will write >> poem in English next time for our English readers. >> >> Best Regards, >> Ayush Jain >> Group Manager >> >> -- Forwarded message -- >> From: Dilip Pandey >> Date: 2010/9/28 >> Subject: Fwd: कविता :- उस Expert का शुभ नाम है..पाण्डेय MR. दिलीप जी. >> To: ayush jain >> >> >> FYI..!! >> >> Many thanks to you as well for creating this wonderful platform.. :) >> >> Warmest Regards, >> DILIPandey >> >> >> -- Forwarded message -- >> From: NOORAIN ANSARI >> Date: 2010/9/28 >> Subject: कविता :- उस Expert का शुभ नाम है..पाण्डेय MR. दिलीप जी. >> To: Dilip Pandey , dilipan...@yahoo.com >> >> >> जिनके ह्रदय के धरातल पर जलता है EXCEL का दीप जी. >> और उस दीप के रौशनी में पलते है कई हजारों TRICK जी. >> उस Expert का शुभ नाम है... पाण्डेय MR. दिलीप जी. >> झट से पूछो सवाल, और चट से पावों जवाब. >> Excel/VBA का है ये एक चलता-फिरता किताब. >> इनके FUNDO के पीछे LOGIC है बेहिसाब. >> इनके इस skills का तो हम है पुराने FAN जी. >> तभी तो इनको कहते है हम MACRO MAN जी. >> बहूत ज्यादा PRACTICE छुपी है जो इनकी KNOWLEDGE है DEEP जी. >> उस Expert का शुभ नाम है... पाण्डेय MR. दिलीप जी. >> As a Friend भी ये एक बहूत अच्छे इंसान है. >> दिल की बाते खुलकर कहना इनकी मूल पहचान है. >> ग्रुप का प्रॉब्लम SOLVE करने में इनका बहूत योगदान है. >>मगर अपनी तारीफ़ करने से ये बहूत ज्यादा बचते है. >>तभी तो अपने दोस्तों को, ये बहूत ज्यादा जचते है. >> मैं जितना लिखूं होगा उनके बारे में बस BRIEF जी. >> उस Expert का शुभ नाम है... पाण्डेय MR. दिलीप जी. >> >> आपका एक फनकार दोस्त, >> नूरैन अंसारी >> >> >> >> -- >> Thanks & regards, >> Noorain Ansari >> >> >> >> >> -- >> Thanks & Regards, >> >> DILIP KUMAR PANDEY >> MBA-HR,B.Com(Hons),BCA >> Mobile: +91 9810929744 >> dilipan...@gmail.com >> dilipan...@yahoo.com >> New Delhi - 62, India >> >> >> >> >> -- >> Best regards, >> Ayush Jain >> >> -- >> >> -- >> 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=wall&ref=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=wall&ref=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 <><><><><><><><><
Re: $$Excel-Macros$$ VBA project password: Unprotect
Hi, Can anyone please assist me on this. Thanks, Sharma On Wed, Sep 29, 2010 at 10:08 AM, amrahs k wrote: > Hi Team, > > I have attached the spreadsheet in which VBA project was protected with > password. Due to this I am unable to edit the code as well. > > Kindly advice me to solve this issue. > > Please let me know whether any method to crack the VBA project password so > that I can apply the same in near future. > > Refer the attachment. Do the needful. > > Thanks, > Sharma > > -- > > -- > 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=wall&ref=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=wall&ref=ts
Re: $$Excel-Macros$$ Please explain that code Which I highlighted
Hi
Thanks Paul
On Wed, Sep 29, 2010 at 5:53 PM, Paul Schreiner wrote:
> this is a change event macro.
> whenever a cell is changed, this macro is called and Range of the cell (or
> cells)
> is passed to the macro as the variable name "Target".
>
> In this case, the macro is checking to see if the row or column of the
> cell changed is 3, then proceeds.
>
> note: if a group of cells is changed.. as in: deleted
> the macro will produce an error because the Target is then an array of
> ranges
> and target.row is not valid..
> I usually check first: if (Target.count = 1) then
>
>
> In the case of:
> * If Cells(Target.Row, 1).Value <> "" Then*
>
> the macro looks at column "A" of the row in which a cell is changed
> and checks to see if it is not blank before proceeding.
>
> hope this helps.
>
> Paul
>
>
> *From:* neil johnson
> *To:* Dave Bonallack ;
> excel-macros@googlegroups.com
> *Sent:* Wed, September 29, 2010 2:47:27 AM
> *Subject:* $$Excel-Macros$$ Please explain that code Which I highlighted
>
> Dear Excel member,
>
> I need your help. Can you explain that code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> *If Target.Row = 3 And Target.Column = 3 Then* ( What is that Means )
> 'calculate criteria cell in case calculation mode is manual
> Worksheets("ProductsList").Range("I3").Calculate
> Worksheets("ProductsList").Range("Database") _
> .AdvancedFilter Action:=xlFilterCopy, _
> CriteriaRange:=Sheets("ProductsList").Range("I2:I3"), _
> CopyToRange:=Range("A6:G6"), Unique:=False
> End If
> End Sub
>
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> *If Target.Column = 7 And Target.Row > 6 Then
> If Cells(Target.Row, 1).Value <> "" Then
> Target.Value = "X"*
> MoveRow
> 'MsgBox "Row has been copied"
> End If
> End If
>
> --
>
> --
> 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=wall&ref=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=wall&ref=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=wall&ref=ts
$$Excel-Macros$$ Re: Need next number with a twist
Build a "key" in column g. The formula for the key should be: =a2&b2&c2&f2 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 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=wall&ref=ts
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 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=wall&ref=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=wall&ref=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=wall&ref=ts
