RE: $$Excel-Macros$$ HI --help me
1) Duplicate Unique Key 2) Blank Cells 3) Wrong Data/time format 4) Wrong Data Type (Number to String) 5) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vijayajith VA Sent: Sunday, July 24, 2011 5:51 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ HI --help me Hi, I have one questions.Usually while doing reports what are errors you will get ? 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$$ Copy Variable data
The below code works and return on single name match. Basically copies the data from sheet one to sheet two using Name as a key. Now would like create loop to go through both sheets and compare the names, and if the name exist in sheet one then copy his value into sheet two. Please let me know the possibilities. Sheet one: “Cargo” Cargo Summary Request Total 1 Total 2 Lori Trump Susan doo Dii Kon 888 111 Moo Kevin Ajax James Alex Trapek Kevin O'neil Ming Kii Kung Kwan Dii Kii Nung No Li Morgan Total Sheet2:” ORDER” New Cargo Cargo Captain Sea NameTotal A Total B General ZW N/A Lori Trump 122 66 General DE N/A Moris Bee 40 56 General DD N/A Dii Kon 888 78 General DD N/A Moo Kevin 127 99 General DD N/A Ajax James 24 33 General DD N/A Alex Trapek 231 12 General MM N/A Kevin O'neil0 10 General Total Reeefer HU N/A Dii Kon 0 33 Reefer HU N/A Kevin O'neil515 55 Reefer Total 515 Fish cargo NI N/A Moris Bee 0 67 Fish cargo NI N/A Ajax James 0 83 Fish cargo Total Here the code: Option Explicit Option Compare Text Public Captain As String Public LastRow As Double Public Variable(15) As Variant Public VarTemp(15) As Variant Sub Cargo_Data() Dim iCol As Long Dim x As Long, x1 As Long, x2 As Long Dim NewBook As Workbook Dim bFind As Boolean Dim Name As String Dim iTmp As Long Worksheets(Cargo).Select 'clear the array variable For x1 = 1 To 15 Variable(x1) = 0 VarTemp(x1) = 0 Next x1 Captain = Name If CaptainThen ' GoTo Name End If ActiveWorkbook.Sheets(Order).Select 'Worksheets(Order).Select Range(A6).Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 6 To LastRow If Cells(x1, 4) = Dii Kon Then Call AddTo(Variable(2), Cells(x1, 5)) Call AddTo(Variable(3), Cells(x1, 6)) End If Next x1 '-- 'Imports to the data Worksheets(Cargo).Select Range(A7).Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 7 To LastRow If Cells(x1, 1) = Dii Kon Then For iCol = 2 To 15 Select Case iCol Case 2 To 3 Cells(x1, iCol) = Variable(iCol) End Select Next iCol Exit For End If Next x1 End Sub Sub AddTo(ByRef vValue As Variant, vNew As Variant) If IsNumeric(vNew) Then If IsNumeric(vValue) Then vValue = vValue + vNew ElseIf vValue = na Or vValue = Then vValue = vNew End If ElseIf vNew = na Then If vValue = 0 Then vValue = na End If End If End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ HI --help me
use formula =IF(iserror(ur formula),,ur formula)) On Sun, Jul 24, 2011 at 5:50 PM, vijayajith VA vijayajith...@gmail.comwrote: Hi, I have one questions.Usually while doing reports what are errors you will get ? 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 -- Regards, Santy -- -- 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$$ HI --help me
Dear Vijay, Please find Error remover technique. * Errors-World* Error Type Error Remover Functions *ISNA()* *ISREF()* *ISERR()* *ISERROR()* *IFERROR()* #N/A YES YES NO YES YES #ISREF NO YES YES YES YES #NULL NO YES YES YES YES #DIV/0! NO YES YES YES YES #NUM NO YES YES YES YES #NAME? NO YES YES YES YES #VALUES NO YES YES YES YES Other Errors # Press Alt+O+C+A 2.32335E+16 Press Ctrl+1,Custom Format-Press 0 Circular Reference Formula-Error checking-Select Circular Referenceand Decreas formula Range -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Sun, Jul 24, 2011 at 5:50 PM, vijayajith VA vijayajith...@gmail.comwrote: Hi, I have one questions.Usually while doing reports what are errors you will get ? 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
Re: $$Excel-Macros$$ HI --help me
Hi Get the PUP V3 utility on the J-Walk site. Do all errors on whole sheet in one click. Magic. Charlie On Mon, Jul 25, 2011 at 6:36 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Vijay, Please find Error remover technique. * Errors-World* Error Type Error Remover Functions*ISNA()* *ISREF()* *ISERR()* *ISERROR()* * IFERROR()* #N/A YES YES NO YES YES #ISREF NO YES YES YES YES #NULL NO YES YES YES YES #DIV/0! NO YES YES YES YES #NUM NO YES YES YES YES #NAME? NO YES YES YES YES #VALUES NO YES YES YES YES Other Errors # Press Alt+O+C+A 2.32335E+16 Press Ctrl+1,Custom Format-Press 0 Circular Reference Formula-Error checking-Select Circular Reference and Decreas formula Range -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sun, Jul 24, 2011 at 5:50 PM, vijayajith VA vijayajith...@gmail.comwrote: Hi, I have one questions.Usually while doing reports what are errors you will get ? 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 -- -- 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$$ vba for loops -beginner
Please Attach Your Code and Workbook -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of netuser501 Sent: Sunday, July 24, 2011 6:37 PM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ vba for loops -beginner I'm looking more for an explanation, why cells.(r+1,1) = is not working? I have to use rng.Rows.Count always to do the equivalent? Thanks On Jul 24, 7:38 am, Rajan_Verma rajanverma1...@gmail.com wrote: See if it Helps Option Base 1 Sub StoreInArrya() Dim Arr() As Variant Dim rng As Range Dim Rw As Integer Dim Cl As Integer Set rng = Range(A1:C5) ReDim Arr(rng.Cells.Rows.Count, rng.Cells.Columns.Count) For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count Arr(Rw, Cl) = rng.Cells(Rw, Cl).Value Next Next For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count st = st vbTab Arr(Rw, Cl) Next st = st vbCrLf Next MsgBox st End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of netuser501 Sent: Sunday, July 24, 2011 6:42 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ vba for loops -beginner Hi The question worksheets is filled with data on 5 rows and 3 columns. I'd like to have this data stored in an Array. For testing purpose, I want to test if the loop is reading the data with an integer tmp that should return the number of cells filled in with data. Set wkb = ThisWorkbook Set wks = wkb.Worksheets(questions) For i = 0 To wks.Cells(i + 1, 1 = For j = 0 To wks.Cells(i + 1, j + 1) = tmp = tmp + 1 Next j Next i Why aren't Loop j and i looping? Another question : Is this possible to redim an array in a 2d for loop : dim SomeArray() as variant redim SomeArray(0,0) 'A for i = 0 to MAXL for j = 0 to MAXC SomeArray(i,j) = cells(i+1,j+1) redim preserve SomeArray(i,j+1) next j redim preserve SomeArray(i+1,j) 'j has still MAXC as value next i I understand it's not making sense since MAXL and MAXC could be declared in A. Back to the previous question it would make sense to redim a 2d array in a for loop until a cell is empty in the column and a cell is empty in the lines. Thanks for your precious help, -- --- - -- 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 ArrayLoop.xlsm 16KViewDownload -- -- 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
FW: $$Excel-Macros$$ Max value from a set of group
See the attached File From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vickey Sent: Sunday, July 24, 2011 5:25 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Max value from a set of group How can I get maximum value from a set of goup i.e there are two to three families out of this from each family maximum age of family member is require in next colum corresponding to maximum age member. for example A xyz 45 A uyy 33 A yss 65 B sas 34 B ree 45 B ww 55 taking to abobe example maximum age from group a should releflect in next column as 65 and from goup b 55. kindly help me out to solve this issue. Thanks in advance. Vikas -- -- 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 Book11.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Max value from a set of group
Hello Vickey, Use DMAX formula. See the attached. HTH Haseeb -- -- 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 sample - DMAX.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Copy Variable data
See if it helps If You have Name Range(A:A) in Sheet1 then Use this Code Sub CopyIfNotExist() Dim rng1 As Range Dim rng2 As Range Dim cell1 As Range Dim cell2 As Range Dim Flag As Boolean Set rng1 = Sheets(Sheet1).Range(A2:A Sheets(Sheet1).UsedRange.Rows.Count) Set rng2 = Sheets(Sheet2).Range(A2:A Sheets(Sheet2).UsedRange.Rows.Count) For Each cell1 In rng1 Flag = False For Each cell2 In rng2 If cell2.Value = cell1.Value Then Flag = True Exit For End If Next If Flag = True Then Else cell1.EntireRow.Copy Sheets(Sheet2).Range(A Sheets(Sheet2).UsedRange.Rows.Count + 1) End If Next End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Dkin Sent: Monday, July 25, 2011 7:41 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Copy Variable data The below code works and return on single name match. Basically copies the data from sheet one to sheet two using Name as a key. Now would like create loop to go through both sheets and compare the names, and if the name exist in sheet one then copy his value into sheet two. Please let me know the possibilities. Sheet one: Cargo Cargo Summary Request Total 1 Total 2 Lori Trump Susan doo Dii Kon 888 111 Moo Kevin Ajax James Alex Trapek Kevin O'neil Ming Kii Kung Kwan Dii Kii Nung No Li Morgan Total Sheet2: ORDER New Cargo Cargo Captain Sea NameTotal A Total B General ZW N/A Lori Trump 122 66 General DE N/A Moris Bee 40 56 General DD N/A Dii Kon 888 78 General DD N/A Moo Kevin 127 99 General DD N/A Ajax James 24 33 General DD N/A Alex Trapek 231 12 General MM N/A Kevin O'neil0 10 General Total Reeefer HU N/A Dii Kon 0 33 Reefer HU N/A Kevin O'neil515 55 Reefer Total 515 Fish cargo NI N/A Moris Bee 0 67 Fish cargo NI N/A Ajax James 0 83 Fish cargo Total Here the code: Option Explicit Option Compare Text Public Captain As String Public LastRow As Double Public Variable(15) As Variant Public VarTemp(15) As Variant Sub Cargo_Data() Dim iCol As Long Dim x As Long, x1 As Long, x2 As Long Dim NewBook As Workbook Dim bFind As Boolean Dim Name As String Dim iTmp As Long Worksheets(Cargo).Select 'clear the array variable For x1 = 1 To 15 Variable(x1) = 0 VarTemp(x1) = 0 Next x1 Captain = Name If CaptainThen ' GoTo Name End If ActiveWorkbook.Sheets(Order).Select 'Worksheets(Order).Select Range(A6).Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 6 To LastRow If Cells(x1, 4) = Dii Kon Then Call AddTo(Variable(2), Cells(x1, 5)) Call AddTo(Variable(3), Cells(x1, 6)) End If Next x1 '--- --- 'Imports to the data Worksheets(Cargo).Select Range(A7).Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 7 To LastRow If Cells(x1, 1) = Dii Kon Then For iCol = 2 To 15 Select Case iCol Case 2 To 3 Cells(x1, iCol) = Variable(iCol) End Select Next iCol Exit For End If Next x1 End Sub Sub AddTo(ByRef vValue As Variant, vNew As Variant) If IsNumeric(vNew) Then If IsNumeric(vValue) Then vValue = vValue + vNew ElseIf vValue = na Or vValue = Then vValue = vNew End If ElseIf vNew = na Then If vValue = 0 Then vValue = na End If End If End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow
Re: $$Excel-Macros$$ Sending e mail based on data in column in excel with permission
Any help Pls... - Manish On Jul 20, 2:43 pm, Manish pansari.man...@gmail.com wrote: Thanks Ashish, Is it possible to attach the picture saved in computer?? Like, If I will mention the path of the image file in column E, and excel will send that image in the body of outlook mail. Also suggest me, I want to restrict the person, So they can not reply and forward my mail. As the option is available in MS Outlook, But is it possible to send mail thru excel with such permissions?? Regards, Manish On Jul 19, 9:35 pm, ashish koul koul.ash...@gmail.com wrote: The easiest way to send an image in the body of outlook mail is to add the image on any photo sharing website like photobucket.com ,etc and use the code in the attached workbook or open this link http://akoul.blogspot.com/2011/07/sending-birthday-images-messages-to... On Tue, Jul 19, 2011 at 1:38 PM, Manish pansari.man...@gmail.com wrote: Dear EE, Dear Ashish, Its really helpful for me to understand the VBA. My requirement is 85% similar but I also want to add some selective greeting picture in body text after massage and before signature and also want to sent the mail thru excel with Permission “Do not forward” and “Do not Reply”. Please suggest. Thanks, Manish -- Forwarded message -- From: ashish koul koul.ash...@gmail.com Date: Aug 21 2010, 3:27 pm Subject: $$Excel-Macros$$ sending e mail based on data in column in excel To: MS EXCEL AND VBA MACROS in reference select Microsoft outlook library Sub bdaymessages() Dim I, K As Long Dim olApp As Outlook.Application Dim olMail As MailItem Dim SigString As String Dim Signature As String Application.ScreenUpdating = False Set olApp = New Outlook.Application 'it is counting the non blank cells in col a K = Application.WorksheetFunction.CountA(Sheets(Sheet1).Range(a:a)) For I = 2 To K If Day(Now) = Day(CDate(Range(c I).Value)) And Month(Now) = Month(CDate(Range(c I).Value)) Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = HappyBirthdayDear Range(a I).Text .Body = Dear ActiveSheet.Range(a I).Text vbCrLf vbCrLf birthday message vbCrLf ActiveSheet.Range(e I).Text .Send End With Set olMail = Nothing End If If (Year(Now) - Year(CDate(Range(D I).Value))) Mod 5 = 0 Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = Congratulations on completion of Year(Now) - Year(CDate(Range(D I).Value)) years of service .Body = Dear ActiveSheet.Range(a I).Text vbCrLf vbCrLf Congrats message vbCrLf ActiveSheet.Range(e I).Text .Send End With Set olMail = Nothing End If Next I Set olApp = Nothing Application.ScreenUpdating = True End Sub Regards Ashish koulhttp://akoul.blogspot.com/ On Sat, Aug 21, 2010 at 2:09 PM, Chandra Gupt Kumar kumar.bemlmum...@gmail.com wrote: Hey I have copied and pasted in new module, but it is showing compile error. If you don’t mind, could u please attach the code in notepad. Regards, C.G.Kumar *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *ashish koul *Sent:* Saturday, August 21, 2010 12:59 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ sending e mail based on data in column in excel Sub bdaymessages() Dim I, K As Long Dim olApp As Outlook.Application Dim olMail As MailItem Dim SigString As String Dim Signature As String Application.ScreenUpdating = False Set olApp = New Outlook.Application 'it is counting the non blank cells in col a K = Application.WorksheetFunction. CountA(Sheets(Sheet1).Range(a:a)) For I = 2 To K If Day(Now) = Day(CDate(Range(c I).Value)) And Month(Now) = Month(CDate(Range(c I).Value)) Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = HappyBirthdayDear Range(a I).Text .Body = Dear ActiveSheet.Range(a I).Text vbCrLf vbCrLf birthday message vbCrLf ActiveSheet.Range(e I).Text .Display '.Send End With Set olMail = Nothing End If If (Year(Now) - Year(CDate(Range(D I).Value))) Mod 5 = 0 Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = Congratulations on completion of Year(Now) - Year(CDate(Range(D I).Value)) years of service .Body = Dear ActiveSheet.Range(a
Re: $$Excel-Macros$$ Call Center Dashboards
Check the below site. It contain the KPI dashboard which you where looking for. http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ Warm Regards Bhushan -- -- 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$$ MS Excel MCQ Quiz
please paste the ans. also. -- -- 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$$ HI --help me
Hello All, Thank You very much.. On Mon, Jul 25, 2011 at 12:20 PM, Cab Boose swch...@gmail.com wrote: Hi Get the PUP V3 utility on the J-Walk site. Do all errors on whole sheet in one click. Magic. Charlie On Mon, Jul 25, 2011 at 6:36 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Vijay, Please find Error remover technique. * Errors-World* Error Type Error Remover Functions*ISNA()* *ISREF()* *ISERR()* *ISERROR()* * IFERROR()* #N/A YES YES NO YES YES #ISREF NO YES YES YES YES #NULL NO YES YES YES YES #DIV/0! NO YES YES YES YES #NUM NO YES YES YES YES #NAME? NO YES YES YES YES #VALUES NO YES YES YES YES Other Errors # Press Alt+O+C+A 2.32335E+16 Press Ctrl+1,Custom Format-Press 0 Circular Reference Formula-Error checking-Select Circular Reference and Decreas formula Range -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sun, Jul 24, 2011 at 5:50 PM, vijayajith VA vijayajith...@gmail.comwrote: Hi, I have one questions.Usually while doing reports what are errors you will get ? 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 -- -- 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$$ Wrong Keywords in Excel - Spell Check
Hi, Is it possible to store those keywords which is being found using Spellcheck option in excel. I have a list of values in Column A on which I run the spellcheck. Now I want, excel(vba) to store those wrong values in sheet2 which is being found by spellcheck function so that I can populate their replacement (expanded version). For example there would be a value as Mktg in my list which will be found by spellcheck. Mktg has to be expanded as Marketing which I do manually and also enter this mktg in my repository manually for the future reference so that I can run my macro and replace mktg with Marketing. I need your help in how to store those wrong keywords in sheet2 with each occurrence. Hope I am able to explain my problem. Any help is appreciated. Thanks, Anish -- -- 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$$ Wrong Keywords in Excel - Spell Check
Try this , Hope I did understand your Query.. Public MyTest As Boolean Public myWord As String Sub GetText() On Error Resume Next Dim ws As Worksheet Dim st As String Dim arr() As String Dim cell As Range Set ws = ActiveSheet Sheets(WrongWord).Delete Sheets.Add.Name = WrongWord ws.Activate st = For Each cell In ActiveSheet.UsedRange st = st cell.Value Next arr = Split(st, ) For i = LBound(arr) To UBound(arr) myWord = arr(i) Call mySpell If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord MyTest = False Next Sheets(WrongWord).Activate MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation End Sub Sub mySpell() MyTest = Application.CheckSpelling(myWord) End Sub From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Anish Shrivastava Sent: Monday, July 25, 2011 4:24 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Wrong Keywords in Excel - Spell Check Hi, Is it possible to store those keywords which is being found using Spellcheck option in excel. I have a list of values in Column A on which I run the spellcheck. Now I want, excel(vba) to store those wrong values in sheet2 which is being found by spellcheck function so that I can populate their replacement (expanded version). For example there would be a value as Mktg in my list which will be found by spellcheck. Mktg has to be expanded as Marketing which I do manually and also enter this mktg in my repository manually for the future reference so that I can run my macro and replace mktg with Marketing. I need your help in how to store those wrong keywords in sheet2 with each occurrence. Hope I am able to explain my problem. Any help is appreciated. Thanks, Anish -- -- 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$$ MS Excel MCQ Quiz
Hi I need answers also pls thanks On Sat, Jul 23, 2011 at 9:49 AM, XLS S xlst...@gmail.com wrote: MS Excel MCQ Quiz 1. You can use the formula pallette to A) format cells containing numbers B) create and edit formula containing functions C) enter assumptions data D) copy a range of cells 2. When a range is selected, how can you activate the previous cell? A) Press the Alt key B) Press Tab C) Press Enter D) None of above 3. Which tool you will use to join some cells and place the content at the middle of joined cell? A) From Format Cells dialog box click on Merge Cells check box B) From Format Cells dialog box select the Centered alignment C) From Format Cells dialog box choose Merge and Center check box D) Click on Merge and Center tool on formatting toolbar 4. Tab scroll buttons are place on Excel screen A) towards the bottom right corner B) towards the bottom left corner C) towards the top right corner D) towards the top left corner 5. The Name box on to the left of formula bar A) shows the name of workbook currently working on B) shows the name of worksheet currently working on C) shows the name of cell or range currently working on D) None of above 6. Each excel file is a workbook that contains different sheets. Which of the following can not be a sheet in workbook? A) work sheet B) chart sheet C) module sheet D) data sheet 7. Which of the following is not the correct method of editing the cell content? A) Press the Alt key B) Press the F2 key C) Click the formula bar D) Double click the cell 8. You can merge the main document with data source in Excel. In mail merge operation, Word is usually A) server B) source C) client D) none 9. How can you update the values of formula cells if Auto Calculate mode of Excel is disabled? A) F8 B) F9 C) F10 D) F11 10. You want to set such that when you type Baishakh and drag the fill handle, Excel should produce Jestha, Aashadh and so on. What will you set to effect that? A) Custom List B) Auto Fill Options C) Fill Across Worksheet D) Fill Series 11. Where can you change automatic or manual calculation mode in Excel? A) Double CAL indicator on status bar B) Go to Tools Options Calculation and mark the corresponding radio button C) Both of above D) None of above 12. How can you show or hide the gridlines in Excel Worksheet? A) Go to Tools Options View tab and mark or remove the check box named Gridline B) Click Gridline tool on Forms toolbar C) Both of above D) None of above 13. Which of the following Excel screen components can NOT be turned on or off? A) Formula Bar B) Status Bar C) Tool Bar D) None of above 14. What happens when you press Ctrl + X after selecting some cells in Excel? A) The cell content of selected cells disappear from cell and stored in clipboard B) The cells selected are marked for cutting C) The selected cells are deleted and the cells are shifted left D) The selected cells are deleted and cells are shifted up 15. Which of the following option is not available in Paste Special dialog box? A) Add B) Subtract C) Divide D) SQRT 16. Which command will you choose to convert a column of data into row? A) Cut and Paste B) Edit Paste Special Transpose C) Both of above D) None of above 17. It is acceptable to let long text flow into adjacent cells on a worksheet when A) data will be entered in the adjecent cells B) no data will be entered in the adjacent cells C) there is no suitable abbrevition for the text D) there is not time to format the text 18. Which of the cell pointer indicates you that you can make selection? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 19. Which of the cell pointer indicates that you can fill series? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 20. Which of the cell pointer indicate that you can move the content to other cell? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above Regds ??? . -- -- 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
Re: $$Excel-Macros$$ MS Excel MCQ Quiz
hi I need answers also pls Thanks On Sat, Jul 23, 2011 at 9:49 AM, XLS S xlst...@gmail.com wrote: MS Excel MCQ Quiz 1. You can use the formula pallette to A) format cells containing numbers B) create and edit formula containing functions C) enter assumptions data D) copy a range of cells 2. When a range is selected, how can you activate the previous cell? A) Press the Alt key B) Press Tab C) Press Enter D) None of above 3. Which tool you will use to join some cells and place the content at the middle of joined cell? A) From Format Cells dialog box click on Merge Cells check box B) From Format Cells dialog box select the Centered alignment C) From Format Cells dialog box choose Merge and Center check box D) Click on Merge and Center tool on formatting toolbar 4. Tab scroll buttons are place on Excel screen A) towards the bottom right corner B) towards the bottom left corner C) towards the top right corner D) towards the top left corner 5. The Name box on to the left of formula bar A) shows the name of workbook currently working on B) shows the name of worksheet currently working on C) shows the name of cell or range currently working on D) None of above 6. Each excel file is a workbook that contains different sheets. Which of the following can not be a sheet in workbook? A) work sheet B) chart sheet C) module sheet D) data sheet 7. Which of the following is not the correct method of editing the cell content? A) Press the Alt key B) Press the F2 key C) Click the formula bar D) Double click the cell 8. You can merge the main document with data source in Excel. In mail merge operation, Word is usually A) server B) source C) client D) none 9. How can you update the values of formula cells if Auto Calculate mode of Excel is disabled? A) F8 B) F9 C) F10 D) F11 10. You want to set such that when you type Baishakh and drag the fill handle, Excel should produce Jestha, Aashadh and so on. What will you set to effect that? A) Custom List B) Auto Fill Options C) Fill Across Worksheet D) Fill Series 11. Where can you change automatic or manual calculation mode in Excel? A) Double CAL indicator on status bar B) Go to Tools Options Calculation and mark the corresponding radio button C) Both of above D) None of above 12. How can you show or hide the gridlines in Excel Worksheet? A) Go to Tools Options View tab and mark or remove the check box named Gridline B) Click Gridline tool on Forms toolbar C) Both of above D) None of above 13. Which of the following Excel screen components can NOT be turned on or off? A) Formula Bar B) Status Bar C) Tool Bar D) None of above 14. What happens when you press Ctrl + X after selecting some cells in Excel? A) The cell content of selected cells disappear from cell and stored in clipboard B) The cells selected are marked for cutting C) The selected cells are deleted and the cells are shifted left D) The selected cells are deleted and cells are shifted up 15. Which of the following option is not available in Paste Special dialog box? A) Add B) Subtract C) Divide D) SQRT 16. Which command will you choose to convert a column of data into row? A) Cut and Paste B) Edit Paste Special Transpose C) Both of above D) None of above 17. It is acceptable to let long text flow into adjacent cells on a worksheet when A) data will be entered in the adjecent cells B) no data will be entered in the adjacent cells C) there is no suitable abbrevition for the text D) there is not time to format the text 18. Which of the cell pointer indicates you that you can make selection? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 19. Which of the cell pointer indicates that you can fill series? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 20. Which of the cell pointer indicate that you can move the content to other cell? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above Regds ??? . -- -- 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
Re: $$Excel-Macros$$ vba for loops -beginner
Hi rajan Can you explain below code st = st vbTab Arr(Rw, Cl) Next st = st vbCrLf Next St means ? On Sun, Jul 24, 2011 at 1:08 PM, Rajan_Verma rajanverma1...@gmail.comwrote: See if it Helps Option Base 1 Sub StoreInArrya() Dim Arr() As Variant Dim rng As Range Dim Rw As Integer Dim Cl As Integer Set rng = Range(A1:C5) ReDim Arr(rng.Cells.Rows.Count, rng.Cells.Columns.Count) For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count Arr(Rw, Cl) = rng.Cells(Rw, Cl).Value Next Next For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count st = st vbTab Arr(Rw, Cl) Next st = st vbCrLf Next MsgBox st End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of netuser501 Sent: Sunday, July 24, 2011 6:42 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ vba for loops -beginner Hi The question worksheets is filled with data on 5 rows and 3 columns. I'd like to have this data stored in an Array. For testing purpose, I want to test if the loop is reading the data with an integer tmp that should return the number of cells filled in with data. Set wkb = ThisWorkbook Set wks = wkb.Worksheets(questions) For i = 0 To wks.Cells(i + 1, 1 = For j = 0 To wks.Cells(i + 1, j + 1) = tmp = tmp + 1 Next j Next i Why aren't Loop j and i looping? Another question : Is this possible to redim an array in a 2d for loop : dim SomeArray() as variant redim SomeArray(0,0) 'A for i = 0 to MAXL for j = 0 to MAXC SomeArray(i,j) = cells(i+1,j+1) redim preserve SomeArray(i,j+1) next j redim preserve SomeArray(i+1,j) 'j has still MAXC as value next i I understand it's not making sense since MAXL and MAXC could be declared in A. Back to the previous question it would make sense to redim a 2d array in a for loop until a cell is empty in the column and a cell is empty in the lines. Thanks for your precious help, -- -- 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
$$Excel-Macros$$ Count characters in cell while typing
Hi I can't figure out how to make a macro that - while the user is typing in a cell - automatically updates the statusbar with the current length of the cell. I'm not interested in the event Worksheet_Change because it only fires when the user press enter. 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$$ Count characters in cell while typing
I don't think any event available in excel to track this .. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Ib Christian Bank Sent: Monday, July 25, 2011 5:54 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Count characters in cell while typing Hi I can't figure out how to make a macro that - while the user is typing in a cell - automatically updates the statusbar with the current length of the cell. I'm not interested in the event Worksheet_Change because it only fires when the user press enter. 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
RE: $$Excel-Macros$$ vba for loops -beginner
Here st is string type variable which will Store the value of Arr(Rw,Cl) In the Cl Loop string will Updated with Next Element of Array and a tab Character and Again updated with New Line (Vbcrlf) Character In Rw Loop Finally a msgbox will show Full value of St. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vijayajith VA Sent: Monday, July 25, 2011 5:51 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ vba for loops -beginner Hi rajan Can you explain below code st = st vbTab Arr(Rw, Cl) Next st = st vbCrLf Next St means ? On Sun, Jul 24, 2011 at 1:08 PM, Rajan_Verma rajanverma1...@gmail.com wrote: See if it Helps Option Base 1 Sub StoreInArrya() Dim Arr() As Variant Dim rng As Range Dim Rw As Integer Dim Cl As Integer Set rng = Range(A1:C5) ReDim Arr(rng.Cells.Rows.Count, rng.Cells.Columns.Count) For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count Arr(Rw, Cl) = rng.Cells(Rw, Cl).Value Next Next For Rw = 1 To rng.Rows.Count For Cl = 1 To rng.Columns.Count st = st vbTab Arr(Rw, Cl) Next st = st vbCrLf Next MsgBox st End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of netuser501 Sent: Sunday, July 24, 2011 6:42 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ vba for loops -beginner Hi The question worksheets is filled with data on 5 rows and 3 columns. I'd like to have this data stored in an Array. For testing purpose, I want to test if the loop is reading the data with an integer tmp that should return the number of cells filled in with data. Set wkb = ThisWorkbook Set wks = wkb.Worksheets(questions) For i = 0 To wks.Cells(i + 1, 1 = For j = 0 To wks.Cells(i + 1, j + 1) = tmp = tmp + 1 Next j Next i Why aren't Loop j and i looping? Another question : Is this possible to redim an array in a 2d for loop : dim SomeArray() as variant redim SomeArray(0,0) 'A for i = 0 to MAXL for j = 0 to MAXC SomeArray(i,j) = cells(i+1,j+1) redim preserve SomeArray(i,j+1) next j redim preserve SomeArray(i+1,j) 'j has still MAXC as value next i I understand it's not making sense since MAXL and MAXC could be declared in A. Back to the previous question it would make sense to redim a 2d array in a for loop until a cell is empty in the column and a cell is empty in the lines. Thanks for your precious help, -- -- 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 -- -- 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
$$Excel-Macros$$ filter of nonblank cell
Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- -- 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$$ Wrong Keywords in Excel - Spell Check
Hi Rajan, Thanks for your quick response. It works fine. However I made few changes according to my further requirements and then It doesnt catch the wrong words.. It's giving me zero.. Please have a look. I have highlighted the changes I made. Let me know Where am I wrong? -- Public MyTest As Boolean Public myWord As String Sub GetText() On Error Resume Next Dim ws As Worksheet Dim st As String Dim arr() As String Dim cell As Range Dim lastrow As Integer Set ws = ActiveSheet Sheets(WrongWord).Delete Sheets.Add.Name = WrongWord ws.Activate lastrow = ws.Range(A1).End(xlDown).Row st = Range(B2, Cells(lastrow, B)).Select For Each cell In Selection st = st cell.Value Next arr = Split(st, ) For i = LBound(arr) To UBound(arr) myWord = arr(i) Call mySpell If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord MyTest = False Next Sheets(WrongWord).Activate MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation End Sub Sub mySpell() MyTest = Application.CheckSpelling(myWord) End Sub On Mon, Jul 25, 2011 at 5:05 PM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this , Hope I did understand your Query..* * * *Public MyTest As Boolean* *Public myWord As String* *Sub GetText()* *On Error Resume Next* *Dim ws As Worksheet* *Dim st As String* *Dim arr() As String* *Dim cell As Range* ** *Set ws = ActiveSheet* *Sheets(WrongWord).Delete* *Sheets.Add.Name http://sheets.add.name/ = WrongWord* *ws.Activate* *st = * ** ** *For Each cell In ActiveSheet.UsedRange* *st = st cell.Value* *Next* ** *arr = Split(st, )* *For i = LBound(arr) To UBound(arr)* *myWord = arr(i)* *Call mySpell* *If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord* *MyTest = False* *Next* *Sheets(WrongWord).Activate* *MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation* *End Sub* *Sub mySpell()* *MyTest = Application.CheckSpelling(myWord)* * * *End Sub* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Anish Shrivastava *Sent:* Monday, July 25, 2011 4:24 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Wrong Keywords in Excel - Spell Check ** ** Hi, Is it possible to store those keywords which is being found using Spellcheck option in excel. I have a list of values in Column A on which I run the spellcheck. Now I want, excel(vba) to store those wrong values in sheet2 which is being found by spellcheck function so that I can populate their replacement (expanded version). For example there would be a value as Mktg in my list which will be found by spellcheck. Mktg has to be expanded as Marketing which I do manually and also enter this mktg in my repository manually for the future reference so that I can run my macro and replace mktg with Marketing.* *** I need your help in how to store those wrong keywords in sheet2 with each occurrence. Hope I am able to explain my problem. Any help is appreciated. Thanks, Anish -- -- 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
Re: $$Excel-Macros$$ Wrong Keywords in Excel - Spell Check
Strange!! but I got it working with the same code below. Thanks a lot for this. Anish On Mon, Jul 25, 2011 at 7:46 PM, Anish Shrivastava anish@gmail.comwrote: Hi Rajan, Thanks for your quick response. It works fine. However I made few changes according to my further requirements and then It doesnt catch the wrong words.. It's giving me zero.. Please have a look. I have highlighted the changes I made. Let me know Where am I wrong? -- Public MyTest As Boolean Public myWord As String Sub GetText() On Error Resume Next Dim ws As Worksheet Dim st As String Dim arr() As String Dim cell As Range Dim lastrow As Integer Set ws = ActiveSheet Sheets(WrongWord).Delete Sheets.Add.Name http://sheets.add.name/ = WrongWord ws.Activate lastrow = ws.Range(A1).End(xlDown).Row st = Range(B2, Cells(lastrow, B)).Select For Each cell In Selection st = st cell.Value Next arr = Split(st, ) For i = LBound(arr) To UBound(arr) myWord = arr(i) Call mySpell If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord MyTest = False Next Sheets(WrongWord).Activate MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation End Sub Sub mySpell() MyTest = Application.CheckSpelling(myWord) End Sub On Mon, Jul 25, 2011 at 5:05 PM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this , Hope I did understand your Query..* * * *Public MyTest As Boolean* *Public myWord As String* *Sub GetText()* *On Error Resume Next* *Dim ws As Worksheet* *Dim st As String* *Dim arr() As String* *Dim cell As Range* ** *Set ws = ActiveSheet* *Sheets(WrongWord).Delete* *Sheets.Add.Name http://sheets.add.name/ = WrongWord* *ws.Activate* *st = * ** ** *For Each cell In ActiveSheet.UsedRange* *st = st cell.Value* *Next* ** *arr = Split(st, )* *For i = LBound(arr) To UBound(arr)* *myWord = arr(i)* *Call mySpell* *If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord* *MyTest = False* *Next* *Sheets(WrongWord).Activate* *MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation* *End Sub* *Sub mySpell()* *MyTest = Application.CheckSpelling(myWord)* * * *End Sub* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Anish Shrivastava *Sent:* Monday, July 25, 2011 4:24 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Wrong Keywords in Excel - Spell Check ** ** Hi, Is it possible to store those keywords which is being found using Spellcheck option in excel. I have a list of values in Column A on which I run the spellcheck. Now I want, excel(vba) to store those wrong values in sheet2 which is being found by spellcheck function so that I can populate their replacement (expanded version). For example there would be a value as Mktg in my list which will be found by spellcheck. Mktg has to be expanded as Marketing which I do manually and also enter this mktg in my repository manually for the future reference so that I can run my macro and replace mktg with Marketing. I need your help in how to store those wrong keywords in sheet2 with each occurrence. Hope I am able to explain my problem. Any help is appreciated. Thanks, Anish -- -- 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
RE: $$Excel-Macros$$ filter of nonblank cell
Try This Sub CopyNonBlankRow() On Error Resume Next Dim cell As Range Dim rng As Range Set rng = ActiveSheet.Range(B2:B ActiveSheet.UsedRange.Rows.Count) Sheets(Data).Delete Sheets.Add.Name = Data For Each cell In rng If cell.Value Then cell.EntireRow.Copy Sheets(Data).Range(A Sheets(Data).Range(A65536).End(xlUp).Row + 1) Next End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vickey Sent: Monday, July 25, 2011 7:42 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ filter of nonblank cell Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- -- 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$$ filter of nonblank cell
thanks but nonblank data is not being copied in data sheet. On Jul 25, 7:29 pm, Rajan_Verma rajanverma1...@gmail.com wrote: Try This Sub CopyNonBlankRow() On Error Resume Next Dim cell As Range Dim rng As Range Set rng = ActiveSheet.Range(B2:B ActiveSheet.UsedRange.Rows.Count) Sheets(Data).Delete Sheets.Add.Name = Data For Each cell In rng If cell.Value Then cell.EntireRow.Copy Sheets(Data).Range(A Sheets(Data).Range(A65536).End(xlUp).Row + 1) Next End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vickey Sent: Monday, July 25, 2011 7:42 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ filter of nonblank cell Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- ---- -- 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- Hide quoted text - - Show quoted text - -- -- 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$$ MS Excel MCQ Quiz
Hey please see the answer :- 1-B, 2-D, 3-D, 4-B, 5-C, 6-D, 7-A, 8-C, 9-B, 10-A, 11-B, 12-C, 13-D, 14-B, 15-D, 16-B, 17-B, 18-A, 19-B, 20-C, On Sat, Jul 23, 2011 at 9:49 AM, XLS S xlst...@gmail.com wrote: MS Excel MCQ Quiz 1. You can use the formula pallette to A) format cells containing numbers B) create and edit formula containing functions C) enter assumptions data D) copy a range of cells 2. When a range is selected, how can you activate the previous cell? A) Press the Alt key B) Press Tab C) Press Enter D) None of above 3. Which tool you will use to join some cells and place the content at the middle of joined cell? A) From Format Cells dialog box click on Merge Cells check box B) From Format Cells dialog box select the Centered alignment C) From Format Cells dialog box choose Merge and Center check box D) Click on Merge and Center tool on formatting toolbar 4. Tab scroll buttons are place on Excel screen A) towards the bottom right corner B) towards the bottom left corner C) towards the top right corner D) towards the top left corner 5. The Name box on to the left of formula bar A) shows the name of workbook currently working on B) shows the name of worksheet currently working on C) shows the name of cell or range currently working on D) None of above 6. Each excel file is a workbook that contains different sheets. Which of the following can not be a sheet in workbook? A) work sheet B) chart sheet C) module sheet D) data sheet 7. Which of the following is not the correct method of editing the cell content? A) Press the Alt key B) Press the F2 key C) Click the formula bar D) Double click the cell 8. You can merge the main document with data source in Excel. In mail merge operation, Word is usually A) server B) source C) client D) none 9. How can you update the values of formula cells if Auto Calculate mode of Excel is disabled? A) F8 B) F9 C) F10 D) F11 10. You want to set such that when you type Baishakh and drag the fill handle, Excel should produce Jestha, Aashadh and so on. What will you set to effect that? A) Custom List B) Auto Fill Options C) Fill Across Worksheet D) Fill Series 11. Where can you change automatic or manual calculation mode in Excel? A) Double CAL indicator on status bar B) Go to Tools Options Calculation and mark the corresponding radio button C) Both of above D) None of above 12. How can you show or hide the gridlines in Excel Worksheet? A) Go to Tools Options View tab and mark or remove the check box named Gridline B) Click Gridline tool on Forms toolbar C) Both of above D) None of above 13. Which of the following Excel screen components can NOT be turned on or off? A) Formula Bar B) Status Bar C) Tool Bar D) None of above 14. What happens when you press Ctrl + X after selecting some cells in Excel? A) The cell content of selected cells disappear from cell and stored in clipboard B) The cells selected are marked for cutting C) The selected cells are deleted and the cells are shifted left D) The selected cells are deleted and cells are shifted up 15. Which of the following option is not available in Paste Special dialog box? A) Add B) Subtract C) Divide D) SQRT 16. Which command will you choose to convert a column of data into row? A) Cut and Paste B) Edit Paste Special Transpose C) Both of above D) None of above 17. It is acceptable to let long text flow into adjacent cells on a worksheet when A) data will be entered in the adjecent cells B) no data will be entered in the adjacent cells C) there is no suitable abbrevition for the text D) there is not time to format the text 18. Which of the cell pointer indicates you that you can make selection? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 19. Which of the cell pointer indicates that you can fill series? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above 20. Which of the cell pointer indicate that you can move the content to other cell? A) Doctor’s symbol (Big Plus) B) small thin plus icon C) Mouse Pointer with anchor at the tip D) None of above Regds ??? . -- -- 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
$$Excel-Macros$$ Hi - Details needed for the MIS Executive Qualification
Hi Group, My Name is krishna, Working as a Data Analyst from the last 3 years. My daily work includes working with excel, Creating General Reports, Pdf to Excel Conversion, Data Mining and Analyzing data by using Data Tools like, Filter, Formulas, Sorting etc.. But I got a mail from one of my friend, It is an Opening for MIS Executive. I just want to know the exact qualification needed for the job. I mean, what are the Tools i need to know, I know, Many of us are already well settled. So, I just want to know from you people, Thanking you and Waiting for your reply - Krrish -- -- 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$$ Help for combo box
Dear friends I require some help regarding Vlookup function conunction with Combo Box. I want vlookup in the combobox with respect to selected value in another combo box. Also want to put value in any cell against the value selected in combo box. Sample file is attached. -- Subhash Chand Yadav -- -- 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 Sample for Combo Box.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Vlookup against the value generated from formula
When I m trying to use to formula VLOOKUP against any formula generated value, there is error. Please suggest suitable method for the same, as I have to take many of the value based n these types of conditions. Sample file attached. -- Subhash Chand Yadav -- -- 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 sample.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ filter of nonblank cell
Try this Sub CopyNonBlankRow() On Error Resume Next Dim cell As Range Dim rng As Range Set rng = Sheets(sheet1).Range(B2:B Sheets(Sheet1).UsedRange.Rows.Count) Sheets(Data).Delete Sheets.Add.Name = Data For Each cell In rng.Cells If cell.Value Then cell.EntireRow.Copy Sheets(Data).Activate Range(A Sheets(Data).Range(A1).End(xlUp).Row + 1).Select ActiveSheet.Paste Application.CutCopyMode = False End If Next End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vickey Sent: Monday, July 25, 2011 8:13 PM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ filter of nonblank cell thanks but nonblank data is not being copied in data sheet. On Jul 25, 7:29 pm, Rajan_Verma rajanverma1...@gmail.com wrote: Try This Sub CopyNonBlankRow() On Error Resume Next Dim cell As Range Dim rng As Range Set rng = ActiveSheet.Range(B2:B ActiveSheet.UsedRange.Rows.Count) Sheets(Data).Delete Sheets.Add.Name = Data For Each cell In rng If cell.Value Then cell.EntireRow.Copy Sheets(Data).Range(A Sheets(Data).Range(A65536).End(xlUp).Row + 1) Next End Sub -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vickey Sent: Monday, July 25, 2011 7:42 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ filter of nonblank cell Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- --- - -- 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- Hide quoted text - - Show quoted text - -- -- 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$$ Vlookup against the value generated from formula
Use this =VLOOKUP(VALUE(C5),Sheet3!$A$1:$D$403,4,FALSE) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Subhash Yadav Sent: Monday, July 25, 2011 6:38 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Vlookup against the value generated from formula When I m trying to use to formula VLOOKUP against any formula generated value, there is error. Please suggest suitable method for the same, as I have to take many of the value based n these types of conditions. Sample file attached. -- Subhash Chand Yadav -- -- 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$$ Help Macro doesn´t work anymore, keeps giving same error
Doing that, it doesn´t return the error anymore, but the macro still doesn´t copy the information :s, is it any complication with excel 2010? 2011/7/22 Rajan_Verma rajanverma1...@gmail.com *Set Wb=ActiveWorkbook* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Jorge Marques *Sent:* Thursday, July 21, 2011 10:44 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Help Macro doesn´t work anymore, keeps giving same error ** ** Hi guys, i have this macro i use to copy a range from column D of a worksheet of a workbook to column G of another workbook and worksheet, but it keeps giving me the same error saying that it´s subscript out of range in the line in yellow. Public wb, wbmes As Workbook Sub filldatabase() Call AbrirFile Call left wbmes.Close End Sub Private Sub AbrirFile() Dim Filter, Caption As String Dim SelectedFile As Variant Set wb = ThisWorkbook Filter = Ficheiro XLS (*.xls),(*.xls) Caption = Escolha o ficheiro a importar... SelectedFile = Application.GetOpenFilename(Filter, , Caption) If SelectedFile = False Then Exit Sub Set wbmes = Workbooks.Open(SelectedFile, 1, 1) End Sub Private Sub left() Dim a As Double wbmes.Activate wbmes.Sheets(Total_Refrige).Select wbmes.Sheets(Total_Refrige).Range(Range(D2), Range(D2).End(xlDown)).Copy wb.Activate If wb.Sheets(Pivot).Range(G2).Value Then wb.Sheets(Pivot).Range(G1).End(xlDown).Offset(1, 0).Select Else wb.Sheets(Pivot).Range(G2).Select End If a = ActiveCell.Row wb.Sheets(Total Refrige).Paste Application.CutCopyMode = False i = a While wb.Sheets(Pivot).Range(G i).Value i = i + 1 Wend End Sub Do you have any idea how to solve this?1000 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 -- -- 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$$ use of indirect function
Hi All, Is indirect function is good function to make dashboard. Please explain about indirect function and provide me some example. how can i use indirect function while to make dashboard. 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
$$Excel-Macros$$ Please explain about loop
Hi All, Please explain loop with real example in sheets . much appreciated if you will provide attached sheet with example of loop . 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$$ Please explain about loop
Are you refering to loop while,or loop until? 2011/7/25 neil johnson neil.jh...@googlemail.com Hi All, Please explain loop with real example in sheets . much appreciated if you will provide attached sheet with example of loop . 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
Re: $$Excel-Macros$$ Please explain about loop
Using Do...Loop Statementshttp://office.microsoft.com/client/helppreview.aspx?AssetId=HV805564959990lcid=1033NS=EXCEL%2EDEVVersion=12pid=CH080556468CTT=4 Article you can use Do...Loop statements to run a block of statements an indefinite number of times. The statements are repeated either while a condition is True or unt... Using For Each...Next Statementshttp://office.microsoft.com/client/helppreview.aspx?AssetId=HV805564969990lcid=1033NS=EXCEL%2EDEVVersion=12pid=CH080556468CTT=4 Article For Each...Next statements repeat a block of statements for each object in a collection or each element in an array . Visual Basic automatically sets a variable... Using For...Next Statementshttp://office.microsoft.com/client/helppreview.aspx?AssetId=HV805564979990lcid=1033NS=EXCEL%2EDEVVersion=12pid=CH080556468CTT=4 Article You can use For...Next statements to repeat a block of statements a specific number of times. For loops use a counter variable whose value is increased or decre... http://www.excel-vba-easy.com/vba-programming-excel-vba-loop.html http://www.databison.com/index.php/vba-for-loop-for-next-and-for-each-in-next/ -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Mon, Jul 25, 2011 at 9:24 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, Please explain loop with real example in sheets . much appreciated if you will provide attached sheet with example of loop . 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
Re: $$Excel-Macros$$ use of indirect function
Thanks a lot Noor Much appreciated On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Neil, Please see attached sheet with Indirect function example -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Jul 25, 2011 at 9:04 PM, neil johnson neil.jh...@googlemail.com wrote: Hi All, Is indirect function is good function to make dashboard. Please explain about indirect function and provide me some example. how can i use indirect function while to make dashboard. 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 -- -- 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$$ Please explain about loop
Hi Jorge, all loop which used in excel sheet On Mon, Jul 25, 2011 at 9:55 PM, Jorge Marques leote.w...@gmail.com wrote: Are you refering to loop while,or loop until? Hi 2011/7/25 neil johnson neil.jh...@googlemail.com Hi All, Please explain loop with real example in sheets . much appreciated if you will provide attached sheet with example of loop . 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 -- -- 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$$ filter of nonblank cell
Hi find attach as per your requirement. try : Sub test() Sheet1.UsedRange.Copy Sheet2.Select Sheet2.Range(A1).PasteSpecial Paste:=xlPasteValues On Error Resume Next Columns(B).SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete On Error GoTo 0 End Sub On Mon, Jul 25, 2011 at 7:42 PM, vickey vikasda...@gmail.com wrote: Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- -- 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 Sample sheet_2507.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Data Validation - Create a drop down list containing only unique
Hello Venkat, Use a separate sheet to get the unique offices. You can hide this sheet, if you don't want to show. Also use dynamic range name, so it will update automatically when add/delete entries. Assume there are no blank cells in Data Col_A Offices. If there is blank change the formula in Unique!A4 to, =IF(ROWS(A$4:A4)=B$1,INDEX(Data,MATCH(1,IF(INDEX(Data,0,1),IF(ISNA(MATCH(INDEX(Data,0,1),A$3:A3,0)),1)),0),1),) Then copy down. See the attached. HTH Haseeb http://www.excelfox.com/forum/forum.php -- -- 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 Unique Data Validation.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ beginner question
I would like to know the best option between using an array (most often as type variant) or a range object? -- -- 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$$ Sending e mail based on data in column in excel with permission
please find the link http://www.rondebruin.nl/mail/folder3/row.htm http://www.teachexcel.com/excel-help/excel-how-to.php?i=178460#1 try On Mon, Jul 25, 2011 at 2:27 PM, Manish pansari.man...@gmail.com wrote: Any help Pls... - Manish On Jul 20, 2:43 pm, Manish pansari.man...@gmail.com wrote: Thanks Ashish, Is it possible to attach the picture saved in computer?? Like, If I will mention the path of the image file in column E, and excel will send that image in the body of outlook mail. Also suggest me, I want to restrict the person, So they can not reply and forward my mail. As the option is available in MS Outlook, But is it possible to send mail thru excel with such permissions?? Regards, Manish On Jul 19, 9:35 pm, ashish koul koul.ash...@gmail.com wrote: The easiest way to send an image in the body of outlook mail is to add the image on any photo sharing website like photobucket.com ,etc and use the code in the attached workbook or open this link http://akoul.blogspot.com/2011/07/sending-birthday-images-messages-to. .. On Tue, Jul 19, 2011 at 1:38 PM, Manish pansari.man...@gmail.com wrote: Dear EE, Dear Ashish, Its really helpful for me to understand the VBA. My requirement is 85% similar but I also want to add some selective greeting picture in body text after massage and before signature and also want to sent the mail thru excel with Permission “Do not forward” and “Do not Reply”. Please suggest. Thanks, Manish -- Forwarded message -- From: ashish koul koul.ash...@gmail.com Date: Aug 21 2010, 3:27 pm Subject: $$Excel-Macros$$ sending e mail based on data in column in excel To: MS EXCEL AND VBA MACROS in reference select Microsoft outlook library Sub bdaymessages() Dim I, K As Long Dim olApp As Outlook.Application Dim olMail As MailItem Dim SigString As String Dim Signature As String Application.ScreenUpdating = False Set olApp = New Outlook.Application 'it is counting the non blank cells in col a K = Application.WorksheetFunction.CountA(Sheets(Sheet1).Range(a:a)) For I = 2 To K If Day(Now) = Day(CDate(Range(c I).Value)) And Month(Now) = Month(CDate(Range(c I).Value)) Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = HappyBirthdayDear Range(a I).Text .Body = DearActiveSheet.Range(a I).Text vbCrLf vbCrLf birthday message vbCrLf ActiveSheet.Range(e I).Text .Send End With Set olMail = Nothing End If If (Year(Now) - Year(CDate(Range(D I).Value))) Mod 5 = 0 Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = Congratulations on completion of Year(Now) - Year(CDate(Range(D I).Value)) years of service .Body = DearActiveSheet.Range(a I).Text vbCrLf vbCrLf Congrats message vbCrLf ActiveSheet.Range(e I).Text .Send End With Set olMail = Nothing End If Next I Set olApp = Nothing Application.ScreenUpdating = True End Sub Regards Ashish koulhttp://akoul.blogspot.com/ On Sat, Aug 21, 2010 at 2:09 PM, Chandra Gupt Kumar kumar.bemlmum...@gmail.com wrote: Hey I have copied and pasted in new module, but it is showing compile error. If you don’t mind, could u please attach the code in notepad. Regards, C.G.Kumar *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *ashish koul *Sent:* Saturday, August 21, 2010 12:59 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ sending e mail based on data in column in excel Sub bdaymessages() Dim I, K As Long Dim olApp As Outlook.Application Dim olMail As MailItem Dim SigString As String Dim Signature As String Application.ScreenUpdating = False Set olApp = New Outlook.Application 'it is counting the non blank cells in col a K = Application.WorksheetFunction. CountA(Sheets(Sheet1).Range(a:a)) For I = 2 To K If Day(Now) = Day(CDate(Range(c I).Value)) And Month(Now) = Month(CDate(Range(c I).Value)) Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = ActiveSheet.Range(b I).Text .Subject = HappyBirthdayDear Range(a I).Text .Body = DearActiveSheet.Range(a I).Text vbCrLf vbCrLfbirthday message vbCrLf ActiveSheet.Range(e I).Text .Display '.Send End With Set
Re: $$Excel-Macros$$ Data Validation - Create a drop down list containing only unique
please find the link http://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/ On Sun, Jul 24, 2011 at 3:49 PM, Venkatesan c venkat1@gmail.com wrote: Dear All, I have attached sheet contains my query on Data Validation - Create a drop down list containing only unique -- *Best Regards,* *Venkat* * * * * -- -- 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$$ use of indirect function
Very Good Noorani thnx On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Neil, Please see attached sheet with Indirect function example -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Jul 25, 2011 at 9:04 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, Is indirect function is good function to make dashboard. Please explain about indirect function and provide me some example. how can i use indirect function while to make dashboard. 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 -- -- 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$$ use of indirect function
but please explain indirect (getphoto) On Mon, Jul 25, 2011 at 11:27 PM, XLS S xlst...@gmail.com wrote: Very Good Noorani thnx On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Neil, Please see attached sheet with Indirect function example -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Jul 25, 2011 at 9:04 PM, neil johnson neil.jh...@googlemail.comwrote: Hi All, Is indirect function is good function to make dashboard. Please explain about indirect function and provide me some example. how can i use indirect function while to make dashboard. 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 -- -- 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$$ filter of nonblank cell
use filter and select non blank then select data press alt+; then copy and paste On Mon, Jul 25, 2011 at 7:42 PM, vickey vikasda...@gmail.com wrote: Is thereany way to filter / copy nonblank values toanother sheet. considering followng example. ID NAME 1 2 BBB 3 CCC 4 5 6 7 DDD 8 FFF 9 EE ID NAME 1 2 BBB 3 CCC 7 DDD 8 FFF 9 EE thanks in advance vikas -- -- 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$$ Request **Must Read**
Dear All, Request you to please do not change the subject line, if you help some one then just click the reply button.. I know my English is very bad but try to understand thnx group member. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Macro needed for ms access database
can u attach the data base On Mon, Jul 25, 2011 at 10:40 AM, §»VIPER«§ viper@gmail.com wrote: Hi First of all I have to thank you. Unfortunately you did something for excel. But my query is about ms access. Please find the OP. Your help will be appreciated. -- *Great day,* *viper* On Fri, Jul 22, 2011 at 7:49 PM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try the Attached Sheet* * * *When it will open it will ask the UserName , I have Give Two User name in Codes 1) Adam 2) Rajan * *After enter the Username as CommandBar will add with Different Menus* * * *Regards* *Rajan.* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *§»VIPER«§ *Sent:* Friday, July 22, 2011 9:39 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Macro needed for ms access database ** ** ** ** Hi 1. I have created a database with some custom menus. In that I am having a menu called Manage and I require a code which should hide the particular menu for particular users. 2. I have created a form for startup and I don’t want allow the users to close the form using the Cnrl+W. It should be always open till the database is open. I have removed the form control property close box from the form but the users are still able to close the form using Cntrl+W button. Can anyone give me the solution? -- *Great day,* *viper* ** ** -- -- 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 -- -- 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$$ Date Formula
Dear All Members Here I am posting my Question : ( 1 ). Starting Date : 25/08/2012 : Format will be dd/mm/ ( 2 ). End Date : 05/05/2016 : Format will be dd/mm/ Now I want Answer in the Following manner : ( 1 ) . 31/08/2012... ( 2 ). 30/09/2012 ( 3 ). 31/10/2012 ( 4 ). 30/11/2012 Up to 05/05/2016 --- End Date This means.. I want the last date of each month from start Month to end Month and for this the days should be calculated...accordingly The Months which have 31 days should be counted 31 and months which have 30 days should be counted 30,if start month is incomplete the days should be calculated accordingly , For End Month the days should be counted up to the mentioned date. For Ex. : My Starting Dt.25/08 but August has 31 days So, I will count Difference Of 6 Days ( 31/08 - 25/08 ) My Ending Dt Will be 05/05/2016..So I will count only 5 days. I want Formula Only I Do Not Want VBA Code...And I am using Office Professional Edition 2003 Version... Thanks Nikhil Shah -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help with Refreshing Multiple Pivots on different sheets
Easy way if you are using excel 2007,2010 then just press ctrl+alt+F5 On Sat, Jul 23, 2011 at 10:36 PM, Ruchi B ruchi.ben...@gmail.com wrote: All, Have a excel workbook with around 15 tabs ..each of tab has multiple pivots referring to different sets of data. All the pivots refer to 4 data sets in all .What is the best way of refreshing these multiple Pivots in different tabs at one go? Regards, Ruchi -- -- 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$$ Count characters in cell while typing
use lan() formula On Mon, Jul 25, 2011 at 5:53 PM, Ib Christian Bank ib.christian.b...@gmail.com wrote: Hi I can't figure out how to make a macro that - while the user is typing in a cell - automatically updates the statusbar with the current length of the cell. I'm not interested in the event Worksheet_Change because it only fires when the user press enter. 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
Re: $$Excel-Macros$$ Max value from a set of group
please find the attachment On Sun, Jul 24, 2011 at 9:16 PM, vickey vikasda...@gmail.com wrote: Thank you so much sir, but I have one query, the maximum salary must reflect against relative salary, becuase there is further formula for other members to increase rest of the members their salary by 20% of maximum. could you please help me out further please find attched sample excel. Thanks again...! Vikas -- -- 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 Copy of sample-1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Fwd: $$Excel-Macros$$ use of indirect function
Dear XLS,
name range of below function is getphoto--Formula-define
name-then paste below formula and put name range
=INDIRECT(Sheet4!BMATCH('Indirect Example 3'!$A$4,Sheet4!$A:$A,0))
-- Forwarded message --
From: XLS S xlst...@gmail.com
Date: Mon, Jul 25, 2011 at 11:27 PM
Subject: Re: $$Excel-Macros$$ use of indirect function
To: excel-macros@googlegroups.com
but please explain indirect (getphoto)
On Mon, Jul 25, 2011 at 11:27 PM, XLS S xlst...@gmail.com wrote:
Very Good Noorani thnx
On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI noorain.ans...@gmail.com
wrote:
Dear Neil,
Please see attached sheet with Indirect function example
--
Thanks regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Mon, Jul 25, 2011 at 9:04 PM, neil johnson
neil.jh...@googlemail.com wrote:
Hi All,
Is indirect function is good function to make dashboard. Please explain
about indirect function and provide me some example. how can i use indirect
function while to make dashboard.
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
--
--
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://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$$ Help with Refreshing Multiple Pivots on different sheets
Sub allpivotsofworkbook() activeworkbook.Refreshall end sub On Mon, Jul 25, 2011 at 11:55 PM, XLS S xlst...@gmail.com wrote: Easy way if you are using excel 2007,2010 then just press ctrl+alt+F5 On Sat, Jul 23, 2011 at 10:36 PM, Ruchi B ruchi.ben...@gmail.com wrote: All, Have a excel workbook with around 15 tabs ..each of tab has multiple pivots referring to different sets of data. All the pivots refer to 4 data sets in all .What is the best way of refreshing these multiple Pivots in different tabs at one go? Regards, Ruchi -- -- 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 -- Thanks regards, Noorain Ansari *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
$$Excel-Macros$$ Restrict access to certain sheets
I have a workbook with 3 sheets 2 of the sheets are for general use but one of the sheets i would like people to access only by a password is this possible? i can protect the sheet but i would like only certain people to view the information? protecting stops people from amending/entering data. -- -- 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$$ use of indirect function
photo??
On Tue, Jul 26, 2011 at 8:10 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear XLS,
name range of below function is
getphoto--Formula-define name-then paste below formula
and put name range
=INDIRECT(Sheet4!BMATCH('Indirect Example 3'!$A$4,Sheet4!$A:$A,0))
-- Forwarded message --
From: XLS S xlst...@gmail.com
Date: Mon, Jul 25, 2011 at 11:27 PM
Subject: Re: $$Excel-Macros$$ use of indirect function
To: excel-macros@googlegroups.com
but please explain indirect (getphoto)
On Mon, Jul 25, 2011 at 11:27 PM, XLS S xlst...@gmail.com wrote:
Very Good Noorani thnx
On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI
noorain.ans...@gmail.com wrote:
Dear Neil,
Please see attached sheet with Indirect function example
--
Thanks regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Mon, Jul 25, 2011 at 9:04 PM, neil johnson
neil.jh...@googlemail.com wrote:
Hi All,
Is indirect function is good function to make dashboard. Please explain
about indirect function and provide me some example. how can i use indirect
function while to make dashboard.
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
--
--
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://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
--
--
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$$ Restrict access to certain sheets
Hey, Try this run this macro and put the password in vba code Sub Macro1() ActiveWorkbook.Unprotect Password:=xxx Sheets(*Sheet1*).Visible = False ActiveWorkbook.Protect Structure:=True, Password:=xxx End Sub On Tue, Jul 26, 2011 at 8:44 AM, mahamadou lawali malaw...@gmail.comwrote: I have a workbook with 3 sheets 2 of the sheets are for general use but one of the sheets i would like people to access only by a password is this possible? i can protect the sheet but i would like only certain people to view the information? protecting stops people from amending/entering data. -- -- 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$$ Two major milestones : 8000 members and 1000+ posts
Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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$$ Lesson 1: The Visual Basic Editor in Excel (VBE)
** *Lesson 1: The Visual Basic Editor in Excel (VBE) * *For users of Excel 1997 to 2006:* The first thing that you need to do is to make sure that the security level of Excel is set at either Low or Medium so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select Tools then Macro then Security and select Medium. *For users of Excel 2007 to 2010:* From the Developer ribbon click on the Macro Security button. Check the second level Disable all Macros with Notification and you are set. *Setting up the Visual Basic Editor in Excel (VBE)* The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient. Print this page, open Excel and open a new workbook (Book1). On your keyboard press the ALT key (left of the space bar) and hold, strike the F11key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press ALT/F11 and you are back into Excel. Use the ALT/F11 key to go from Excel to the VBA and back. When you first open the VBE you will see is a window somewhat like the image below. If there are any open windows within the VBE like in the image below click on the Xs to close them and see a gray rectangle filling up the bottom part of the screen like in the image above. *The Three Windows in the Visual Basic Editor* To be efficient when working with the VBE there should always be 3 windows showing like in the image below; the Project Window (1), the Code Window ( 2), and the Properties Window (3), arranged as in the image below. You can resize the windows by left-clicking where the red stars are, holding and moving sideways or up and down. We will study each of the three windows in lessons 2, 3 and 4 but first we will set them up in the VBE. In the exercise below we will setup the 3 windows of the VBE. *Exercise 1* (Create your first macro and use it) Remember that you will perform this task only once as each time you will open the VBE it will remain setup. *Step 1:* Close all the windows that are open in the VBE to end up with this: *Step 2:* Go to the menu bar View and click Project Explorer. The result will be somewhat like the image below: If the project window already appears as a column on the left side of the screen there is nothing else that you have to do for now. If the project window appears in the middle of the gray area like above, right-click in the white space in the middle of the project window and check Dockable. Then click on the top blue bar of the Project window, hold and drag it left until the cursor (white arrow) touches the middle of the left side of the screen. When you let go of the mouse button the end result should be like shown in the image below. Congratulations you have setup the first major window of the VBE. *Step 3: *Move your cursor on the line separating the project window and the gray rectangle. When it turns to two small parallel lines and arrows click, hold and move the lines sideways. Resize the two windows as you want them. *Step 4:* Go back to the menu bar View and click Properties Window. The Properties window will appear somewhat like in the image below. If the Properties window is already located below the Project window there is nothing left to do. If it shows like in the image above, right-click in the white space in the middle of the Properties window and check Dockable. Then click on the top blue bar of the Properties window and drag it left and down until the cursor (white arrow) touches the center of the bottom of the Project window. When you let go of the mouse button the end result should be as the image below. Congratulations you have setup the second major window of the VBE. *Step 5: *Move your cursor on the line separating the project window and the properties window. When it turns to two small parallel lines and arrows click, hold and move the lines vertically. Resize the two windows as you want them. *Step 6: *To add the code window to the setup, you just have to double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook) and its code window appears within the gray rectangle. You can maximize any Code window by clicking on its Maximize button . The final result looks like the image below. The words Option Explicit might not be present in your Code window. We will address this issue later in the lesson on variables (Lesson 19). You might also have a VBAProject named FUNCRES.XLA or FUNCRES.XLAM in the project window. Forget about this project for now. *Step 6: *Now go to Excel and close it. Re-open Excel, go to the VBE (ALT/F11) and you will see that the VBE setup persists. Congratulations, you are now ready to work in the Visual Basic Editor. We will discover more about each of these three windows in lessons 2 (*Project Window
Re: $$Excel-Macros$$ Two major milestones : 8000 members and 1000+ posts
it's great news... On Tue, Jul 26, 2011 at 9:22 AM, Ayush jainayus...@gmail.com wrote: Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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$$ Two major milestones : 8000 members and 1000+ posts
congratulation ayush On Tue, Jul 26, 2011 at 9:22 AM, Ayush jainayus...@gmail.com wrote: Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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 -- Regards, Santy -- -- 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$$ Help Required (Excel Function or VBA Code)
Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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$$ Help Required (Excel Function or VBA Code)
Hi, Use this formula =LEN(A1)-LEN(SUBSTITUTE(A1, ,)) where A1 contains 9 7 5 6 On Tue, Jul 26, 2011 at 9:36 AM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote: Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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 -- Regards Vasant skype Id: vasantjob http://facebook.com/vasantjob -- -- 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$$ Restrict access to certain sheets
Hi You can use this code on workbook open event. 'User Level 1 is admin usage 'User Level 0 is general usage if a user logs in using level 1 he can see sheet4 for other users sheet4 will be hidden Sub test() If userlevel = 1 Then ThisWorkbook.Worksheets(Sheet4).Visible = xlVeryHidden Else ThisWorkbook.Worksheets(Sheet4).Visible = True End If End Sub On Tue, Jul 26, 2011 at 9:16 AM, XLS S xlst...@gmail.com wrote: Hey, Try this run this macro and put the password in vba code Sub Macro1() ActiveWorkbook.Unprotect Password:=xxx Sheets(*Sheet1*).Visible = False ActiveWorkbook.Protect Structure:=True, Password:=xxx End Sub On Tue, Jul 26, 2011 at 8:44 AM, mahamadou lawali malaw...@gmail.comwrote: I have a workbook with 3 sheets 2 of the sheets are for general use but one of the sheets i would like people to access only by a password is this possible? i can protect the sheet but i would like only certain people to view the information? protecting stops people from amending/entering data. -- -- 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 -- Regards Vasant skype Id: vasantjob http://facebook.com/vasantjob -- -- 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$$ Help with find date.
I want the macro below to accept any date that I enter in its input box, e.g. 17/06/2011, and go and find it in the active worksheet. I got an error message saying, Object variable or With block variable not set. Can anyone help me? Thanks. Sub FindDate() Dim myDate As Date ' possibly incorrect myDate = Application.InputBox(What date are you looking for?, Type:=1) Cells.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub Tom -- -- 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$$ Lesson 1: The Visual Basic Editor in Excel (VBE)
awaiting more lessons On 26 July 2011 09:24, XLS S xlst...@gmail.com wrote: ** *Lesson 1: The Visual Basic Editor in Excel (VBE) * *For users of Excel 1997 to 2006:* The first thing that you need to do is to make sure that the security level of Excel is set at either Low or Medium so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select Tools then Macro then Security and select Medium. *For users of Excel 2007 to 2010:* From the Developer ribbon click on the Macro Security button. Check the second level Disable all Macros with Notification and you are set. *Setting up the Visual Basic Editor in Excel (VBE)* The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient. Print this page, open Excel and open a new workbook (Book1). On your keyboard press the ALT key (left of the space bar) and hold, strike the F11key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press ALT/F11 and you are back into Excel. Use the ALT/F11 key to go from Excel to the VBA and back. When you first open the VBE you will see is a window somewhat like the image below. If there are any open windows within the VBE like in the image below click on the Xs to close them and see a gray rectangle filling up the bottom part of the screen like in the image above. *The Three Windows in the Visual Basic Editor* To be efficient when working with the VBE there should always be 3 windows showing like in the image below; the Project Window (1), the Code Window ( 2), and the Properties Window (3), arranged as in the image below. You can resize the windows by left-clicking where the red stars are, holding and moving sideways or up and down. We will study each of the three windows in lessons 2, 3 and 4 but first we will set them up in the VBE. In the exercise below we will setup the 3 windows of the VBE. *Exercise 1* (Create your first macro and use it) Remember that you will perform this task only once as each time you will open the VBE it will remain setup. *Step 1:* Close all the windows that are open in the VBE to end up with this: *Step 2:* Go to the menu bar View and click Project Explorer. The result will be somewhat like the image below: If the project window already appears as a column on the left side of the screen there is nothing else that you have to do for now. If the project window appears in the middle of the gray area like above, right-click in the white space in the middle of the project window and check Dockable. Then click on the top blue bar of the Project window, hold and drag it left until the cursor (white arrow) touches the middle of the left side of the screen. When you let go of the mouse button the end result should be like shown in the image below. Congratulations you have setup the first major window of the VBE. *Step 3: *Move your cursor on the line separating the project window and the gray rectangle. When it turns to two small parallel lines and arrows click, hold and move the lines sideways. Resize the two windows as you want them. *Step 4:* Go back to the menu bar View and click Properties Window. The Properties window will appear somewhat like in the image below. If the Properties window is already located below the Project window there is nothing left to do. If it shows like in the image above, right-click in the white space in the middle of the Properties window and check Dockable. Then click on the top blue bar of the Properties window and drag it left and down until the cursor (white arrow) touches the center of the bottom of the Project window. When you let go of the mouse button the end result should be as the image below. Congratulations you have setup the second major window of the VBE. *Step 5: *Move your cursor on the line separating the project window and the properties window. When it turns to two small parallel lines and arrows click, hold and move the lines vertically. Resize the two windows as you want them. *Step 6: *To add the code window to the setup, you just have to double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook) and its code window appears within the gray rectangle. You can maximize any Code window by clicking on its Maximize button . The final result looks like the image below. The words Option Explicit might not be present in your Code window. We will address this issue later in the lesson on variables (Lesson 19). You might also have a VBAProject named FUNCRES.XLA or FUNCRES.XLAM in the project window. Forget about this project for now. *Step 6: *Now go to Excel and close it. Re-open Excel, go to the VBE (ALT/F11) and you will see that the VBE setup persists. Congratulations, you are now ready to work in the Visual
RE: $$Excel-Macros$$ Help Required (Excel Function or VBA Code)
Try this =LEN(B1)-LEN(SUBSTITUTE(B1, ,)) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of KAUSHIK SAVLA Sent: Tuesday, July 26, 2011 9:37 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help Required (Excel Function or VBA Code) Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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$$ Help with find date.
Try using Cdate(Mydate) in Find On Tue, Jul 26, 2011 at 9:44 AM, Tom tcli...@gmail.com wrote: I want the macro below to accept any date that I enter in its input box, e.g. 17/06/2011, and go and find it in the active worksheet. I got an error message saying, Object variable or With block variable not set. Can anyone help me? Thanks. Sub FindDate() Dim myDate As Date ' possibly incorrect myDate = Application.InputBox(What date are you looking for?, Type:=1) Cells.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub Tom -- -- 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 -- Regards Vasant skype Id: vasantjob http://facebook.com/vasantjob -- -- 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$$ use of indirect function
See the attached sheet
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Tuesday, July 26, 2011 8:10 AM
To: excel-macros@googlegroups.com
Subject: Fwd: $$Excel-Macros$$ use of indirect function
Dear XLS,
name range of below function is getphoto--Formula-define
name-then paste below formula and put name range
=INDIRECT(Sheet4!BMATCH('Indirect Example 3'!$A$4,Sheet4!$A:$A,0))
-- Forwarded message --
From: XLS S xlst...@gmail.com
Date: Mon, Jul 25, 2011 at 11:27 PM
Subject: Re: $$Excel-Macros$$ use of indirect function
To: excel-macros@googlegroups.com
but please explain indirect (getphoto)
On Mon, Jul 25, 2011 at 11:27 PM, XLS S xlst...@gmail.com wrote:
Very Good Noorani thnx
On Mon, Jul 25, 2011 at 11:14 PM, NOORAIN ANSARI noorain.ans...@gmail.com
wrote:
Dear Neil,
Please see attached sheet with Indirect function example
--
Thanks regards,
Noorain Ansari
http://noorain-ansari.blogspot.com/ http://noorain-ansari.blogspot.com/
On Mon, Jul 25, 2011 at 9:04 PM, neil johnson neil.jh...@googlemail.com
wrote:
Hi All,
Is indirect function is good function to make dashboard. Please explain
about indirect function and provide me some example. how can i use indirect
function while to make dashboard.
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
http://www.excel-macros.blogspot.com/
4. Learn VBA Macros at http://www.quickvba.blogspot.com
http://www.quickvba.blogspot.com/
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
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
http://www.excel-macros.blogspot.com/
4. Learn VBA Macros at http://www.quickvba.blogspot.com
http://www.quickvba.blogspot.com/
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
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
http://www.excel-macros.blogspot.com/
4. Learn VBA Macros at http://www.quickvba.blogspot.com
http://www.quickvba.blogspot.com/
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
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://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
--
--
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
Book1.xlsx
Description:
Re: $$Excel-Macros$$ Date Time picker on a userform
Hi, Sam could you share the access mdb file, so that new user like me can learn how to link mdb with excel forms. Thanks in advance Regards Paul On Mon, Jul 25, 2011 at 6:12 AM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote: U can use date function or today function of excel. On 7/24/11, alisha malhotra alisha.malhotr...@gmail.com wrote: Hi, I am using one userform, In that I need Date time Picker in excel 2003. But when I click on Additional controls Nothing Happens. I need to add some reference for this? How can I add the Date time Picker on a userform? I am attaching the file also. Pls help. Thanks Regards, Alisha -- -- 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 -- Sent from Gmail for mobile | mobile.google.com Kaushik Savla -- -- 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$$ Help Required (Excel Function or VBA Code)
Dear Kaushik, In Excel Try it *=LEN(SUBSTITUTE(B2, ,))* in VBA... Try it. *Function cell_length(s as range)* *cell_length=application.worksheetfunction.* *Function cell_length(s As Range) cell_length = Len(Application.WorksheetFunction.Substitute(s, , )) End Function* ** -- *Thanks regards,* *Noorain Ansari* *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ * * On Tue, Jul 26, 2011 at 10:21 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this* * * *=LEN(B1)-LEN(SUBSTITUTE(B1, ,))* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *KAUSHIK SAVLA *Sent:* Tuesday, July 26, 2011 9:37 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Help Required (Excel Function or VBA Code) ** ** Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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$$ Two major milestones : 8000 members and 1000+ posts
It's great news. I would like to congratulate all group members thanks to all MVPs other members for their contribution. A special thanks to Ayush for making continuous monitoring this group. Frankly speaking I am getting lot of help from this group. I learned excel a lot from this group. In a sentence this group is like OXYGEN for my career. Thank you all Kalyan Chattopadhyay On Tue, Jul 26, 2011 at 9:33 AM, Sant Ram santram...@gmail.com wrote: congratulation ayush On Tue, Jul 26, 2011 at 9:22 AM, Ayush jainayus...@gmail.com wrote: Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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 -- Regards, Santy -- -- 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
Fwd: $$Excel-Macros$$ Help Required (Excel Function or VBA Code)
Dear Kaushik, In Excel Try it *=LEN(SUBSTITUTE(B2, ,)) * in VBA... Try it. *Function cell_length(s As Range) cell_length = Len(Application.WorksheetFunction.Substitute(s, , )) End Function * -- Thanks regards, Noorain Ansari http://noorain-ansari.blogspot.com/ -- Forwarded message -- From: NOORAIN ANSARI noorain.ans...@gmail.com Date: Tue, Jul 26, 2011 at 11:07 AM Subject: Re: $$Excel-Macros$$ Help Required (Excel Function or VBA Code) To: excel-macros@googlegroups.com Dear Kaushik, In Excel Try it *=LEN(SUBSTITUTE(B2, ,))* in VBA... Try it. *Function cell_length(s as range)* *cell_length=application.worksheetfunction.* *Function cell_length(s As Range) cell_length = Len(Application.WorksheetFunction.Substitute(s, , )) End Function* ** -- *Thanks regards,* *Noorain Ansari* *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ * * On Tue, Jul 26, 2011 at 10:21 AM, Rajan_Verma rajanverma1...@gmail.comwrote: *Try this* * * *=LEN(B1)-LEN(SUBSTITUTE(B1, ,))* * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *KAUSHIK SAVLA *Sent:* Tuesday, July 26, 2011 9:37 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Help Required (Excel Function or VBA Code) ** ** Hi All, I want to count the number of spaces in a cell:- Eg In Cell A1 data is 9 7 5 6 I want function which returns answer as 4 count. Please suggest. -- -- 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 -- Thanks regards, Noorain Ansari *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$$ Two major milestones : 8000 members and 1000+ posts
Great Achievment, Congrats Ayush... On Tue, Jul 26, 2011 at 9:22 AM, Ayush jainayus...@gmail.com wrote: Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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://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$$ Help with find date.
If error is coming it means your date not found in that sheet.. ' On the Top on Module On Error Goto Err: 'Before End sub Statement Err: If err.number0 then Msgbox Date Not Found,vbinformation endif -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Tom Sent: Tuesday, July 26, 2011 9:45 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Help with find date. I want the macro below to accept any date that I enter in its input box, e.g. 17/06/2011, and go and find it in the active worksheet. I got an error message saying, Object variable or With block variable not set. Can anyone help me? Thanks. Sub FindDate() Dim myDate As Date ' possibly incorrect myDate = Application.InputBox(What date are you looking for?, Type:=1) Cells.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub Tom -- -- 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$$ Two major milestones : 8000 members and 1000+ posts
Dear Group members, It is a great news. I would like to congratulate the members who have shared their knowledge to others. Congratulation Ayush.. Regards Paul On Tue, Jul 26, 2011 at 9:22 AM, Ayush jainayus...@gmail.com wrote: Dear members, I am glad to share that this forum has achieved two major milestones this month. The number of members have reached 8000 and 1000 posts in a month which is highest in forum history. The credit goes to each forum member who is passionate about excel. My sincere thanks to active MVPs, query solvers and all excel enthusiasts. *Feel free to invite your friends, colleagues , boss, relatives, your ex, your present and everyone who is in love with excel :) :)* ** You can provide me the email ids of the people you want to invite in the forum. Send me the list at jainayus...@gmail.com Keep the spirit up Warm regards Ayush Jain Group Manager. -- -- 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$$ Re: Date Formula
Dear Nikhil, Please the attached solution file might help you. Regards, Bhushan Sabbani. -- -- 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.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Please explain about loop
See the attached File : Sub FLoop() Dim i As Integer Range(A1).Value = Example By For Loop Range(A2).Select For i = 1 To 20 ActiveCell.Value = i am In For Loop i time and My value is i ActiveCell.Offset(1, 0).Select Next End Sub Sub DoLoop() Dim i As Integer Range(A1).Value = Example By Do Loop Range(A2).Select i = 0 Do i = i + 1 ActiveCell.Value = i am In Do Loop i time and My value is i ActiveCell.Offset(1, 0).Select Loop Until i = 20 End Sub Sub WhileLoop() Dim i As Integer Range(A1).Value = Example By While Loop Range(A2).Select i = 0 While i 20 i = i + 1 ActiveCell.Value = i am In While Loop i time and My value is i ActiveCell.Offset(1, 0).Select Wend End Sub Sub LableLoop() Dim i As Integer Range(A1).Value = Example By lable and Goto Range(A2).Select i = 0 CC: i = i + 1 ActiveCell.Value = i am In Goto Loop i time and My value is i ActiveCell.Offset(1, 0).Select If i 20 Then GoTo CC: Else Exit Sub End If End Sub From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of neil johnson Sent: Monday, July 25, 2011 9:24 PM To: excel-macros Subject: $$Excel-Macros$$ Please explain about loop Hi All, Please explain loop with real example in sheets . much appreciated if you will provide attached sheet with example of loop . 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 Loop Example.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
