Re: $$Excel-Macros$$ Backup file on every save
try this code and check if it helps Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False ' ---save the backup--- Me.SaveAs Filename:=F:\MyData\ Me.Name '---save the main file--- Me.SaveAs Filename:=D:\MyDocument\ Me.Name Application.DisplayAlerts = True End Sub On Mon, Dec 26, 2011 at 12:50 PM, Prakash Gusain gusainprak...@gmail.com wrote: Hi I am trying to find the code to create a backup of file on every occasion user saves the file. But I want to specify the location where the backup file will be created. I know the feature to create the backup of file but want to specify the location where it saves the backup. Thanks in advance. -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ List all Excel Workbooks
Hi Charlie, Please check and try this code, if it helps... '---standard Module- Sub *GenerateList*() ' sitiVi / jakarta 23 Dec 2011 ' in answering: ' http://groups.google.com/group/ _ ' excel-macros/browse_thread/thread/9dbda650f1d09d65# ' Dim ArDir As Variant, iDir As Integer ' please edit this list of Path.. ArDir = Array(*C:\MyData, F\, E:\, D\xFolder\yFolder*) ' ~~ Application.Calculation = xlCalculationManual Application.ScreenUpdating = False For iDir = LBound(ArDir) To UBound(ArDir) Call ListFiles(ArDir(iDir)) Next iDir Columns(A:B).EntireColumn.AutoFit Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Private Sub *ListFiles*(ByVal SpecifiedDir As String) ' sitiVi / jakarta 23 Dec 2011 ' in answering: ' http://groups.google.com/group/ _ ' excel-macros/browse_thread/thread/9dbda650f1d09d65# ' Dim vFName As Variant, RowN As Long RowN = Cells(1).CurrentRegion.Rows.Count If RowN = 1 Then RowN = 0 With Application.FileSearch .NewSearch .LookIn = SpecifiedDir .SearchSubFolders = True .FileType = msoFileTypeExcelWorkbooks REM .Execute If .Execute 0 Then For Each vFName In .FoundFiles RowN = RowN + 1 ActiveSheet.Cells(RowN, 1).Value = SpecifiedDir ActiveSheet.Cells(RowN, 2).Value = vFName Next End If End With End Sub '--end of code - Looking forward to hear if this code works (or not).. Thx, Best regards Wassalamualaikum wr wb. siti Vi On Fri, Dec 23, 2011 at 4:45 AM, Cab Boose swch...@gmail.com wrote: Hi There are plenty of solutions for listing open excel files. I would like to list all excel workbooks on my C: and F: drives. They are all over the place. My bad housekeeping! How do I get the complete address for each workbook ? either with a hyperlink or not. But do need to print out the list. Using Excel 2000 at the moment but soon hopefully to upgrade to 2003 Thanks Merry Christmas to all regards Charlie -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ How to add command button in excel....
visit to this site: http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010236676.aspx On Thu, Sep 8, 2011 at 11:55 PM, Madhukar madhukar.kalaha...@gmail.comwrote: Hi all, I'm new to VBA Programming. But how o add a command button in excel without using form. -- -- 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/discussexcel
Re: $$Excel-Macros$$ get name of months from date
assuming your date is in cell A2; also try this formula =TEXT(A2,[$-6000446]) Punjabi month name ?? On Sun, May 8, 2011 at 6:18 PM, Rajesh K R rajeshkainikk...@gmail.com wrote: Hi Experts If I want to get the name of month from dates 1/4/11 - April 5/5/11 - May how I can I do it with a formula or code Regards Rajesh Kainikkara -- -- 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/discussexcel
Re: $$Excel-Macros$$ discussexcel : Formula : Extract email address from sentence in cell
Dear Mr. Ayush Please try =TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND( ,A2 ,FIND(@,A2))-1), ,REPT( ,99)),99)) or (Array Formula:) =MID(LEFT(A2,FIND( ,A2 ,FIND(@,A2))),COUNT(SEARCH( *@,A2,ROW($1:$103)))+1,99) thank you and best regards siti Vi On Sat, Mar 26, 2011 at 9:52 PM, Ayush jainayus...@gmail.com wrote: Hello Group, Learn to extract email address from the string in the cell. Visit : http://www.discussexcel.com/functions Feel free to share ideas you might have for the same task. I will appreciate it. Thanks and 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/discussexcel find email address.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ File attachement
please tell me.. what is the previous / original subject of this case FILE ATTACHMENT as a subject is not make a sense anymore http://groups.google.com/group/excel-macros/browse_thread/thread/def081e4a88ac093 On Thu, Mar 10, 2011 at 9:49 PM, Cesar Delanoval cdelano...@gmail.comwrote: I am sending you the sample file that goes my latest post since I was not able to attach it or upload it on the group website. I appreciate all your help Thanks -- -- 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/discussexcel
Re: $$Excel-Macros$$ Delete extra information from a cell
please test if it helps Sub BlaBlaBla() 'siti Vi / jakarta, 2 mar 2011 Dim Dat As Range, r As Long, c As Integer Set Dat = Sheets(1).Cells(1).CurrentRegion For r = 1 To Dat.Rows.Count c = InStr(1, Dat(r, 1), 12 $, vbTextCompare) - 1 Dat(r, 2) = Left(Dat(r, 1), c) Next r End Sub assuming that your data is starts in A1 at sheet(1) / most left tab The result will be written in column B On Wed, Mar 2, 2011 at 12:39 AM, CAN cdelano...@gmail.com wrote: I have a .CSV file that contains all the data in column A. Each row has different information. I am looking for macro that will review the data on each row and whenever find the number 12 it will delete it along with all the information after. For example see data below 1 MF2-001 B Y MILAGROS ALVARADO 12 $140.00 01/01/11 $25.00 14 DAYS 2 MF2-002 B Y OLIVER B. INGRID Y. URQUIETA 12 $140.00 01/01/11 $25.00 14 DAYS 3 MF2-003 B Y LUCIA G. FERNANDEZ 12 $140.00 01/01/11 $25.00 14 DAYS 4 MF2-004 B Y JOHNNY BARAKAT 12 $140.00 01/01/11 $25.00 14 DAYS The data on each row is only in column A. In row 1 I need a macro that will delete all the words or caracters after the word Alvarado. In row 2 to delete all the words after Urquieta In row 3 to delete all the words after Fernandez, etc, etc. In other words to identify in each colum space+ 12 and delete anything that begins with 12 but leaving intact everything before. After the macro runs, the data must look like this. 1 MF2-001 B Y MILAGROS ALVARADO 2 MF2-002 B Y OLIVER B. INGRID Y. URQUIETA 3 MF2-003 B Y LUCIA G. FERNANDEZ 4 MF2-004 B Y JOHNNY BARAKAT The macro needs to loop until the last row. I will appreciate any help with this. Thank you in advance. -- -- 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/discussexcel
Re: $$Excel-Macros$$ Need Help!!!!!!!!!!!!!!!!!
That was Excel limitation: max number of criteria (unique values list)on Auto Filter = 1000 On 2/13/11, Aamir Shahzad aamirshahza...@gmail.com wrote: sheet attached, simply when you want to use auto filter you must select all your data till at the end. Regards, Aamir Shahzad On Wed, Feb 9, 2011 at 10:27 AM, Surendra shindaro...@rediffmail.comwrote: HI Team, I need your help, In attached file i fill the numbers from 1 to 1500 but when i see the data from Auto filter an -- -- 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/discussexcel
Re: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA
just =TEXT(E5,) On Wed, Feb 2, 2011 at 2:39 PM, solomon raju faithful1...@gmail.com wrote: Hi All, How can we extract month in text. Can someone define some formula for this. Attached my question in clear. Regards, Solomon -- -- 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/discussexcel
Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
Why not using available [Sort Ascending / Descending] buttons in Auto Filter ? On 1/26/11, San Pat myitems2...@gmail.com wrote: Hi All, I am working on a excel with data in many columns. I want to add two macro button on each heading to sort data, one for Ascending sort +and second for Descending sort. Is it possible with macro. I have attached the sample sheet. Please advice if it possible. Regards, San -- -- 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/discussexcel -- -- 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/discussexcel
Re: $$Excel-Macros$$ Macros for paste special-transpose
Sub Bang() Dim Rng As Range Set Rng = Cells(1).CurrentRegion Rng.Copy Rng(1).Offset(Rng.Rows.Count + 2, 0).PasteSpecial _ Paste:=xlPasteAll, Transpose:=True End Sub On Tue, Jan 25, 2011 at 10:12 AM, Manoj b manoj.bi...@gmail.com wrote: Hello All, Please can somebody advise me macros for paste special-transpose wherein i can just copy the data in horizontal format and paste them in vertical with a shortcut key. My data looks something like below in different sheets of same workbook 2009 2008 2007 1.2 0.84 0.94 and i want it in below format. 2007 0.94 2008 0.84 2009 1.20 Any help would be much appreciated. I am in an urgent need of this. Many Thanks Manoj -- -- 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/discussexcel
Re: $$Excel-Macros$$ How to get the address of a cell in VBA
Sub ABCFoundCell() Dim x As Range Set x = Cells.Find(What:=ABC, _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) MsgBox the cell address is : x.Address End Sub On Sat, Jan 22, 2011 at 4:56 AM, Mao yanjiemao...@gmail.com wrote: Hi there, I have a question about how to locate the address of the cell in VBA For example, I am looking for ABC in my sheet. By using FIND function, I got the location as A1. How could I write this location into one variable? So when later I am using ABC again, I could refer Range(A1) directly. Thanks Mao -- -- 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/discussexcel
Re: $$Excel-Macros$$ Re: Number sequentially, skipping blank cells
please check this formula (written in A2) =IF(A1=COUNTA($B$2:B2),,COUNTA($B$2:B2)) best regards, On Fri, Jan 7, 2011 at 1:06 PM, Rahul Gandhi myname.ra...@gmail.com wrote: Can you please let us know what exactly you require in output On Jan 7, 4:01 am, scoobysnack88 scoobysnac...@gmail.com wrote: Does anyone have a formula or vbs to accomplish the sequential numbering as listed here? The rows with no information would get a nill and the next cell would not break the number sequence. 1 5t67 2 g786 3 1234r 4 ty765 5 24567u 6 78ytr89 7 56ytre 8 re45789 9 2ewqs78 10 45iyunhg -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts sequence numbering with blank rows.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ how to get month Occurrence in no.
If the word April or *another month name *is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE(1 A1 2010),M) On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young rohan.j...@gmail.com wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ How to attach file
Sorry for OOT.. Compose and Send your mail from your pc' GMail, not from the group web page. cmiiw On 11/19/10, Chandru chandrashekarb@gmail.com wrote: Hi, How to attach file in this group Thanks Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Time in Decimal format
just multiply by 24 time 3:48 in cell A1 =A1*24 resulting: 3.80 that is equal to:3 + 48/60 not 4.48 CMIIW On Fri, Oct 15, 2010 at 10:55 AM, Vijay Kr. Aggarwal vjaggarwal2...@gmail.com wrote: Hi Friends, Please let me know that how can I write time in decimal format. Time :- 3:48 (3 hours 48 mins) then it should show 3.48 Thanks in advance Regards, Vijay -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Define a Range based on a Range object
Set xRange = Range(C1:D10) xRow = 5 Set yRange = xRange.Offset(xRow-1, 0).Resize(xRow+1, xRange.Columns.Count) CMIIW On Fri, Oct 15, 2010 at 4:06 AM, TerryP webtour...@gmail.com wrote: Given a Range object (e.g: set xRange = Range(C1:D10) ) And given xRow = 5 , which stands for Row 5 (absolute Row index) How to define a range yRange based on xRange so that yRange is Range(C5:D10) ? thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Seemingly simple thing.....
ActiveWorkbook.PrecisionAsDisplayed = True tmp() = Range(A1:A20) but, PrecisionAsDisplayed = True will change your data in your sheets On Fri, Oct 15, 2010 at 1:49 PM, The Frog mr.frog.to@googlemail.comwrote: Hi everyone, First time posting to this group. Glad to have found it. I have a question regarding capturing cell data into an array in VBA. If I use the following code: tmp() = Range(A1:A20) I can capture the values of these cells into my array. This seems to produce a double as the data type, and in my current situation there are many digits after the decimal place. The displayed value on the sheet is formatted so that only one decimal place is shown. What I would like to do is to capture the values into the array as shown on the worksheet. Does anyone have a 'rapid' way of achieving this rather than cell by cell iteration? I need to keep this macro as fast as possible as it can be working with many tens of thousands of cells. The above code captures the range into the array near instantly. Cheers The Frog -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Macro to run on specific day.
only work on day 5 to 10 of the month sub blablabla() if day(date) = 5 and day(date) =10 then * *your full macro* * end if end sub On Wed, Oct 13, 2010 at 12:26 PM, C.G.Kumar kumar.bemlmum...@gmail.com wrote: Dear All, I want my macro to be executed only for specific day in a month based on system date and it should not run thereafter. Kindly let me know the code for it.It's urgent. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ significance of sign in formula
Your formula is an ARRAY FORMULA
To enter the Array Formula you have to press *[Ctrl] , [Shift] + [Enter]
button
*(not only just [Enter]
learn more : http://www.cpearson.com/excel/ArrayFormulas.aspx
On Tue, Oct 12, 2010 at 1:59 PM, shantanu chouhan
chouhanshanta...@gmail.com wrote:
Dear all experts
i am using a sheet where a cell containg a formula
cell:{=SUMPRODUCT(IF(($G9:$AS9=BV$3)*($G9:$AS90),1,0),$G$4:$AS$4)}
where if i duble click or delete this sign {} the out come is
cell:#VALUE!
shatanu
please help me out
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Counting Unique Items
if your list of data (some data = duplicated data) are stored in C3:C26 then *The Count of Unique Values *is *=SUM(1/(COUNTIF(C3:C26,C3:C26)))* best regards siti On Tue, Oct 12, 2010 at 7:13 AM, MikeMikeMike michael.lovel...@gmail.com wrote: I am having a problem with a forumla I am hoping someone out there can help me with. What I am trying to do is use a vlookup to give me the amount of unique stores attributed to an email address. OR if it is better to use one of the many SUM options (which one?) to create an additional column to get the SUM I am looking for on WORKSHEET 2 and then use a vlookup to pull that data into WORKSHEET 1? WORKSHEET 1 Column1 = email address Column2 = This is the column I want to show the number of unique stores that are attributed to the email address (There is a many to many relationship. Many email addresses can be attributed to many stores.) WORKSHEET 2 - The stores. Column1 = email address Column2 = store_name Any help is most appreciated. Thank you! MikeMikeMike -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts ctv_Count of Unique Values.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$
Dear shrinivas, you can put this formula *=B4+BeforeThisSheet(C4)* and copy to another sheet (except sheet1 / *most left* tab sheet in workbook) *BeforeThisSheet* is an UDF ( = User defined function) that we can create with VBA Function BeforeThisSheet(RangeTxt As String) ' siti Vi / jakarta, 0ct 10, 2010 Dim TheSheet As Worksheet Set TheSheet = Sheets(Application.ThisCell.Parent.Index - 1) BeforeThisSheet = TheSheet.Range(RangeTxt) End Function On Fri, Oct 8, 2010 at 1:49 PM, shrinivas shevde shrinivas...@hotmail.com wrote: Dear All Please help me on given problem Problem I have many sheet in File(more than 30) In Shhet 1 ,C4 =B4 In Sheet 2 C4=B4 of sheet2 + C4 of sheet 1 If I copy paste this pformula in sheet 3 it will take as C4= B4 of Sheet 3+C4 of sheet 1 but actually I want C4= B4 of Sheet 3+C4 of sheet 2 (That is Previous sheet) Currently I can not copy paste this formula I have to write it Can Any one help me shrinivas -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts ctv_Sheet_BeforeThisSheet.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ MS Excel help needed for newbie
*formula in cell A2 *
=CHOOSE(MATCH(TRUE,A1={A,B,C,D,E},0),10,30,50,70,90)
*formula in cell A3*
=A2+10
On Thu, Oct 7, 2010 at 1:30 PM, Dean Brown bramfiel...@googlemail.comwrote:
I have several questions so if it's ok with you I'll ask then one at a
time (that'll give me time to digest any answers).
Ok, here's problem 1.
I want the person using the spreadsheet to input a value into cell A1.
The value that is input is going to be either A,B,C,D or E. The values
of cells A2 and A3 will vary depending on what is input into A1.
Basically (and in simple terms) what I want the formula to work out is
this:
If A1=A then A2=10 and A3=20
If A1=B then A2=30 and A3=40
If A1=C then A2=50 and A3=60
If A1=D then A2=70 and A3=80
If A1=E then A2=90 and A3=100
I hope this makes sense. Any help is greatly appreciated.
Regards.
Dean.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ MS Excel help needed for newbie
or, another formula in A2
*=MATCH(TRUE,A1={A,B,C,D,E},0)*20-10*
*
*
***
*
On Thu, Oct 7, 2010 at 4:23 PM, siti Vi villager.g...@gmail.com wrote:
*formula in cell A2 *
=CHOOSE(MATCH(TRUE,A1={A,B,C,D,E},0),10,30,50,70,90)
*formula in cell A3*
=A2+10
On Thu, Oct 7, 2010 at 1:30 PM, Dean Brown bramfiel...@googlemail.comwrote:
I have several questions so if it's ok with you I'll ask then one at a
time (that'll give me time to digest any answers).
Ok, here's problem 1.
I want the person using the spreadsheet to input a value into cell A1.
The value that is input is going to be either A,B,C,D or E. The values
of cells A2 and A3 will vary depending on what is input into A1.
Basically (and in simple terms) what I want the formula to work out is
this:
If A1=A then A2=10 and A3=20
If A1=B then A2=30 and A3=40
If A1=C then A2=50 and A3=60
If A1=D then A2=70 and A3=80
If A1=E then A2=90 and A3=100
I hope this makes sense. Any help is greatly appreciated.
Regards.
Dean.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ save as cell A1 to to a specific drive - BUT excel then closes - why ?
Dim myPath As String, ThisFile As String myPath = H:\Temp\ ThisFile = myPath Range(A1).Value Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=ThisFile Application.DisplayAlerts = True On Sat, Oct 2, 2010 at 6:34 PM, Johnnyboy5 intermediatec...@gmail.com wrote: Hi When I use the macro below it does save the file as per cell A1 and it does save it to the chosen drive. But it then closes Excel. On checking the file location it is saved there. Any ideas what’s wrong. Thanks John Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileSaveName ChDrive H ChDir H:\Temp ThisFile = Range(A1).Value ActiveWorkbook.SaveAs Filename:=ThisFile 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=wallref=ts
Re: $$Excel-Macros$$ Please Help
please check the attached workbook On Mon, Sep 27, 2010 at 11:02 PM, hemant shah hemanthin...@gmail.com wrote: Hi Team, Can you please help on the attached file. I have explained my query in the attached sheet. Regards, Hemant Shah -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Change Data in transpose form
try this code Sub dosomething() '* siti Vi / jakarta, sept 24, 2010* Dim refTbl As Range Dim DesTbl As Range Dim r As Long, i As Long, c As Long Set refTbl = Cells(1, 1).CurrentRegion Set DesTbl = refTbl(1, 1).Offset(0, refTbl.Columns.Count + 3) For r = 2 To refTbl.Rows.Count If Not refTbl(r, 1) = refTbl(r - 1, 1) Then c = c + 1 i = 0 DesTbl(1, c) = refTbl(1, 1) DesTbl(2, c) = refTbl(r, 1) DesTbl(3, c) = refTbl(r, 2) Else i = i + 1 DesTbl(3 + i, c) = refTbl(r, 2) End If Next r End Sub On Wed, Sep 22, 2010 at 6:04 PM, Deepak Rawat deepakexce...@gmail.com wrote: Dear All Pls find the attached file and do the needful I have data in two columns and has to split it, in what form i have mention inside the file Regards, Deepak -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe ctv_Change in transpose form.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Show Values in List
put this formula into SourceBox in the Data Validation List =OFFSET($B$1,MATCH($E$2,$A$2:$A$17,0),0,COUNTIF($A$2:$A$17,$E$2),1) [image: Formula for List Source.PNG] please check the attachaed workbook best regards, siti Vi On Sun, Sep 19, 2010 at 2:12 PM, Deepak Rawat deepakexce...@gmail.com wrote: Dear all I attached a sample file, the problem is there are two lists one is Sales_head other is cities comes under that sales head my problem is when i select the sales head in one list then all the cities should show under that sales head in another list. Please suggest the solutions Regards, Deepak -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Formula for List Source.PNG
Re: $$Excel-Macros$$ Cubic spline interpolation macro in VBA
http://www.ozgrid.com/Excel/excel-interpolate-cubic-curve-fit.htm http://www.brothersoft.com/excel-cubic-spline-16499.html http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm On Sun, Sep 19, 2010 at 2:04 AM, Pratik pratik...@rediffmail.com wrote: Hi, Can anybody help me find VBA macro to perform cubic spline interpolation? -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Hi
you have to send your posting from your email-client, not from the group web. On Thu, Sep 16, 2010 at 4:03 PM, dinoabeer umarab...@googlemail.com wrote: Can somebody help. I m a newbie here. how do I upload I file when I post something -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Column Data in Rows Table
*(1) : Macro VBA-Excel Solution* Private Sub CommandButton1_Click() Dim SrceTbl As Range, DestTbl As Range Dim r As Long, nr As Long, c As Integer Set SrceTbl = Sheets(Sheet1).Cells(1, 1).CurrentRegion Set DestTbl = Sheets(Sheet2).Cells(2, 2) For r = 2 To SrceTbl.Rows.Count For c = 2 To SrceTbl.Columns.Count nr = nr + 1 DestTbl(nr, 1) = SrceTbl(r, 1) DestTbl(nr, 3) = SrceTbl(2, c) DestTbl(nr, 2) = SrceTbl(1, c) Next c Next r End Sub *(2) : FORMULA Solution* Column 1 =OFFSET($B$4,CEILING(ROW(A1),5)/5,0) Column 2 =OFFSET($B$4,0,MOD(ROW(A1)-1,5)+1) Column 3 =OFFSET($B$4,CEILING(ROW(A1),5)/5,MOD(ROW(A1)-1,5)+1) On Wed, Sep 8, 2010 at 5:38 PM, Sayyad1284 anamika2...@gmail.com wrote: Hi Dave , Thanks but if you see the output I am expecting I need data in 3 columns ..i.e. column headings also need to displayed against each cell On Sep 8, 2:02 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi Sayyad, Have a look at the attached. Click the button on sheet 2. Regards - Dave Date: Tue, 7 Sep 2010 23:30:20 -0700 Subject: $$Excel-Macros$$ Column Data in Rows Table From: anamika2...@gmail.com To: excel-macros@googlegroups.com Hi , I have a case where I get the data in following format Name A B C D E AA 10 14 11 13 14 AB 13 13 14 14 12 AC 13 10 14 11 14 AD 14 10 10 13 13 I need to convert the above format to the data in following format. The no columns no of rows in the data vary, can you help me to get this done AA A 10 AA B 14 AA C 11 AA D 13 AA E 14 AB A 13 AB B 13 AB C 14 AB D 14 AB E 12 AC A 13 AC B 10 AC C 14 AC D 11 AC E 14 AD A 14 AD B 10 AD C 10 AD D 13 AD E 13 -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe ctv_Sayyad (Formulas).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Date series
Array Formula =TRANSPOSE(ROW(INDIRECT(B3:C3))) On Mon, Sep 6, 2010 at 2:47 PM, Kal xcel kalx...@gmail.com wrote: Dear experts, I am sending a file where I need to show a series of dates after giving start date end date. File is attached . Thanks in advance. Kalyan -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe ctv__Series of Date.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Date series
or if we want result as a number (not a date type) Array Formula =DAY(TRANSPOSE(ROW(INDIRECT(B3:C3 On Mon, Sep 6, 2010 at 2:47 PM, Kal xcel kalx...@gmail.com wrote: Dear experts, I am sending a file where I need to show a series of dates after giving start date end date. File is attached . Thanks in advance. Kalyan -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Question about excel report
try this formula =MID(CELL(address,Sheet1!A1),FIND(],CELL(address,Sheet1!A1))+1,99) = Sheet1!A1 On Fri, Sep 3, 2010 at 6:08 AM, jjsmd jschwart...@gmail.com wrote: Hi: I am new to excel and I was wondering hiw I would do the following. I have an excel worksheet with names of people in a sheet going from column A to X and row 1 to 10. I would like to get a worksheet showing the location and then name. Ex: A1=John, A2=Smith and so on. Is this easily done? and if so how? Thanks -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Cell Address and Cell Value.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ sumif kinda formula needed
Yes, DATEDIF is a worksheet function available in Excel please visit to: http://www.cpearson.com/excel/datedif.aspx The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. On Fri, Sep 3, 2010 at 6:00 PM, shrinivas shevde shrinivas...@hotmail.com wrote: Dear siti vi/Group Member Plesae let me know What is the function DATEDIF ? Is this function available in Excel? If not How u insert function in Excel. Thanks in advance shrinivas Date: Fri, 3 Sep 2010 09:29:35 +0700 Subject: Re: $$Excel-Macros$$ sumif kinda formula needed From: villager.g...@gmail.com To: excel-macros@googlegroups.com Formula for AGE and UDF for SUMMARY see attachments -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Re: I love excel because.......
I love Excel because She is very funny -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ mirror worksheets
Hi, You can work in a GROUP of SHEETS f.x. Multi-Select: Sheet1 and Sheet2 then you need to work in Sheet1 only On Mon, Aug 30, 2010 at 3:31 PM, Steen matlab@googlemail.com wrote: HI I would like to mirror a worksheet to another worksheet only certern cells and if you in the main sheet inserts a new rows this will atomaticly also bee inserted in the other cell f.x by using an insert button that insert a row above the selected cell, both in the main sheet and also in the mirroed cell with correct cell mirroring once agian many thanks for your help cheers Steen -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ User defined function for text formatting.
Unfortunately a FUNCTION can not DO a method Function can only returns a Value CMIIW To format a range you need a SUB Procedure not Function Procedure best regards, siti On Tue, Aug 31, 2010 at 2:32 AM, Silviu silviu.cioci...@gmail.com wrote: Hello I'm trying to find out how to build some user defined functions for text formatting. Something like: customstyle(bold,arial,18,cell) I want to use these type of functions in order to apply them in a concatenate function. Something like: concatenate(this would the best,customtype(bold,arial,18,A1)) for A1 containing any text or number. Is this possible and if yes, could anyone give me some hints? Thank you very much. -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ fonts in combobox keeps becoming smaller as it is clicked
You can set the Font Size of your Combobox (activeX control) object For example: ComboBox1.Font.Size = 12 On Tue, Aug 31, 2010 at 8:27 AM, Meimei xxu8810...@gmail.com wrote: I am writing a simple vba program. I used a few comboboxes in the program. But I found that as I click on the combobox, the font size in the drop-down list gets smaller each time, and after a few clicks, the font is too small to even see. Does anyone know why it happens? Thank you! -- -- 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 HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Insert Blank Rows after detecting a specific text string
UCase function Returns a Variant (String) containing the specified string, converted to uppercase. Syntax: UCase(string) Example Range A10 contain text data : tOtaL Cost x = UCase(Left(Range(A10)),5) return x = TOTAL x = Left(Range(A10),5) returns x = tOtaL comparing UCase(Left(Range(A10)),5) = TOTAL returns TRUE comparing Left(Range(A10)),5) = TOTAL returns FALSE CMIIW best rgds siti On May 14, 12:50 am, John Whetstone johnw...@gmail.com wrote: Thanks for the code... I am trying it out it all makes sense to me, except UCase -- what is it? It's not a function... and when I run this code , nothing happens -- can you explain a little more? Sorry, but I am a novice... On Wed, May 12, 2010 at 6:55 PM, siti Vi villager.g...@gmail.com wrote: Sub Bla_Bla_Bla() Dim MyRng As Range, n As Long Set MyRng = ActiveSheet.UsedRange For n = MyRng.Rows.Count To 1 Step -1 If UCase(Left(Trim(MyRng(n, 1)), 5)) = TOTAL _ Then MyRng(n + 1, 1).EntireRow.Insert Next End Sub NOTE: to insert a row you have to say the range object first: RangeObject.EntireRow.Insert -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Delete rows and concatenate values.
formula : =CONCATIF($A$2:$A$18,D2,$B$2:$B$65) vba code of ConcatIf UDF : Function ConcatIf(Rang1 As Range, Crite As Range, _ Optional Rang2 As Range = Nothing, _ Optional Dlmtr As String = ; ) As String '--- ' siti Vi / UDF CONDITIONAL CONCATENATE ' bluewater, indonesia May 13 2010 '--- Dim Cel As Range, Hasil As String, n As Long If Rang2 Is Nothing Then Set Rang2 = Rang1 For n = 1 To Rang1.Rows.Count If Rang1(n, 1) = Crite Then If Rang2(n, 1) And Rang2(n, 1) ? Then Hasil = Hasil Dlmtr Rang2(n, 1) End If End If Next n If Len(Hasil) 0 _ Then ConcatIf = Right(Hasil, Len(Hasil) - Len(Dlmtr)) _ Else ConcatIf = Hasil End Function From: maryann jiangqiyangf...@gmail.com Date: Wed, 12 May 2010 07:56:52 -0700 (PDT) Local: Wed, May 12 2010 9:56 pm Subject: $$Excel-Macros$$ Delete rows and concatenate values. Dear VBA guru, I have a table like below: col A ColB 1 ? 1 ABD 2 ADC 2 2 ADD I would like to have results as: 1 ABD 2 ADC; ADD In other words, I would like to delete the rows with empty cell and question marks, or other unwanted values (ideally, there will be a pop up window shows the value criteria that I want to delete the rows). Also, if possible, concatenate the value in column B so that values in column A is unique. Thanks a lot! Maryann -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe ctv_UDF_ConcatIf.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel help : Remove Page Breaks Using VBA code
I think, removing VPageBreaks and HPageBreaks can be done by setting FitToPage /Wide/Tall properties to : 1 Sub ClearPgBreasks() With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With End Sub On May 12, 8:33 pm, Sundarvelan N nsund...@gmail.com wrote: Hello Friends, Can anyone help me to remove Page Breaks in Excel sheets Using VBA code. Thanks, N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Insert Blank Rows after detecting a specific text string
Sub Bla_Bla_Bla() Dim MyRng As Range, n As Long Set MyRng = ActiveSheet.UsedRange For n = MyRng.Rows.Count To 1 Step -1 If UCase(Left(Trim(MyRng(n, 1)), 5)) = TOTAL _ Then MyRng(n + 1, 1).EntireRow.Insert Next End Sub NOTE: to insert a row you have to say the range object first: RangeObject.EntireRow.Insert On May 12, 10:38 pm, John in Huntington Beach johnw...@gmail.com wrote: Hello, I have data that contains certain text strings (Total followed by other text) repeated in column A. When my progam finds this text string, I want it to insert a blank row below the row that contains the text. My macro looks like this: Set CELLToCHECK = FirstCellR /comment - FirstCellR is in column A I = 0 Do Until I = LastRowL CELLToCHECK = CELLToCHECK.Offset(I, 0) MsgBox CELLToCHECK VALUE = CELLToCHECK.Value If CELLToCHECK.Value = Total * Then MsgBox Here's one = CELLToCHECK.Address(False, False) Insert.Row End If I = I + 1 Loop This is not working any suggestions? thanks in advance -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Need a code or macro urgent
A formula cannot performs an action / method like INSERT ROW It only returns DATA(s). So, you need a macro '-- Sub InsertRowBelowYes() ' by siti Vi / may 12, 2010 Dim MyRng As Range, r As Long, c As Integer Set MyRng = ActiveSheet.UsedRange For r = MyRng.Rows.Count To 1 Step -1 For c = 1 To MyRng.Columns.Count If LCase(MyRng(r, c).Value) = yes Then MyRng(r + 1, c).EntireRow.Insert Exit For End If Next c Next r End Sub On May 11, 5:56 pm, Puttu puttu...@gmail.com wrote: Hi Experts, I need help , I need a macro or formula where as in excel where ever I found YES it should insert additional row bleow to that YES. Can you please help me. Thanks Puttu -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ sum/count the number of times a name has occured
Assuming your list of names are in A2:A6 =COUNTIF($A42:$A$6,Bill) returns the number of Bill in your above mentioned range. On May 12, 3:00 am, nyberg75 nyber...@gmail.com wrote: Hi How can i sum/count the number of times a name has occured Example: John Bill Gordon Bill Bill Sara Bill John Result: Bill = 4 times John = 2 times -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ VBA code to delete all dates before last week
Assuming that : * YOUR LIST OF DATE are in a Range * to delete means to clear the content of a cell containing Date data Sub DoSomething() Dim MyRange as Range, Xel As Range Dim LastWeekWednesday As Date '--Please edit the code for your Range Set MyRange = Sheets(Bla).Range(A2:A65533) LastWeekWednesday = Date - Weekday(Date) - 3 For Each Xel In MyRange If Xel.Value LastWeekWednesday Then Xel.ClearContents Next End Sub On May 6, 8:08 pm, Sundarvelan N nsund...@gmail.com wrote: Hi Friends, Please help me to delete the date which are older than last week wednesday. Also i need a code to remove page breaks except the below recoded code ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 Thanks N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ prima and non-prima
Dim ArrDevisors() Sub BlaBlaBlaBla() ' siti Vi / bluewater, indonesia / 7 May 2010 Dim MyCell As Range Dim isPrima As String, n As Long Set MyCell = Selection isPrima = IsPrimeNumber(MyCell.Value) MyCell(1, 4) = Result: MyCell(1, 5) = isPrima If isPrima = NON-PRIMA Then For n = 1 To UBound(ArrDevisors) MyCell(1 + n, 5) = ArrDevisors(n) Next n End If End Sub Private Function IsPrimeNumber(aNumber As Long) As String ' siti Vi / bluewater, indonesia /14 Dec 2005 Dim AreYou As Boolean Dim Devisor As Long Dim i As Long AreYou = True For Devisor = 2 To aNumber If aNumber Mod Devisor = 0 Then AreYou = False i = i + 1: ReDim Preserve ArrDevisors(1 To i) ArrDevisors(i) = Devisor End If Next If aNumber = 0 Then AreYou = False IsPrimeNumber = IIf(AreYou, PRIMA, NON-PRIMA) End Function ' NOTE: '-- before running the makro (BlaBlaBla procedure) '-- you have to select the cell (single cell) containing the number. On May 7, 12:56 pm, L- Van elvan.bys...@gmail.com wrote: hi all, im new member in this group.. I want to find a number of non-prime numbers.. (please see attachment)http://groups.google.com/group/excel-macros/web/prima_nonprima-EXCELM... example READ 45 RESULT NON-PRIMA 3 5 9 15 … if i read the number 15 the result will be NON-PRIMA and the below cell (optional) will create a number that can be divide by read number Thanks for all of ur attention L-van -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ prima and non-prima
there is a little correction in my code '--- Dim ArrDevisors() Sub BlaBlaBlaBla() ' siti Vi / bluewater, indonesia / 7 May 2010 Dim MyCell As Range Dim isPrima As String, n As Long Set MyCell = Selection isPrima = IsPrimeNumber(MyCell.Value) MyCell(1, 4) = Result: MyCell(1, 5) = isPrima If isPrima = NON-PRIMA Then For n = 1 To UBound(ArrDevisors) MyCell(1 + n, 5) = ArrDevisors(n) Next n End If End Sub Private Function IsPrimeNumber(aNumber As Long) As String ' siti Vi / bluewater, indonesia /14 Dec 2005 Dim AreYou As Boolean Dim Devisor As Long Dim i As Long AreYou = True For Devisor = 2 To aNumber - 1 If aNumber Mod Devisor = 0 Then AreYou = False i = i + 1: ReDim Preserve ArrDevisors(1 To i) ArrDevisors(i) = Devisor End If Next If aNumber = 0 Then AreYou = False IsPrimeNumber = IIf(AreYou, PRIMA, NON-PRIMA) End Function '- On May 7, 12:53 pm, L- Van elvan.bys...@gmail.com wrote: hi all, im new member in this group.. I want to find a number of non-prime numbers.. (please see attachment) example READ 45 RESULT NON-PRIMA 3 5 9 15 … i read the number 15 the result will be NON-PRIMA and the below cell (optional) will create a number that can be divide by *read number* Thanks for all of ur attention L-van -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Macro help : How to remove Page Break
try: Sheets(MySheet).UsedRange.PageBreak = xlPageBreakNone -- On May 4, 6:09 pm, Sundarvelan N nsund...@gmail.com wrote: Hi Friends, Please help me to solve the final step of my project. I need to remove the page break lines to the end of the column upto which the data is available. Thanks, N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Groups : date from last Sunday To Saturday
The below procedure will create a list of current Work-Week-Date in one-ColumnRange (6 cells) starting at the active cell Sub ListOfWorkWeekDate() Dim dtLastSunday As Date Dim i As Byte dtLastSunday = Date - Weekday(Date) + 1 For i = 1 To 6 ActiveCell(i, 1) = dtLastSunday + i Next End Sub On Apr 23, 3:38 pm, Sundarvelan N nsund...@gmail.com wrote: Hi Friends, The below code is to return the date from last monday to saturday. Dim dtWeekAgo As Date Dim dtWeekAgoMonday As Date Dim dtLastSunday As Date Dim dayOfWorkWeek As Integer dtWeekAgo = Date - 7 dayOfWorkWeek = Application.Weekday(dtWeekAgo, 3) dtWeekAgoMonday = dtWeekAgo - dayOfWorkWeek dtLastSunday = dtWeekAgoMonday + 5 I need the code to be altered to give date from last Sunday To Saturday Please help me Thanks, N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel Groups : date from last Sunday To Saturday
updating my previous code Sub ListOfWorkWeekDate() Dim dtLastSunday As Date Dim i As Byte dtLastSunday = Date - Weekday(Date) + 1 For i = 1 To 6 ActiveCell(i, 1).NumberFormat = , dd mmm ActiveCell(i, 1) = dtLastSunday + i Next End Sub On Apr 23, 3:38 pm, Sundarvelan N nsund...@gmail.com wrote: Hi Friends, The below code is to return the date from last monday to saturday. Dim dtWeekAgo As Date Dim dtWeekAgoMonday As Date Dim dtLastSunday As Date Dim dayOfWorkWeek As Integer dtWeekAgo = Date - 7 dayOfWorkWeek = Application.Weekday(dtWeekAgo, 3) dtWeekAgoMonday = dtWeekAgo - dayOfWorkWeek dtLastSunday = dtWeekAgoMonday + 5 I need the code to be altered to give date from last Sunday To Saturday Please help me Thanks, N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Excel-Macro : Help
Subtotal ?? Sub SortAndSubTotalOnColumn4() Cells(1).CurrentRegion.Select Selection.Sort _ Key1:=Range(A2), Order1:=xlAscending, _ Key2:=Range(B2), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortNormal Selection.Subtotal _ GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Cells(1).Select End Sub On Apr 25, 6:49 pm, Sundarvelan N nsund...@gmail.com wrote: Hi Friends, I need a macro to sort the below excel by client and patient in Ascendingorder, and insert blank row between each client and Groos Assign column to $. Please help me to do this work by macro Thanks, N.Sundarvelan -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Permutations and combinations
may be you need a program like this INPUT: 123 OUTPUT: 123, 213, 312, 132, 231, 321 Private Sub ArrangeAndWrite(ByVal D, i As Byte) '-- ' siti Vi / jurangmangu / 16 mar 2008 ' this is a recursive procedure '-- Dim txt As String, tmp As String * 1, j As Byte ' limitation (if i = N) If i = N Then ' text arrangement For j = 1 To N: txt = txt D(j): Next j ' change oCol when If oRow = MaxRow Then oRow = 0: oCol = oCol + 1 End If ' Output / writing the arrangement to sheets'cell) oRow = oRow + 1: Tulis(oRow, oCol) = txt Else For j = i To N ' re-Arrange Data /change betwenn 2 data tmp = D(j): D(j) = D(i): D(i) = tmp '--- i coll myself --- ArrangeAndWrite D, (i + 1) '--- Next j End If End Sub You can down load my related workbook at http://www.box.net/shared/svi1e6c6x7 On Apr 15, 2:21 am, MD18358 michael.e.dray...@citi.com wrote: Hi All: I don't want the number of Permutations I want to see the permutations to a calculated sum. For example: How many different ways can I get 20 by looking at the number 1 through 19. My answer should look like: 1+19 2+18 3+17 4+16 ...etc Any ideas??? As always, Thanks Michael -- -- 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Named Ranges
Dear Grup, '---naming a range, example Dim NotCtr1 As Range Dim IsSameRange as Boolean Set NotCtr1 = Range(A1:C2) Range(A1:C2).Name = Ctr1 '---refering a named range, example Range(Ctr1).interior.ColorIndex = 16 IsSameRange = (NotCtr1 = Range(Ctr1)) MsgBox IsSameRange On Feb 25, 10:36 am, Harpreet Singh Gujral harpreetguj...@gmail.com wrote: Hi Group, In order to name ranges i use the following code: ActiveWorkbook.Names.Add Name:=Ctr1, RefersToR1C1:= _ Strshtname R1C1:R2C3 It uses R1C1 referrencing, can we use the other referrencing for this code Thanks Harpreet Gujral -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
