$$Excel-Macros$$ calculate multiple value in one cell
Hi, I have multiple value in one cell for example. *A1* 1*7+5 12*57+54 12+854 14+47*1 I want result below type by formula VBA. *B1* 12 738 866 61 Regards, Anjul -- -- 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$$ calculate multiple value in one cell
Dear Anjul, Please see attached sheet, hope it will help to u... Step 1 Select Cell B1 2 Formula-Define Name-Refers to-type-=Evaluate($A2) 3 Give Name Range 4 Use Name Range in B1 Cell to Output -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Oct 27, 2011 at 1:10 PM, Anjul Porwal porwalan...@gmail.com wrote: Hi, I have multiple value in one cell for example. *A1* 1*7+5 12*57+54 12+854 14+47*1 I want result below type by formula VBA. *B1* 12 738 866 61 Regards, Anjul -- -- 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 Solution.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Advanced Filter
Hi Experts, I have some records in sheet 1 and some in sheet 2. Now I want all those records which are in sheet 2 but not in sheet 1 and result to be saved in sheet 2 Thanks Akhilesh Airen -- -- 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
$$Excel-Macros$$ Advanced Filter
Hi Experts, I have some records in sheet 1 and some in sheet 2. Now I want all those records which are in sheet 2 but not in sheet 1 and result to be saved in sheet 2 Thanks Akhilesh Airen -- -- 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$$ Advanced Filter
Dear Airen, Please see attached sheet, hope it help to u. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Oct 27, 2011 at 1:47 PM, airen airen1...@gmail.com wrote: Hi Experts, I have some records in sheet 1 and some in sheet 2. Now I want all those records which are in sheet 2 but not in sheet 1 and result to be saved in sheet 2 Thanks Akhilesh Airen -- -- 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 Example.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Advanced Filter
Hi Noorain, Thanks for your reply. Provided solution wont work, I dont want empty rows in between. Is there any VBA code?? 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$$ Advanced Filter
try consolidate pivot table option.. http://tipsindeed.com/excel/data-consolidation-in-excel-made-easy-using-pivot-tables.html On Thu, Oct 27, 2011 at 4:40 PM, airen airen1...@gmail.com wrote: Hi Noorain, Thanks for your reply. Provided solution wont work, I dont want empty rows in between. Is there any VBA code?? 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 -- . -- -- 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
$$Excel-Macros$$ Time between
Hi, I´m using the code below. Cells(i,6) has hour data, 09:06 for example. I need to transfer this value to another cell , but between 09:06. The code below result in: hour=0,4048611 however i want hour=09:06 Coud anyone help me with this simple problem? Best regards Sub xml() Set r = Range(A1:J60) For i = 5 To 60 If r.Cells(i, 1) Then Cells(i, 15).Value = hour=Cells(i,6 ) End If Next i 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/discussexcel
Re: $$Excel-Macros$$ Urgent help needed
Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. === Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = Master Sheet For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, h) = Sheets(i).Name .Cells(dlr, H).Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com From: Anil Bhange Sent: Thursday, October 27, 2011 5:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub Anil Bhange Assistant Manager Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.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/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$$ Time between
First of all, you need to understand that to Excel, time is simply a fraction of a day. So, 9:00am is really 9/24 of a day, or .375 9:06:24 is 0.379 of a day... when you SEE the cell as a time: 9:06 AM, it is a display format. You're DISPLAYING the number as a time. you could enter ANY number: 40515.225997274 And the Integer portion is the number of days since 1/1/1900 40515 = December 3, 2010 and the decimal portion (.225997274) is the time: 5:25:26 AM So, the problem is that cells(i,6) is returning the actual VALUE of the cell, not what is displayed. if you want the number represented as an hour, you'll need to convert it using hour() as in: Cells(i, 15).Value = hour=hour(Cells(i,6 )) also, the line: Set r = Range(A1:J60) isn't required You could use: Sub xml() Dim I For I = 5 To 60 If Cells(I, 1) Then Cells(I, 15).Value = hour=Hour(Cells(I, 6).Value) End If Next I End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eduardo Cereja cerejaedua...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, October 27, 2011 8:05:22 AM Subject: $$Excel-Macros$$ Time between Hi, I´m using the code below. Cells(i,6) has hour data, 09:06 for example. I need to transfer this value to another cell , but between 09:06. The code below result in: hour=0,4048611 however i want hour=09:06 Coud anyone help me with this simple problem? Best regards Sub xml() Set r = Range(A1:J60) For i = 5 To 60 If r.Cells(i, 1) Then Cells(i, 15).Value = hour=Cells(i,6 ) End If Next i 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/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$$ Urgent help needed
I agree with Don. The first thing I do is delete all requests that do not describe the problem in the subject line. Posting Tips: Subjects like Urgent Help Needed convey no valueable information. the problem isn't urgent for ME, and if the poster didn't need help, he/she wouldn't be posting. Next, I consider myself an expert in many facets of Excel and VBA, but I have very little experience in Pivot Tables. Therefore, I skip questions outside of my areas of expertise and let someone who knows what they're talking about answer those questions. So, Subjects that sound interesting, challenging, or within my areas of expertise, I'll open and look at. If the author didn't feel like wasting his time writing a descriptive subject line, I don't feel obligated to waste my time trying to figure out what he needs help with. I receive HUNDREDS of emails every day... I get to choose which ones to open... just my opinion... Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: dguillett1 dguille...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, October 27, 2011 9:56:09 AM Subject: Re: $$Excel-Macros$$ Urgent help needed Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. === Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = Master Sheet For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, h) = Sheets(i).Name .Cells(dlr, H).Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com From: Anil Bhange Sent: Thursday, October 27, 2011 5:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub Anil Bhange Assistant Manager Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India 'Desk : + 91 22 6659 2320 | IP Phone : 51 2320 |Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.com --
$$Excel-Macros$$ Internet Explorer
Hi Experts, Please help me with this code Sub FillInternetForm() Dim objIE As Object Dim theForm As HTMLFormElement Set objIE = CreateObject(InternetExplorer.Application) objIE.Navigate http://www.oanda.com/currency/converter/; objIE.Visible = True Do While objIE.busy While objIE.busy DoEvents Wend Loop objIE.Document.getElementById(quote_currency_input).Value = Gambian Dalasi objIE.Document.getElementById(flipper).Click End Sub Thanks Airen -- -- 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$$ Internet Explorer
Dear Airen, Please add Microsoft HTML Liberary then run your code. Press Alt+F11 Tools-References-Check Microsoft HTML Liberary then press F5 Coding is correct On Thu, Oct 27, 2011 at 8:14 PM, airen airen1...@gmail.com wrote: Hi Experts, Please help me with this code Sub FillInternetForm() Dim objIE As Object Dim theForm As HTMLFormElement Set objIE = CreateObject(InternetExplorer.Application) objIE.Navigate http://www.oanda.com/currency/converter/; objIE.Visible = True Do While objIE.busy While objIE.busy DoEvents Wend Loop objIE.Document.getElementById(quote_currency_input).Value = Gambian Dalasi objIE.Document.getElementById(flipper).Click End Sub Thanks Airen -- -- 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.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/discussexcel
Re: $$Excel-Macros$$ Urgent help needed
Seconding Don and Paul on that. Another point that comes to the table is the lack of proper Subject Titles that clearly and concisely describes the problem/objective. I know that this is hinted in the forum rules, but do we not need to moderate this? Regards, Sam On Thu, Oct 27, 2011 at 7:39 PM, Paul Schreiner schreiner_p...@att.netwrote: I agree with Don. The first thing I do is delete all requests that do not describe the problem in the subject line. Posting Tips: Subjects like Urgent Help Needed convey no valueable information. the problem isn't urgent for ME, and if the poster didn't need help, he/she wouldn't be posting. Next, I consider myself an expert in many facets of Excel and VBA, but I have very little experience in Pivot Tables. Therefore, I skip questions outside of my areas of expertise and let someone who knows what they're talking about answer those questions. So, Subjects that sound interesting, challenging, or within my areas of expertise, I'll open and look at. If the author didn't feel like wasting his time writing a descriptive subject line, I don't feel obligated to waste my time trying to figure out what he needs help with. I receive HUNDREDS of emails every day... I get to choose which ones to open... just my opinion... *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* dguillett1 dguille...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, October 27, 2011 9:56:09 AM *Subject:* Re: $$Excel-Macros$$ Urgent help needed Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. === Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = Master Sheet For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, h) = Sheets(i).Name .Cells(dlr, H).Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Anil Bhange anil.bha...@tatacommunications.com *Sent:* Thursday, October 27, 2011 5:24 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536,
Re: $$Excel-Macros$$ Internet Explorer
Use this instead. You don't have to add the library in this case. Sub FillInternetForm() Dim objIE As Object Dim theForm As Object Set objIE = CreateObject(InternetExplorer.Application) objIE.Navigate http://www.oanda.com/currency/converter/; objIE.Visible = True Do While objIE.busy: Loop objIE.Document.getElementById(quote_currency_input).Value = Gambian Dalasi objIE.Document.getElementById(flipper).Click Do While objIE.busy: Loop End Sub Regards, Sam Mathai Chacko (GL) On Thu, Oct 27, 2011 at 8:28 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Airen, Please add Microsoft HTML Liberary then run your code. Press Alt+F11 Tools-References-Check Microsoft HTML Liberary then press F5 Coding is correct On Thu, Oct 27, 2011 at 8:14 PM, airen airen1...@gmail.com wrote: Hi Experts, Please help me with this code Sub FillInternetForm() Dim objIE As Object Dim theForm As HTMLFormElement Set objIE = CreateObject(InternetExplorer.Application) objIE.Navigate http://www.oanda.com/currency/converter/; objIE.Visible = True Do While objIE.busy While objIE.busy DoEvents Wend Loop objIE.Document.getElementById(quote_currency_input).Value = Gambian Dalasi objIE.Document.getElementById(flipper).Click End Sub Thanks Airen -- -- 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.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/discussexcel -- Sam Mathai Chacko -- -- 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$$ Auto Reference
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deepak Rawat Sent: Friday, January 21, 2011 7:02 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Auto Reference My problem is, I want max amount in the last column but whenever i insert any column before the Max column the Max range should change automatically. I want a macro for the maximum value calculation at the last column. The sample file is attached. Regards, Deepak Rawat -- -- 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$$ Auto Reference
You could use named ranges, but what I've done in the past is to search the column headings for the heading for the Max column and set that column number in a variable to use in the formula. The issue you have there is that someone could choose to rename the column. But you have nearly the same issue with using a Named Range for the column Heading. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: D M Sukumar vijayajith...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, October 27, 2011 11:15:53 AM Subject: RE: $$Excel-Macros$$ Auto Reference From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deepak Rawat Sent: Friday, January 21, 2011 7:02 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Auto Reference My problem is, I want max amount in the last column but whenever i insert any column before the Max column the Max range should change automatically. I want a macro for the maximum value calculation at the last column. The sample file is attached. Regards, Deepak Rawat -- -- 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 -- -- 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$$ calculate multiple value in one cell
Bravo Noorain Rgds, Sam On Thu, Oct 27, 2011 at 1:23 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Anjul, Please see attached sheet, hope it will help to u... Step 1 Select Cell B1 2 Formula-Define Name-Refers to-type-=Evaluate($A2) 3 Give Name Range 4 Use Name Range in B1 Cell to Output -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Oct 27, 2011 at 1:10 PM, Anjul Porwal porwalan...@gmail.comwrote: Hi, I have multiple value in one cell for example. *A1* 1*7+5 12*57+54 12+854 14+47*1 I want result below type by formula VBA. *B1* 12 738 866 61 Regards, Anjul -- -- 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 -- Sam Mathai Chacko -- -- 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$$ calculate multiple value in one cell
And here's the VBA version Function EVALUATER(rngCell As Range) EVALUATER = Application.EVALUATE(= rngCell.Value) End Function Regards, Sam Mathai Chacko (GL) On Thu, Oct 27, 2011 at 9:49 PM, Sam Mathai Chacko samde...@gmail.comwrote: Bravo Noorain Rgds, Sam On Thu, Oct 27, 2011 at 1:23 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Anjul, Please see attached sheet, hope it will help to u... Step 1 Select Cell B1 2 Formula-Define Name-Refers to-type-=Evaluate($A2) 3 Give Name Range 4 Use Name Range in B1 Cell to Output -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Oct 27, 2011 at 1:10 PM, Anjul Porwal porwalan...@gmail.comwrote: Hi, I have multiple value in one cell for example. *A1* 1*7+5 12*57+54 12+854 14+47*1 I want result below type by formula VBA. *B1* 12 738 866 61 Regards, Anjul -- -- 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 -- Sam Mathai Chacko -- Sam Mathai Chacko -- -- 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$$ calculate multiple value in one cell
Thanks SAM. On Thu, Oct 27, 2011 at 9:49 PM, Sam Mathai Chacko samde...@gmail.comwrote: Bravo Noorain Rgds, Sam On Thu, Oct 27, 2011 at 1:23 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Anjul, Please see attached sheet, hope it will help to u... Step 1 Select Cell B1 2 Formula-Define Name-Refers to-type-=Evaluate($A2) 3 Give Name Range 4 Use Name Range in B1 Cell to Output -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Oct 27, 2011 at 1:10 PM, Anjul Porwal porwalan...@gmail.comwrote: Hi, I have multiple value in one cell for example. *A1* 1*7+5 12*57+54 12+854 14+47*1 I want result below type by formula VBA. *B1* 12 738 866 61 Regards, Anjul -- -- 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 -- Sam Mathai Chacko -- -- 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.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/discussexcel
RE: $$Excel-Macros$$ Urgent help needed
Hi Noorain, Thanks for the help, but it is not working at my end due to different sheet name (I believe), can we make it flexible (as it need to run in any worksheet). Also i need my output as like below.. I am ok to repeat the heading of data And one more thing can macro ask me the range which need to select (single time) and sheets to be consolidate (as selection only once) Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Thursday, October 27, 2011 05:37 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Urgent help needed Dear Anil, Please try through below code and see attached sheet. Sub Consodilated_sheet_withsheetname() Dim i, j, k, s As Long Dim x, y, z As Long Application.ScreenUpdating = False x = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column s = Sheets.Count Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Master Sheet For i = 1 To s Sheets(i).Select j = 2 k = ActiveSheet.UsedRange.Rows.Count Rows(j : k).Copy Sheets(Sheets.Count).Select Sheets(Sheets.Count).Range(A65356).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(0, x) = Sheets(i).Name ActiveCell.Offset(0, x).EntireRow.Interior.Color = vbCyan Next i Application.ScreenUpdating = True End Sub On Thu, Oct 27, 2011 at 3:54 PM, Anil Bhange anil.bha...@tatacommunications.commailto:anil.bha...@tatacommunications.com wrote: Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub Anil Bhange Assistant Manager Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai - 400 001, India ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.commailto:anil.bha...@tatacommunications.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.comhttp://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.comhttp://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.comhttp://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari
RE: $$Excel-Macros$$ Urgent help needed... (apologies)
Thanks guys... For suggesting the same... will take care next time Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Sam Mathai Chacko Sent: Thursday, October 27, 2011 08:45 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Urgent help needed Seconding Don and Paul on that. Another point that comes to the table is the lack of proper Subject Titles that clearly and concisely describes the problem/objective. I know that this is hinted in the forum rules, but do we not need to moderate this? Regards, Sam On Thu, Oct 27, 2011 at 7:39 PM, Paul Schreiner schreiner_p...@att.netmailto:schreiner_p...@att.net wrote: I agree with Don. The first thing I do is delete all requests that do not describe the problem in the subject line. Posting Tips: Subjects like Urgent Help Needed convey no valueable information. the problem isn't urgent for ME, and if the poster didn't need help, he/she wouldn't be posting. Next, I consider myself an expert in many facets of Excel and VBA, but I have very little experience in Pivot Tables. Therefore, I skip questions outside of my areas of expertise and let someone who knows what they're talking about answer those questions. So, Subjects that sound interesting, challenging, or within my areas of expertise, I'll open and look at. If the author didn't feel like wasting his time writing a descriptive subject line, I don't feel obligated to waste my time trying to figure out what he needs help with. I receive HUNDREDS of emails every day... I get to choose which ones to open... just my opinion... Paul - Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can. - John Wesley - From: dguillett1 dguille...@gmail.commailto:dguille...@gmail.com To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Sent: Thu, October 27, 2011 9:56:09 AM Subject: Re: $$Excel-Macros$$ Urgent help needed Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. === Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = Master Sheet For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, h) = Sheets(i).Name .Cells(dlr, H).Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.commailto:dguille...@gmail.com From: Anil Bhangemailto:anil.bha...@tatacommunications.com Sent: Thursday, October 27, 2011 5:24 AM To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index =
Re: $$Excel-Macros$$ Download Word Macro Tutorial
2011/10/24 NOORAIN ANSARI noorain.ans...@gmail.com Dear Pankaj, PFA On Mon, Oct 24, 2011 at 6:01 AM, pankaj gmail account pankaji...@gmail.com wrote: This link is also not working. Pankaj On Sun, Oct 23, 2011 at 8:01 PM, ashish koul koul.ash...@gmail.comwrote: http://www.susandoreydesigns.com/software/WordVBATechniques.pdf -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* P Before printing, think about the environment. -- -- 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 -- Pankaj 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/discussexcel -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.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/discussexcel Hi Ashish koul and NOORAIN ANSARI I received a Word document and Access Macro Tutorial sent toyou by two. Thank 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Urgent help needed
Thanks Sam , Paul and Don, I totally agree with you guys but it is not possible to moderate each message. As an immediate action, I have modified the message footer to add forum rules. Now every message will carry below text at bottom. I hope this helps. Let me know if it is helpful or any other action required. Thanks again, Ayush Jain - FORUM RULES (925+ 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 On Thu, Oct 27, 2011 at 8:44 PM, Sam Mathai Chacko samde...@gmail.comwrote: Seconding Don and Paul on that. Another point that comes to the table is the lack of proper Subject Titles that clearly and concisely describes the problem/objective. I know that this is hinted in the forum rules, but do we not need to moderate this? Regards, Sam On Thu, Oct 27, 2011 at 7:39 PM, Paul Schreiner schreiner_p...@att.netwrote: I agree with Don. The first thing I do is delete all requests that do not describe the problem in the subject line. Posting Tips: Subjects like Urgent Help Needed convey no valueable information. the problem isn't urgent for ME, and if the poster didn't need help, he/she wouldn't be posting. Next, I consider myself an expert in many facets of Excel and VBA, but I have very little experience in Pivot Tables. Therefore, I skip questions outside of my areas of expertise and let someone who knows what they're talking about answer those questions. So, Subjects that sound interesting, challenging, or within my areas of expertise, I'll open and look at. If the author didn't feel like wasting his time writing a descriptive subject line, I don't feel obligated to waste my time trying to figure out what he needs help with. I receive HUNDREDS of emails every day... I get to choose which ones to open... just my opinion... *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* dguillett1 dguille...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thu, October 27, 2011 9:56:09 AM *Subject:* Re: $$Excel-Macros$$ Urgent help needed Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. === Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = Master Sheet For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, h) = Sheets(i).Name .Cells(dlr, H).Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub Don Guillett SalesAid Software dguille...@gmail.com *From:* Anil Bhange anil.bha...@tatacommunications.com *Sent:* Thursday, October 27, 2011 5:24 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets
$$Excel-Macros$$ Lookup
Dear All, Below mentioned Macro provided by SAM, but I am facing trouble please see the attached file provide the further enhancement in this macro. Your cooperate will highly appreciate. -- *Function MultiResultLookedUp(varLookupValue, rngRange As Range) As String Dim lngLoop As Long Dim varArray varArray = rngRange For lngLoop = LBound(varArray, 1) To UBound(varArray, 1) If varArray(lngLoop, 1) = varLookupValue Then MultiResultLookedUp = MultiResultLookedUp varArray(lngLoop, 2) / End If Next lngLoop MultiResultLookedUp = Left(MultiResultLookedUp, Len(MultiResultLookedUp) - 1) End Function* -- Regards, Aamir Shahzad -- FORUM RULES (925+ 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 Query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Urgent help needed
Did you try my solution?? Don Guillett SalesAid Software dguille...@gmail.com From: Anil Bhange Sent: Thursday, October 27, 2011 11:49 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Urgent help needed Hi Noorain, Thanks for the help, but it is not working at my end due to different sheet name (I believe), can we make it flexible (as it need to run in any worksheet). Also i need my output as like below.. I am ok to repeat the heading of data And one more thing can macro ask me the range which need to select (single time) and sheets to be consolidate (as selection only once) Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Thursday, October 27, 2011 05:37 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Urgent help needed Dear Anil, Please try through below code and see attached sheet. Sub Consodilated_sheet_withsheetname() Dim i, j, k, s As Long Dim x, y, z As Long Application.ScreenUpdating = False x = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column s = Sheets.Count Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Master Sheet For i = 1 To s Sheets(i).Select j = 2 k = ActiveSheet.UsedRange.Rows.Count Rows(j : k).Copy Sheets(Sheets.Count).Select Sheets(Sheets.Count).Range(A65356).End(xlUp).Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(0, x) = Sheets(i).Name ActiveCell.Offset(0, x).EntireRow.Interior.Color = vbCyan Next i Application.ScreenUpdating = True End Sub On Thu, Oct 27, 2011 at 3:54 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = Master Then MsgBox There is a worksheet called as 'Master'. vbCrLf _ Please remove or rename this worksheet since 'Master' would be _ the name of the result worksheet of this process., vbOKOnly + vbExclamation, Error Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = Master 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copypaste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub Anil Bhange Assistant Manager Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.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
Re: $$Excel-Macros$$ Lookup
Public Function MultiResultLookedUp(varLookupValue, rngRange As Range) As String Dim lngLoop As Long Dim varArray varArray = rngRange For lngLoop = LBound(varArray, 1) To UBound(varArray, 1) If varArray(lngLoop, 1) = varLookupValue Then If InStr(1, / MultiResultLookedUp /, / varArray(lngLoop, 2) /) = 0 Then MultiResultLookedUp = MultiResultLookedUp varArray(lngLoop, 2) / End If End If Next lngLoop MultiResultLookedUp = Left(MultiResultLookedUp, Len(MultiResultLookedUp) - 1) End Function Regards, Sam Mathai Chacko (GL) On Thu, Oct 27, 2011 at 11:22 PM, Aamir Shahzad aamirshahza...@gmail.comwrote: Dear All, Below mentioned Macro provided by SAM, but I am facing trouble please see the attached file provide the further enhancement in this macro. Your cooperate will highly appreciate. -- *Function MultiResultLookedUp(varLookupValue, rngRange As Range) As String Dim lngLoop As Long Dim varArray varArray = rngRange For lngLoop = LBound(varArray, 1) To UBound(varArray, 1) If varArray(lngLoop, 1) = varLookupValue Then MultiResultLookedUp = MultiResultLookedUp varArray(lngLoop, 2) / End If Next lngLoop MultiResultLookedUp = Left(MultiResultLookedUp, Len(MultiResultLookedUp) - 1) End Function* -- Regards, Aamir Shahzad -- FORUM RULES (925+ 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 -- Sam Mathai Chacko -- FORUM RULES (925+ 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
$$Excel-Macros$$ Find last number in a column with numbers
I have two columns of numbers. Column A is sequential dates. Column B is data gathered from time to time and entered in the appropriate date row as related to Column A. Column B has lots of blank rows without data. Reason: I do not gather data each day. All the rows below the last entry in Column B are blank. I would like to find and plot the data on a graph and have the graph grow with the data without changing the Source on the graph. I can do it by changing the Data Source each time; I can do it with Macros; and can do it with data in each cell of Column B down to the last entry. How to do it with random blanks in the data of column B? -- FORUM RULES (925+ 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$$ Auto Reference
Put this in a REGULAR module and then use =don(1) Function don(r As Long) Application.Volatile lc = Cells(r, Columns.Count).End(xlToLeft).Column don = Cells(Rows.Count, lc).End(xlUp) End Function Don Guillett SalesAid Software dguille...@gmail.com From: Paul Schreiner Sent: Thursday, October 27, 2011 10:33 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Auto Reference You could use named ranges, but what I've done in the past is to search the column headings for the heading for the Max column and set that column number in a variable to use in the formula. The issue you have there is that someone could choose to rename the column. But you have nearly the same issue with using a Named Range for the column Heading. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: D M Sukumar vijayajith...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, October 27, 2011 11:15:53 AM Subject: RE: $$Excel-Macros$$ Auto Reference From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deepak Rawat Sent: Friday, January 21, 2011 7:02 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Auto Reference My problem is, I want max amount in the last column but whenever i insert any column before the Max column the Max range should change automatically. I want a macro for the maximum value calculation at the last column. The sample file is attached. Regards, Deepak Rawat -- -- 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 -- -- 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 -- FORUM RULES (925+ 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$$ Find last number in a column with numbers
Attach your file with a copy of this msg Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Ken Sent: Thursday, October 27, 2011 1:54 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Find last number in a column with numbers I have two columns of numbers. Column A is sequential dates. Column B is data gathered from time to time and entered in the appropriate date row as related to Column A. Column B has lots of blank rows without data. Reason: I do not gather data each day. All the rows below the last entry in Column B are blank. I would like to find and plot the data on a graph and have the graph grow with the data without changing the Source on the graph. I can do it by changing the Data Source each time; I can do it with Macros; and can do it with data in each cell of Column B down to the last entry. How to do it with random blanks in the data of column B? -- FORUM RULES (925+ 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 -- FORUM RULES (925+ 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
$$Excel-Macros$$ Want to learn Match and Offset - formulas
Hi All, Can anyone tell me the simplified / easiest way to learn Index, Match and Offset - formulas or arrays, with some example. Thanks in advance. -- Indrajit Snai talk2indra...@gmail.com Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (925+ 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
$$Excel-Macros$$ To send mail from excel.
Hi group, Hope somebody can help me out by modifying the macro of below attached sheet which Mr. Ashish had posted earlier to send mails from excel: My requirement is that the body column should be copied to the mail without changing its cell format like below, Hi Ashish, how r u And is it possible to send it with a signature (my signature is a combination of text and image). Thanks regards, Kurikkal. -- FORUM RULES (925+ 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
$$Excel-Macros$$ Re: To send mail from excel.
Attachment... On Thu, Oct 27, 2011 at 11:34 PM, kurikkal padinjarappalla padinjarappa...@gmail.com wrote: Hi group, Hope somebody can help me out by modifying the macro of below attached sheet which Mr. Ashish had posted earlier to send mails from excel: My requirement is that the body column should be copied to the mail without changing its cell format like below, Hi Ashish, how r u And is it possible to send it with a signature (my signature is a combination of text and image). Thanks regards, Kurikkal. -- FORUM RULES (925+ 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 email_macrowithoutlooksignature.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Find last number in a column with numbers
Here's how you create the named range =OFFSET(Sheet1!$B$1,1,,MAX((LEN(TRIM(Sheet1!$B$1:$B$200))0)*(ROW(Sheet1!$B$1:$B$200)))-1,1) Check the attachment Regards, Sam Mathai Chacko (GL) On Fri, Oct 28, 2011 at 12:46 AM, dguillett1 dguille...@gmail.com wrote: Attach your file with a copy of this msg Don Guillett SalesAid Software dguille...@gmail.com -Original Message- From: Ken Sent: Thursday, October 27, 2011 1:54 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Find last number in a column with numbers I have two columns of numbers. Column A is sequential dates. Column B is data gathered from time to time and entered in the appropriate date row as related to Column A. Column B has lots of blank rows without data. Reason: I do not gather data each day. All the rows below the last entry in Column B are blank. I would like to find and plot the data on a graph and have the graph grow with the data without changing the Source on the graph. I can do it by changing the Data Source each time; I can do it with Macros; and can do it with data in each cell of Column B down to the last entry. How to do it with random blanks in the data of column B? -- FORUM RULES (925+ 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 -- FORUM RULES (925+ 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 -- Sam Mathai Chacko -- FORUM RULES (925+ 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 Dynamic Chart Source With Rows Blank.xlsm Description: Binary data
$$Excel-Macros$$ access query to run into excel
hey is there a macro to run access query inside excel 2007. and excel data should refresh with any change in access query data. -- FORUM RULES (925+ 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$$ Time between
Dear Paul, thanks a lot for the expalnation. Really good. But using the code that you wrote the result is: hour=9 and not equal hour=09:06 I tried this way: Cells(I, 15).Value = hour=Hour(Cells(I, 6).Value) : Minute(Cells(I,6)) but results in: hour=9:6 inspite of hour=09:06 Could you help me again? On 27 out, 10:59, Paul Schreiner schreiner_p...@att.net wrote: First of all, you need to understand that to Excel, time is simply a fraction of a day. So, 9:00am is really 9/24 of a day, or .375 9:06:24 is 0.379 of a day... when you SEE the cell as a time: 9:06 AM, it is a display format. You're DISPLAYING the number as a time. you could enter ANY number: 40515.225997274 And the Integer portion is the number of days since 1/1/1900 40515 = December 3, 2010 and the decimal portion (.225997274) is the time: 5:25:26 AM So, the problem is that cells(i,6) is returning the actual VALUE of the cell, not what is displayed. if you want the number represented as an hour, you'll need to convert it using hour() as in: Cells(i, 15).Value = hour=hour(Cells(i,6 )) also, the line: Set r = Range(A1:J60) isn't required You could use: Sub xml() Dim I For I = 5 To 60 If Cells(I, 1) Then Cells(I, 15).Value = hour=Hour(Cells(I, 6).Value) End If Next I End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eduardo Cereja cerejaedua...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, October 27, 2011 8:05:22 AM Subject: $$Excel-Macros$$ Time between Hi, I´m using the code below. Cells(i,6) has hour data, 09:06 for example. I need to transfer this value to another cell , but between 09:06. The code below result in: hour=0,4048611 however i want hour=09:06 Coud anyone help me with this simple problem? Best regards Sub xml() Set r = Range(A1:J60) For i = 5 To 60 If r.Cells(i, 1) Then Cells(i, 15).Value = hour=Cells(i,6 ) End If Next i 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://www.facebook.com/discussexcel -- FORUM RULES (925+ 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
$$Excel-Macros$$ Re: Advanced Filter
Hello Akhiles, Use Advanced Filter. In Sheet2, insert 3 blank rows above the heading, then use this formula in A2 =ISNA(MATCH($A5,Sheet1!$A:$A,0)) Then select Sheet2 data, use Advanced Filter. In Advanced Filter, Select Copy to another location List Range: Select Sheet2 data. Criteria Range: Select A1:A2 Copy To: Select a cell where do you need the result. If you need unique list, tick Unique Records Only. Click OK See the attached HTH Haseeb -- FORUM RULES (925+ 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 Copy_of_Example.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Find last number in a column with numbers
Hello Ken; Also, =MATCH(9E300,A:A) will give the last row number contains number. so you can define name range with this as length See the attached. Same data as Sam posted. HTH Haseeb -- FORUM RULES (925+ 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 Copy of Dynamic+Chart+Source+With+Rows+Blank.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Find last number in a column with numbers
Hello Ken; Also, =MATCH(9E300,$B:$B) will give the last row number contains number. so you can define name range with this as length See the attached. Same data as Sam posted. HTH Haseeb -- FORUM RULES (925+ 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 Copy of Dynamic+Chart+Source+With+Rows+Blank.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Auto Reference
Also, Another one to avoid OFFSET volatile, In E2 copy down. =MAX(B2:INDEX(2:2,MATCH(z,$1:$1)-1)) HTH Haseeb -- FORUM RULES (925+ 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$$ access query to run into excel
Dear Sara, Please try through below link http://quickvba.blogspot.com/2008/04/query-access-from-excel.html http://www.excelvbamacros.com/p/access.html On Fri, Oct 28, 2011 at 4:46 AM, Sara Lee lee.sar...@gmail.com wrote: hey is there a macro to run access query inside excel 2007. and excel data should refresh with any change in access query data. -- FORUM RULES (925+ 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 -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ -- FORUM RULES (925+ 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$$ Time between
Cells(I, 15).Value = hour=Format(Hour(Cells(I, 6).Value),00) : Format(Minute(Cells(I,6)), 00) Regards Sam On Fri, Oct 28, 2011 at 6:20 AM, Eduardo Cereja cerejaedua...@gmail.comwrote: Dear Paul, thanks a lot for the expalnation. Really good. But using the code that you wrote the result is: hour=9 and not equal hour=09:06 I tried this way: Cells(I, 15).Value = hour=Hour(Cells(I, 6).Value) : Minute(Cells(I,6)) but results in: hour=9:6 inspite of hour=09:06 Could you help me again? On 27 out, 10:59, Paul Schreiner schreiner_p...@att.net wrote: First of all, you need to understand that to Excel, time is simply a fraction of a day. So, 9:00am is really 9/24 of a day, or .375 9:06:24 is 0.379 of a day... when you SEE the cell as a time: 9:06 AM, it is a display format. You're DISPLAYING the number as a time. you could enter ANY number: 40515.225997274 And the Integer portion is the number of days since 1/1/1900 40515 = December 3, 2010 and the decimal portion (.225997274) is the time: 5:25:26 AM So, the problem is that cells(i,6) is returning the actual VALUE of the cell, not what is displayed. if you want the number represented as an hour, you'll need to convert it using hour() as in: Cells(i, 15).Value = hour=hour(Cells(i,6 )) also, the line: Set r = Range(A1:J60) isn't required You could use: Sub xml() Dim I For I = 5 To 60 If Cells(I, 1) Then Cells(I, 15).Value = hour=Hour(Cells(I, 6).Value) End If Next I End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eduardo Cereja cerejaedua...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Thu, October 27, 2011 8:05:22 AM Subject: $$Excel-Macros$$ Time between Hi, I´m using the code below. Cells(i,6) has hour data, 09:06 for example. I need to transfer this value to another cell , but between 09:06. The code below result in: hour=0,4048611 however i want hour=09:06 Coud anyone help me with this simple problem? Best regards Sub xml() Set r = Range(A1:J60) For i = 5 To 60 If r.Cells(i, 1) Then Cells(i, 15).Value = hour=Cells(i,6 ) End If Next i 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp:// www.facebook.com/discussexcel -- FORUM RULES (925+ 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 -- Sam Mathai Chacko -- FORUM RULES (925+ 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
