Re: $$Excel-Macros$$ Expert Advice Needed - Formula to count Worksheets
Create a Name in your workbook (ribbon Tab Formula Name Manager Name (for Excample) : SheetsCount Refer to: =GET.WORKBOOK(4) [image: Create a NAME.PNG] in the sheet you can use *=SheetsCount* resultling number of sheet in the workbook they called it : Macro Excel4 http://support.microsoft.com/kb/109976 On Tue, Nov 29, 2011 at 4:36 AM, Secret Shot secrets...@gmail.com wrote: Dear Experts, It there any Formula to count worksheets in a Excel File. I know VBA code for this but i want any Formula or any combination of formula's only to solve this. kindly Help. This is very critical for me. Thanks in Advance.. Pankaj Pandey Bhopal -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Create a NAME.PNG
Re: $$Excel-Macros$$ Setting a default cell format in excel 2007
It seems you need a new template standards. Open a new workbook Set the desired number of sheet in the workbook Select all sheets at once as a group Select all cells (Ctrl + A) Format the cells of this workbook in your own font-style, font-size, and so on, including the format number (= GENERAL) save the workbook with NAME: BOOK.XLTX (assumption: you you are the users of Excel 2007 / 2010) storage paths: If you are a Windows XP user the Templates folder is usually C:\Documents and Settings\\Application Data\Microsoft\Templates. In Windows Vista the Templates folder is usually C:\Users\\AppData\Roaming\Microsoft\Templates. Make the same workbook but also contains only one sheet and save as SHEET.XLTX book.xltx will be used by Excel when you open a new workbook. sheet.xltx will be used by Excel every time you insert a new sheet. ~siti On Thu, Nov 17, 2011 at 12:48 PM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear Masters, ** ** Can you please let me know, how to set a default format for all cells in excel 2007. As of now whenever I type any data it comes in date format, I want to change this to general. ** ** Regards, *Amit Desai* ** -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Simple Excel Calendar
Another *Array Formula* for Simple Calendar
(MultipleResult Array Formula on 7 columns X 6 Rows) based on Date in a
cell F22)
grid formula
=$F$22-WEEKDAY($F$22)+COLUMN(1:7)+ROW(1:7)*7-7
weekday name formula
=TEXT({1,2,3,4,5,6,7},ddd)
On Sun, Oct 30, 2011 at 8:40 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear Group,
Please find attached Simple Excel Calender.
=TRANSPOSE({1;2;3;4;5;6;7})
=IF(MONTH(F10)MONTH(F10-WEEKDAY(F10)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}),,F10-WEEKDAY(F10)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})
Press ctrl+shift+enter with both formula
--
Thanks regards,
Noorain Ansari
*
*
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
Simple Calander.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ How to learn vba
Dear Mr excel, You are welcome to visit your brother here http://www.mrexcel.com/articles.shtml#VBA best regards ~siti your fans from indonesia On Thu, Oct 20, 2011 at 8:21 AM, Mr excel excelkeec...@gmail.com wrote: Thanks all,for the replies.i had been working with the VBA macro recorder.Its really very funny intersting to learn like this.But there are some situation where i need to learn the programming techniques like Do Loops,While Loops etc which cannot be learnt from macro recording.Please suggest me some ideas or techniques for learning...Thanks in advance to all those who had helped me... -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
without macro solution Assuming 1,2,3,4,5 etc is stored in A1:A300 Put this formula in D1 *=OFFSET($A$1,ROW(A1)*3-(4-COLUMN(A1)),0)* copy right down 3 columns x 100 rows best regards, ~ siti On Fri, Aug 26, 2011 at 8:45 PM, GreenBriar impedimenta.st...@gmail.comwrote: Transpose would not work because every 4th record (or row) would need to go back to the first column, next row. original data could be in either an excel spreadsheet or a text file, whichever works best. thus *1 2 3 4 5 6 7 8 9 * would look like *1 2 3 4 5 6 7 8 9* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_wrapped every 3 columns.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Hi
http://chrisrae.com/vba/whatisvba.html http://www.excel-vba.com/ http://www.excel-vba-easy.com/ http://www.youtube.com/watch?v=t1vdt0FddsE On Fri, Aug 26, 2011 at 10:24 PM, Mohamed Youssouf youssouf.mohame...@gmail.com wrote: I'd like to learn how ti use MACRO -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ how to delete multiple row columns
A little correction Sub cleanrowsandcolumnsSAS() lr = Cells.Find(*, Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row *+ 1* lc = Cells.Find(*, Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column *+ 1* Application.EnableEvents = False Rows(lr).Resize(Rows.Count - lr).Delete Columns(lc).Resize(, Columns.Count - lr).Delete Application.EnableEvents = True ActiveWorkbook.Save End Sub '--thank you siti Vi On Tue, Aug 23, 2011 at 7:37 PM, dguillett1 dguille...@gmail.com wrote: If I understand your request, you may like this. It also saves the file Sub cleanrowsandcolumnsSAS() lr = Cells.Find(*, Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row lc = Cells.Find(*, Cells(Rows.Count, Columns.Count) _ , , , xlByColumns, xlPrevious).Column Application.EnableEvents = False Rows(lr).Resize(Rows.Count - lr).Delete Columns(lc).Resize(, Columns.Count - lr).Delete Application.EnableEvents = True ActiveWorkbook.Save End Sub *From:* Amit Desai (MERU) amit.de...@merucabs.com *Sent:* Tuesday, August 23, 2011 1:32 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ how to delete multiple row coulmns Dear All, Please let me know how to delete multiple rows(say from my last data based row # 678 to last row) multiple columns (say from AA to last column).*** * This is taking a lot of file size… Best Regards, Amit Desai -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 I need your help , Please explain it with example.
*1. **Worksheet.Change Event* * *Occurs when cells on the worksheet are changed by the user or by an external link. This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation. This example changes the color of changed cells to blue. Private Sub Worksheet_Change(ByVal Target as Range) Target.Font.ColorIndex = 5 End Sub *2 Worksheet.SelectionChange Event * Occurs when* the selection changes* on a worksheet. e.g You move cell pointer from A1 to A2 The parameter Target is Range(A2) This example scrolls through the workbook window until* the selection is in the upper-left corner of the window*. Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveWindow .ScrollRow = Target.Row .ScrollColumn = Target.Column End With End Sub CMIIW On Fri, Jul 15, 2011 at 6:45 PM, neil johnson neil.jh...@googlemail.com wrote: Hi Sir, Can you please tell me what is difference between these two codes, Please guide me. Private Sub Worksheet_Change(ByVal Target As Range) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) 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$$ Regression Analysis in Excel
visit .. http://phoenix.phys.clemson.edu/tutorials/excel/regression.html http://mallit.fr.umn.edu/fr4218/assigns/excel_reg.html http://www.chem.orst.edu/courses/ch361-464/ch464/RegrssnFnl.pdf http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/Use%20Excel%202007%20Regression.pdf http://cameron.econ.ucdavis.edu/excel/ex61multipleregression.html http://www.youtube.com/watch?v=lTNyalLBZo0 http://www.law.uchicago.edu/files/files/20.Sykes_.Regression.pdf http://en.wikipedia.org/wiki/Regression_analysis http://office.microsoft.com/en-us/excel-help/perform-a-regression-analysis-HA00963.aspx On Thu, Jul 14, 2011 at 2:49 PM, Venkatesan c venkat1@gmail.com wrote: Dear All, Can any one help me on Regression Analysis in Excel with sample data.. *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
Re: $$Excel-Macros$$ months and fraction of a month between two days
Function MonthFrac(StartDate As Date, EndDate As Date) As Single *'= siti Vi / Jakarta 12 July 2011* Dim YY1 As Integer, MM1 As Integer, DD1 As Integer Dim YY2 As Integer, MM2 As Integer, DD2 As Integer Dim MM As Integer, DD As Integer, EndMonthDays As Integer YY1 = Year(StartDate):YY2 = Year(EndDate) MM1 = Month(StartDate): MM2 = Month(EndDate) DD1 = Day(StartDate): DD2 = Day(EndDate) If DD2 DD1 Then DD2 = DD2 + Day(DateSerial(YY2, MM2, 0)) MM2 = MM2 - 1 End If If MM2 MM1 Then MM2 = MM2 + 12 YY2 = YY2 - 1 End If EndMonthDays = Day(DateSerial(YY2, MM2 + 1, 0)) MM = (YY2 - YY1) * 12 + (MM2 - MM1) DD = (DD2 - DD1) MonthFrac = MM + (DD / EndMonthDays) End Function On Tue, Jul 12, 2011 at 7:55 PM, Richard richard.m...@gmail.com wrote: Hi All, I would appreciate a vba function that will calculation the number of months and fraction of a month between 2 dates. i.e. January 1, 2011 to February 14, 2011 would be 1.5 months January 1, 2011 to February 15, 2011 would be 1.5335714 January 1, 2011 to March 15, 2011 would be 2.483871 Thank you in advance Rich -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 sitiVi_UDF_MonthFrac.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ months and fraction of a month between two days
how about: fraction to the LASTMONT ( days in EndDate Month) On Wed, Jul 13, 2011 at 9:28 AM, Dave Bonallack davebonall...@hotmail.comwrote: Hi, Fractions of a month are a bit subjective. Fractions of WHICH month, needs to be decided. Fractions of a 28-day month? 29- 30- or 31- day month? Or maybe a standardized 30.4375-day month? Regards - Dave On 12/07/2011, at 8:55 PM, Richard richard.m...@gmail.com wrote: Hi All, I would appreciate a vba function that will calculation the number of months and fraction of a month between 2 dates. i.e. January 1, 2011 to February 14, 2011 would be 1.5 months January 1, 2011 to February 15, 2011 would be 1.5335714 January 1, 2011 to March 15, 2011 would be 2.483871 Thank you in advance Rich -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Converting Text Case By VBA
Converting text case to ProperCase in VBA alt: Instead of using Application.Function / WorksheetFunction) we can also use VBA function: *StrConv *function StrConv( , vbProperCase) Example in sub procedur Sub ConvertProperCase() Application.ScreenUpdating = False Dim Cell As Range For Each Cell In Selection.Cells If Not Cell.HasFormula Then Cell.Value = *StrConv(Rng, vbProperCase)* End If Next Cell Application.ScreenUpdating = True End Sub Thank you On Thu, Jul 7, 2011 at 3:18 AM, Rajan_Verma rajanverma1...@gmail.comwrote: http://excelpoweruser.blogspot.com/2011/07/hiding-data-on-worksheet.html* *** ** ** -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Query
=*LOOKUP*
(D16,{3001,5001,6001,7001,8001,9001,15001,25001,40001},{30,40,45,50,60,110,130,150,200})
or..
=*VLOOKUP*(D16,$A$1:$B$9,2,TRUE)
the above VLookUp fungtion refers to a table
*tabel A1:B9*
* 3001 30 *
* 5001 40 *
* 6001 45 *
* 7001 50*
* 8001 60*
* 9001 110*
*15001 130*
*25001 150*
*40001 200 *
On Wed, Jul 6, 2011 at 6:26 PM, Kal xcel kalx...@gmail.com wrote:
Dear Experts,
For this chart
3001-5000 = 30
5001-6000 = 40
6001-7000 = 45
7001-8000 = 50
8001-9000 = 50
9001-15000 = 110
15001-25000 = 130
25001-4 = 150
40001= = 200
I use this formula:
=IF(AND(D16=3001,D16=5000),30,IF(AND(D16=5001,D16=6000),40,IF(AND(D16=6001,D16=7000),45,IF(AND(D16=7001,D16=8000),50,IF(AND(D16=8001,D16=9000),90,IF(AND(D16=9001,D16=15000),110,IF(AND(D16=15001,D16=25000),130,IF(AND(D16=25001,D16=4),150,IF(AND(D16=40001),200,0)
Is their any other short/intelligent way to get desired result?
Please help
Thanks in advance
Kalyan
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
LOOKUP and VLOOUP (1_2_3_TRUE).xls
Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Macro for find any possible combination of numbers from given numbers to a given total
you are talking about *Permutation* given number : 1 2 3 4 5 total arrangement of permutation *=FACT(5)* = *120* == 12345 21345 31245 41235 51234 12354 21354 31254 41253 51243 12435 21435 31425 41325 51324 12453 21453 31452 41352 51342 12534 21534 31524 41523 51423 12543 21543 31542 41532 51432 13245 23145 32145 42135 52134 13254 23154 32154 42153 52143 13425 23415 32415 42315 52314 13452 23451 32451 42351 52341 13524 23514 32514 42513 52413 13542 23541 32541 42531 52431 14235 24135 34125 43125 53124 14253 24153 34152 43152 53142 14325 24315 34215 43215 53214 14352 24351 34251 43251 53241 14523 24513 34512 43512 53412 14532 24531 34521 43521 53421 15234 25134 35124 45123 54123 15243 25143 35142 45132 54132 15324 25314 35214 45213 54213 15342 25341 35241 45231 54231 15423 25413 35412 45312 54312 15432 25431 35421 45321 54321 == ' VBA CODE: '- Dim D() As String ' =Array Data Characters Dim N As Byte' =number of char Dim Tulis As Range ' =range where output data to be written Dim oRowAs Long' =Row index for Output Dim oColAs Byte' =COlumn index for Output Dim MaxRow As Long' =oRow when reaching Fact(N-1) Sub PermutArranger_Jilid3() '-- ' this procedure is only preparing variables inisials, and ' calling ArrangeAndWrite (main procedur) for the first time '-- Dim k As Byte oRow = 0: oCol = 1 N = Len(Trim(Range(B2))) Set Tulis = Range(L5) ReDim D(1 To N) As String MaxRow = WorksheetFunction.Fact(N - 1) ActiveSheet.Unprotect xpas ClearDataArea ActiveSheet.Range(L2) If N 9 Then MsgBox max 9 digit, due to limitation on number of cells in a sheet.., _ 16, ThisWorkbook.Name Exit Sub End If For k = 1 To N: D(k) = Mid(Trim(Range(B2)), k, 1): Next k Application.Calculation = xlCalculationManual Call ArrangeAndWrite(D, 1) Application.Calculation = xlCalculationAutomatic ActiveSheet.Protect xpas End Sub Private Sub ArrangeAndWrite(ByVal D, i As Byte) ' this is a recursive procedure '- Dim txt As String, tmp As String * 1, j As Byte *' limitation (if i = N)* If i = N Then For j = 1 To N: txt = txt D(j): Next j If oRow = MaxRow Then oRow = 0: oCol = oCol + 1 End If oRow = oRow + 1: Tulis(oRow, oCol) = txt Else For j = i To N tmp = D(j): D(j) = D(i): D(i) = tmp ArrangeAndWrite D, (i + 1) Next j End If End Sub '--- you can download my example workbook, if you like, at: http://www.box.net/shared/22pginzj8p kind regards, siti Vi On Sat, Jul 2, 2011 at 10:47 PM, Markkim mark@gmail.com wrote: Hi I can think of a way to write this macro. What I want is to find any possible combination of numbers from given numbers to a given total. For example, Let's say Given numbers: 1, 2, 3, 4, 5 total = 30 Will it be possible to get any combination of Given numbers adding up to 30? Macro should say number of possible combinations and details of combindation I have been thinking about this macro for long time... but I can't think of any way.. Any ideas??? cheers! -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ workbook location on status bar
'--Thisworkbook Module:-- Dim OldStaBar As String Private Sub Workbook_Open() OldStaBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = ThisWorkbook.FullName End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayStatusBar = False Application.DisplayStatusBar = OldStaBar End Sub On Mon, Jul 4, 2011 at 2:41 AM, netuser501 netuser...@gmail.com wrote: Hi, Is there a code or a build-in option to have the location path of a workbook permanently showing on the Excel status bar? Thanks Pascal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 req.
Sub Insert2Rows() ' siti Vi / jakarta, 29 Jun 2011 '--- Dim RNG As Range, r As Long Set RNG = ActiveSheet.Cells(1).CurrentRegion.Offset(1, 0) For r = RNG.Rows.Count To 2 Step -1 If r Mod 2 0 Then RNG(r, 1).Resize(2, 1).EntireRow.Insert Next End Sub On Wed, Jun 29, 2011 at 6:29 PM, Rakesh Sharma rksharma...@gmail.comwrote: *Dear all, * * * *please help* thanks in adv. -- Regards Rakesh Sharma -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ User form + Macro to activate certain region of active work sheet.
Dim ws As Worksheet Const pass As String = rash Dim ArChkBox(1 To 6) As Object Private Sub UserForm_Activate() Set ArChkBox(1) = CheckBox1 Set ArChkBox(2) = CheckBox2 Set ArChkBox(3) = CheckBox3 Set ArChkBox(4) = CheckBox4 Set ArChkBox(5) = CheckBox5 Set ArChkBox(6) = CheckBox6 End Sub Private Sub Locked_Unlocked(Rowval As Long) Dim r As Long r = Rowval * 10 - 9 Sheet1.Unprotect pass Sheet1.Cells.Locked = True If ArChkBox(Rowval) Then Cells(r, 1).Resize(10, 1).EntireRow.Locked = False Else Cells(r, 1).Resize(10, 1).EntireRow.Locked = True End If Sheet1.Protect pass If Rowval 1 Then ActiveWindow.ScrollRow = r - 2 Else: ActiveWindow.ScrollRow = r Cells(r, 1).Resize(10, 1).EntireRow.Select End Sub Private Sub CheckBox1_Click() Call Locked_Unlocked(1) End Sub Private Sub CheckBox2_Click() Call Locked_Unlocked(2) End Sub Private Sub CheckBox3_Click() Call Locked_Unlocked(3) End Sub Private Sub CheckBox4_Click() Call Locked_Unlocked(4) End Sub Private Sub CheckBox5_Click() Call Locked_Unlocked(5) End Sub Private Sub CheckBox6_Click() Call Locked_Unlocked(6) End Sub On Wed, Jun 29, 2011 at 1:53 PM, Rash rashmiv.ni...@gmail.com wrote: Hi all, Need Help... I have a user form in which i have 6 check boxes. ok and cancel button. when ever I click 1st check box, rows 1 to 10 should to be editable, rest all rows should be non editable and hidden. For check box 2, rows 11 to 20 editable, rest up and down area should be non editable and hidden. And vice versa for other check boxes. I need a macro for this. Attached is the excel sheet with user form created in background. Thanks in advance, Rsh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Test_Cells_Locking_Unlocking.xlsm Description: Binary data
Re: $$Excel-Macros$$ VBA object?
You need some Variables (memory variable) http://www.excel-vba.com/excel-vba-contents.htm Sub Test() '--declaring variable Dim MyCell As Range Dim MyValue As Variant Dim CompareResult as Boolean '--assignments Set MyCell = Range(A1) MyValue = MyCell.Value '--copying the value to another cell Range(T4).Value = MyValue '--comparaing two thing (in same Type) '--you can compare two values by one of the Comparison Operators : ' = / / / = / = / / Like ' http://www.excelfunctions.net/VBA-Operators-And-Functions.html ' http://sourcedaddy.com/ms-excel/working-with-vba-operators.html CompareResult = (Range(T4).Value* = *MyCell.Value) MsgBox CompareResult or in another expression If Range(T4).Value *=* MyCell.Value Then MsgBox Equal Else MsgBox Not Equal End If End Sub hope it helps CMIIW On Mon, Jun 27, 2011 at 10:10 PM, Andrew nordies_1...@yahoo.com wrote: I am new to VBA and would like to ask everyone a question about how to capture an object? I want to copy a value and position in excel. For example: 1 - Find cell A1 and get the contents, code1234 and remember that position 2 - I want to copy the contents to cell T4 3 - Go back to cell position A1 and compare it to the value of cell A2 So i think my problem right now being a newbie at this is that i can record the actions using the recorder and can copy and paste a cell by referring to it as say, Range(F48).Select But if i want to know the value of the F48 cell i am not sure how to do that after i have copied that cell? Also how do you compare the value of cells? For example i want to compare cells, A1 and A2? Andrew -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Excel Pop-ups
please check this vba code, hope it helps.. -Standard Module Sub ExpiryWarning() ' siti Vi // Jakarta, 19 Jan 2011 ' Dim dTable As Range, t As String Dim R As Long, N As Long, I As Integer Set dTable = Cells(1).CurrentRegion N = dTable.Rows.Count For R = 2 To N If IsDate(dTable(R, 5)) Then I = dTable(R, 5) - Date If I = 15 Then If I 0 Then dTable(R, 1).Resize(1, dTable.Columns.Count).Select t = Name : dTable(R, 2) vbCr vbCr t = t Expiry Date : Format(dTable(R, 5), dd-mmm-) vbCr vbCr t = t Number of days to come : Format(I, 0) vbCr MsgBox t, 48, Warning End If End If End If Next Cells(1).Activate End Sub -Module Sheet1 Private Sub Worksheet_Activate() ExpiryWarning End Sub -Tiisworkbook Module Private Sub Workbook_Open() ExpiryWarning End Sub On Sun, Jun 19, 2011 at 12:25 AM, Mahreen Ellahi mahreen.acc...@gmail.com wrote: Hii I have a some data with limits expiry on different dates. I want to have popup window to appear whenever the limit is about to expire like 15 to 30 days earlier. Can anyone help me for VBA coding. Regards Mahreen -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Excel MsgBox.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Recover delete file.
use Recuva Recuva recovers files deleted from your Windows computer, Recycle Bin, digital camera card, or MP3 player. And it's free! http://www.piriform.com/recuva http://www.piriform.com/recuva On Fri, Jun 17, 2011 at 12:26 PM, Bhushan Sabbani bsabban...@gmail.comwrote: Dear Excel Expert, I was using the below code to delete the temp data file by mistake my personal which contain all the macro and the senstive data got delete i have serach in the recycle bin and whereever pls help me to find the file the file name was personal.xls Sub Killed() Application.DisplayAlerts=False ThisWorkbook.ChangeFileAccess xlReadOnly Kill ThisWorkbook.FullName ThisWorkbook.Close False End Sub Thanks in advance. 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
Re: $$Excel-Macros$$ Transpose Data by Formula
Dear Mr. Smith, Please try and check this VBA Code, if it helps... Sub AbnormalizeYourTabel() ' STDEV(i) / milis belajar-excel / 08 apr 2011 ' retouched for another table-structure ' jakarta, Jun 12, 2011 '--- Dim Tbl As Range, NewTbl As Range Dim n As Long, r As Long, i As Long, tR As Long Dim c As Integer, u As Integer, TotQty As Double, ArtQty() Dim StrItm As String, Itm As String Set Tbl = Sheets(Data Transpose).Cells(1).CurrentRegion tR = Tbl.Rows.Count Set NewTbl = Tbl(tR + 6, 1) ' searching UniqItems StrItm = | Application.Calculation = -4135 '=manual Application.ScreenUpdating = 0 For i = 2 To tR Itm = Tbl(i, 1) | If InStr(1, StrItm, Tbl(i, 1), 1) = 0 Then r = r + 1 StrItm = StrItm Itm Tbl(i, 1).Resize(1, 2).Copy NewTbl(r, 1).PasteSpecial 12 End If Next i ' Repost all datas in New Table-Structure Application.CutCopyMode = False Set NewTbl = NewTbl.CurrentRegion ReDim ArtQty(1 To NewTbl.Rows.Count) For n = 1 To NewTbl.Rows.Count c = 0: TotQty = 0 For i = 2 To tR If NewTbl(n, 1) = Tbl(i, 1) Then c = c + 3 *TotQty = TotQty + Tbl(i, 3*) Tbl(i, 3).Resize(1, 3).Copy NewTbl(n, c).PasteSpecial 12 End If Next i *ArtQty(n) = TotQty* Next n Tbl.Resize(1, Tbl.Columns.Count - 2).Copy NewTbl(0, 1) u = (NewTbl.CurrentRegion.Columns.Count - 1) ' Headings.. Tbl(1, 3).Resize(1, 3).Copy For c = 3 To u Step 3 NewTbl(0, c).PasteSpecial xlAll Next c * ' new column (Total Qty)* With NewTbl(0, c) .Value = TOTAL QTY .Font.Bold = True .BorderAround Weight:=xlThin .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True End With For n = 1 To NewTbl.Rows.Count NewTbl(n, c) = ArtQty(n) Next n Application.CutCopyMode = False Application.Calculation = -4105 Application.ScreenUpdating = 1 End Sub On Mon, Jun 13, 2011 at 2:07 AM, John A. Smith johnasmit...@gmail.comwrote: Please, attached is an example of data which I need to transpose and a pivot table doesn't do it... Thank you for your help. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Abnormalize Your Normal Tabel.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Reverse Vlookup - -- very very urgent
=OFFSET($A$1,MATCH(E2,$B$2:$B$8,0),0) or =INDEX($A$2:$B$8,MATCH(E2,$B$2:$B$8,0),1) On Fri, Jun 10, 2011 at 3:14 AM, hanumant shinde arsfan2...@yahoo.co.inwrote: Hi friends, this is really really urgent and very very very IMP for me so quick help would be really really appreciated. Name No a 1 s 2 d 3 f 4 g 5 h 6 j 7 data is like above i.e. first Name and then No. i want to Vlookup for No. values and get Name but without moving any col or modifying anything. i.e something like =VLOOKUP(E2,$B$2:$D$8,3,0) but this will not work as in a range parameter No. column is not at 1st place. please find the attached workbook for more details. let me know if you need anything else. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Vlookup_to_LEFT.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Print area that has data
May be, by hiding the rows that contains formula, before printing A cell contaning formula can be detected by *HasFormula property* Please check this code if it helps. Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim MyRng As Range, r As Long, c As Integer Set MyRng = ActiveSheet.UsedRange Application.ScreenUpdating = False For r = 1 To MyRng.Rows.Count For c = 1 To MyRng.Columns.Count If MyRng(r, c).HasFormula Then MyRng(r, c).EntireRow.Hidden = True End If Next c Next r Application.ScreenUpdating = True End Sub best regards, siti Vi On Mon, Jun 6, 2011 at 5:10 AM, Bob bobandrich...@comcast.net wrote: Is there a way to print the rows with data in them and not print the rows with just the formula. My spreadsheet has several rows with formulas in them and I only want to print the rows which I have added data. I know you can select print area and all that. But mainly it's for the inexperianced user that might have trouble setting the print area tab. Is there a setting that I can set or a macro for this? Thanks in Advance!!! -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel ctv_hiding formulasRow.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Find value in Active Cell in another sheet(s) delete
please check and try this VBA code if it helps Sub DoSomething() * ' siti Vi* Dim w As Worksheet, xCel As Range Dim sCriteria As String sCriteria = MyCriteriaText ' please edit For Each w In Worksheets If Not w.Name = ActiveSheet.Name Then If Not LCase(w.Name) = home Then For Each xCel In w.UsedRange If xCel.Value = sCriteria Then xCel.Clear Next xCel End If End If Next w End Sub On Sat, Jun 4, 2011 at 11:47 AM, sswcharlie swch...@gmail.com wrote: Hi Part of a another code - I want to find the value(text) in active cell (value changes often) in workbook (except current WS and one other worksheet 'home) and delete. Could be more than one instance. Thanks Charlie -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Find value in Active Cell in another sheet(s) delete
Dear GoldenLance, Thank you very much for your great sugestion.. user just *selects/activates a cell* where the criteria is written, and run the macro... kindest regards, STDEV(i) On Sat, Jun 4, 2011 at 5:57 PM, GoldenLance samde...@gmail.com wrote: Just a minor tweak to STDEV(i)'s code. Based on the original subject, I might change *sCriteria* = MyCriteriaText to sCriteria = * ActiveCell.Value* On Jun 4, 2:24 pm, STDEV(i) setiyowati.d...@gmail.com wrote: please check and try this VBA code if it helps Sub DoSomething() * ' siti Vi* Dim w As Worksheet, xCel As Range Dim sCriteria As String sCriteria = MyCriteriaText ' please edit For Each w In Worksheets If Not w.Name = ActiveSheet.Name Then If Not LCase(w.Name) = home Then For Each xCel In w.UsedRange If xCel.Value = sCriteria Then xCel.Clear Next xCel End If End If Next w End Sub On Sat, Jun 4, 2011 at 11:47 AM, sswcharlie swch...@gmail.com wrote: Hi Part of a another code - I want to find the value(text) in active cell (value changes often) in workbook (except current WS and one other worksheet 'home) and delete. Could be more than one instance. Thanks Charlie -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Moving files from folder to folder
Sub Copy_One_File_Only() FileCopy D:\Bla_Bla_Bla\SOS\SourceFolder\FileName.pdf, E:\WOW\AngelinaJoly\DestinFolde\FileName.pdf End Sub On Sun, May 29, 2011 at 11:06 PM, PAB pab1...@gmail.com wrote: Hi Using VBA in Excel 2003 is it possible to select a pdf file in a folder and move it to another folder. If not, how can I automate this procedure. Thanks Paul -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Formula to extract parent folder, parent of parent folder etc
try this formula =*SUBSTITUTE(B3* ,MID(B3,1+FIND(^,SUBSTITUTE(B3,\,^,LEN(B3)-LEN(SUBSTITUTE(B3,\,-1,999),) and see the attachment if it helps On Fri, May 27, 2011 at 12:56 PM, Jhoomla amith.yeshwa...@gmail.com wrote: Does any one have a function or formula to extract for a path for eg., E:\02 My Documents\20 Ebooks and Articles\10.04 Tintin\01 Tintin in the Land of the Soviets.pdf Parent Folder: = 10.04 Tintin Parent.Parent.folder = 20 Ebooks and Articles Parent.Parent.Parent.Folder= 02 My Documents any help in this regard would be greatly appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_File Path and his parents.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Dates in Excel
Assuming that you r date is in Cell A1 Convert them with this formula: Formula in B1=Date(2010,Month(A1),Day(A1)) On Fri, May 27, 2011 at 12:49 AM, Rich Prince rich.pri...@gmail.com wrote: I have a column of various dates that have the incorrect year, for example: 9/1/2011 12/17/2011 10/31/2011 etc, etc... The year should be 2010 not 2011. Is there a quick remedy to correct this problem for several hundred dates? The Month and day are correct - just not the year. Any ideas? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Keep formula in cell
when you type a data ( event a dot ) in a cell, the old data (incl. FORMULA) in the cell will be removed and replaced with the new data. with macro you can automatically keep the old data (and stored in another place) and then enter the new value in the same cell cmiiw On Mon, May 23, 2011 at 5:21 AM, Bob bobandrich...@comcast.net wrote: Is there a way to keep the formula in the cell when you enter your own value? Thanks in advance -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to remove space in excel cell
check whether the character is space / CHAR(32) =CODE(A1) if this formula returns 32 then use TRIM if this formual returns other numbers that 32 (space) use SUBSTITUTE On Wed, May 18, 2011 at 2:43 PM, anandydr anand...@gmail.com wrote: At times the space is leading space which is not completely removed using TRIM ( ) could anyone suggest what to do in such case? On May 18, 3:38 am, STDEV(i) setiyowati.d...@gmail.com wrote: sometimes what we thought as the space (as prefix or suffix) turns out to be *Char (160)*so we need formula like this =SUBSTITUTE(A1,CHAR(160),) On Sun, May 15, 2011 at 12:48 PM, Prabhu prabhugate...@gmail.com wrote: Hi friends, How to remove spaces(bugs) in a particular cell either will be as prefix or suffix before or after number or text. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 on Transpose Function based upon criteria
*Array Formula, written in 9 cells (oneRow Range) at once !* =IFERROR(TRANSPOSE(INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=$C16,ROW($1:$9)),ROW($1:$9,) On Wed, May 18, 2011 at 5:36 PM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote: Hi All, I want to transpose certain data from row to column if meets specific criteria: eg Whole data is as below: Claim No. Amount 1 10 2 20 1 30 1 10 2 20 I want as Claim No. 1 10 30 10 I want transpose to happen if meets criteria that it match the claim number, i have 5 records as such. I have attached an spreadsheet as example Please help its urgent. Regards, Kaushik -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Conditional_Transpose.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Special Cursor Movement After the Enter Key is Pressed
Put this code into *Sheet1 Module* Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count = 1 Then If .Column = 8 Then Target(1, 2).Activate If .Column = 9 Then Target(2, 0).Activate End If End With End Sub ' this macro run in column H and column I of Sheet1 only On Tue, May 17, 2011 at 5:58 PM, John A. Smith johnasmit...@gmail.comwrote: I need a macro that will move the curser in a specific pattern of; Right one cell the first time, and down one and left one the second time and then repeat. It would save a lot of time entering the data in two side by side columns so I need it to not be column specific (so I could use it in any two side by side columns). Thank you for your continueing dedication to helping others with Excel. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Special Cursor Movement Upon Hitting Enter on H or I columns ONLY.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ database concept - Reg
it looks like you need a textbook / excel standard manual Go to a bookstore and buy one you can also visit to this sites: http://blog.contextures.com/archives/2010/03/10/sort-it-your-way-with-excel-custom-lists/ http://www.ehow.com/how_5905455_create-custom-list-excel-2007.html http://spreadsheets.about.com/od/excel101/ss/enter_data.htm http://spreadsheets.about.com/od/excel101/ss/enter_data.htm http://oit.utk.edu/scc/HowToUseExcelForDataEntry.pdf http://oit.utk.edu/scc/HowToUseExcelForDataEntry.pdf http://www.timeatlas.com/5_minute_tips/general/excel_data_form_makes_input_easier http://www.timeatlas.com/5_minute_tips/general/excel_data_form_makes_input_easier http://www.internet4classrooms.com/excel_enter_edit.htm http://www.internet4classrooms.com/excel_enter_edit.htm http://www.homeandlearn.co.uk/me/mes10p1.html On Tue, May 17, 2011 at 9:36 PM, jmothilal gjmothi...@gmail.com wrote: how to create custom list , pls help me -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to remove space in excel cell
sometimes what we thought as the space (as prefix or suffix) turns out to be *Char (160)*so we need formula like this =SUBSTITUTE(A1,CHAR(160),) On Sun, May 15, 2011 at 12:48 PM, Prabhu prabhugate...@gmail.com wrote: Hi friends, How to remove spaces(bugs) in a particular cell either will be as prefix or suffix before or after number or text. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ insert new row if column is missing date....
Sub InsertDatetUntilToday() ' coded by: siti Vi / Jakarta, May 16, 2011 '--- Dim SeleDate As Date, DiffDate As Long, n As Long Do While ActiveCell 0 If IsDate(ActiveCell) And ActiveCell Date Then If Not IsEmpty(ActiveCell(2, 1)) Then SeleDate = ActiveCell.Value DiffDate = Date - SeleDate ActiveCell(2, 1).Resize(DiffDate, 1).EntireRow.Insert For n = 1 To DiffDate ActiveCell(n + 1, 1) = SeleDate + n Next n End If End If ActiveCell(n + 1, 1).Activate Loop Me.Cells(1).Activate End Sub On Sun, May 15, 2011 at 4:32 PM, Stuart swilson2...@gmail.com wrote: Hi all I hope someone can help with this. My data is in columns A to F with the short date being in column B. The data is sorted in date order and the first date that appears in column B is 23/12/2002 and the next row contains the date 14/01/2002. What I need is for a macro to look at the first date in the cell that I have selected and then look at the next date and fill in the missing dates until it reaches today. The data can go down in an unlimited amount of rows. So basically the macro would insert a new row with the date is column B as 24/12/2002, 25/12/2002, 26/12/2002 and so on and so forth. I hope this makes sense to someone Best regards Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Insert Row and Date.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel filtered data selection
you should visit http://www.rondebruin.nl/merge.htm On Mon, May 16, 2011 at 12:44 AM, Prathima R prathima@gmail.com wrote: Hi All, i have excel data in five differrent workbooks which i will consolidate into one workbook and apply filter and select data by category Xin particular column and if yes need to be moved to another sheet and rename as In active.. Please advise themacro or code to be used to select data by filtering the excel sheet and select only X category in particular specified column. Thanks, Prathima -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: no of days in the month ?
i like this cool formula =32-DAY(A1-DAY(A1)+32) last day in a month / number of days in a month based on a date. On Sun, May 15, 2011 at 8:53 AM, Armando amon...@gmail.com wrote: Try these: =DAY(EOMONTH(A1,0)) =32-DAY(A1-DAY(A1)+32) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ no of days in the month ?
thanks to mr verma, with his formula =DAY(DATE(YEAR(TODAY()),MONTH(*(B21)*)+1,1)-1) how about =DAY(DATE(YEAR(TODAY()),MONTH((B21))+1,*0*)) The *last day of this month* is = the *0*th day of the Next Month !! On Sat, May 14, 2011 at 7:03 PM, rajan verma rajanverma1...@gmail.com wrote: find attached Sheet On Sat, May 14, 2011 at 3:36 PM, jmothilal gjmothi...@gmail.com wrote: Dear Friends i want find total no days in the month i.e January = 31 days Feb = 29 days march = 30 days like -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 your keyboard On Wed, May 11, 2011 at 6:15 PM, jmothilal gjmothi...@gmail.com wrote: I am typing Name and place name day 2 day different excel files, what is easy method i type these names and place names. -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to change Date format
thank you mr sixthsense may i modif the formula to be: =DATE(YEAR( DATEVALUE(1JanRIGHT(B2,2))),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2)) On Fri, May 13, 2011 at 6:03 PM, Sixthsense sixthsense...@gmail.com wrote: Hi Stdev, Your Solution will fail when the person intended to mention the year 1998 in this manner 10498. --- *Sixthsense **:) Man of Extreme Innovative Thoughts :)* On Fri, May 13, 2011 at 4:13 PM, STDEV(i) setiyowati.d...@gmail.comwrote: * =DATE(2000+RIGHT(B2,2),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2)) * see the attachment On Thu, May 12, 2011 at 7:14 PM, Prabhu prabhugate...@gmail.com wrote: Hi friends, Plz help to change date format when downloaded report from 1st of every month to 9th of the month will be like DMMYY . I have no issue in changing the format using text to column from 10th date because it will be in DDMMYY. I have attached the sample sheet for your reference.Plz help to know the date format as DDMMYY for entire month.(i am requesting macros to do the same) Regards, Pr -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv__bsninvreport(V2).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Re: How to change Date format
another variant =DATE(20RIGHT(B2,2),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2)) On Thu, May 12, 2011 at 8:58 PM, GoldenLance samde...@gmail.com wrote: Use this in E2 and drag down =DATE(2000+RIGHT(B2,2),LEFT(RIGHT(B2,4),2),LEFT(B2,LEN(B2)-4)) On May 12, 5:14 pm, Prabhu prabhugate...@gmail.com wrote: Hi friends, Plz help to change date format when downloaded report from 1st of every month to 9th of the month will be like DMMYY . I have no issue in changing the format using text to column from 10th date because it will be in DDMMYY. I have attached the sample sheet for your reference.Plz help to know the date format as DDMMYY for entire month.(i am requesting macros to do the same) Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 help to make my project good.
please check the attachment if it helps.. best regards, STDEV(i) note: we need dynamic* Max Value* of ScrollBar so we use Scroolbar from ActiveX Control, instead of from FORM. A little macro wil work each time you change the career On Wed, May 11, 2011 at 5:21 PM, karan 1237 karan1...@gmail.com wrote: Respected Sirs/Mam, I need your help making my project good. Please find attachment. I will be very obliged if someone can help me as earlier as possible. -- Basically I have to do that when I click on drop down list select another career i.e. Building and Construction so In sub career list data should comes from the Building and construction's sheet and If I select another career i.e. Catering and Hospitality so data should comes from its list (Catering and Hospitality) and scroll bar should work with the same. I have 16 careers so please tell me how can I do this. Thnx in Advance ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Indirect And Address FUNCTION.xlsb Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
Re: $$Excel-Macros$$ Auto Refresh Data
try using Application.OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm http://www.cpearson.com/excel/OnTime.aspx On Mon, May 9, 2011 at 1:17 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi All, I am having one database which is link to the external data (Internet)… I need the VBA code where the data will automatically refresh after 30 seconds / 15 Seconds, but it should not be a loop it just a macro automatically running after 15 sec. *Example : My data is refreshed at 11:44:15 time and I wanted macro to be automatically run at 11:45:30 and again after 11:45:45 then 11:50:00 and so on…* Please suggest something. I am having one problem using loop here because while macro is running in background I can’t able to work on other files and I wanted that macro to be run at background and I should able to run on the files. Is there any way to do it.. Thanks Regards, Anil Bhange IP - 512320 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Need Help
dear Mr. Verma, et us try both macros with the NEW source table (as New Input) like this please find attached: ** a workbook containing this data ** a workbook containing both macros X1 1.7 11/11/2008 01/11/2010 X2 2.4 11/12/2009 01/11/2010 X3 3.8 11/01/2010 01/11/2010 X4 2.5 12/30/2009 01/11/2010 X5 4.2 11/22/2009 01/11/2010 X6 4.9 02/01/2010 01/11/2010 X7 5 01/06/2010 01/11/2010 X8 5.5 01/16/2010 01/11/2010 X9 5.2 01/03/2010 01/11/2010 if you don't mind, you are requested to attached the result sheets to this groups. I 'll do the same on May 10, 2011. thank you and best regards STDEV(i) On Sun, May 8, 2011 at 3:13 PM, rajan verma rajanverma1...@gmail.com wrote: hi STDV(i) Your macro only working with 3 Rows.. if he want to add more data in that table it will not expend all table.. On Sun, May 8, 2011 at 1:40 PM, rajan verma rajanverma1...@gmail.com wrote: hi find attached File On Sat, May 7, 2011 at 11:33 AM, anvesh.gw...@gmail.com anvesh.gw...@gmail.com wrote: Hi, I need help in VBA code. I have excel sheet on which data is write as below mention Input A B C D 1 X1 1.5 11/11/2008 01/11/2010 2 X2 2.5 11/12/2009 01/11/2010 3 X3 3.5 11/01/2010 01/11/2010 I want Output in another sheet as given below Output A B C D 1 X1 1.0 11/11/2008 01/11/2010 1 X1 0.5 11/11/2008 01/11/2010 2 X2 2.0 11/12/2009 01/11/2010 2 X2 0.5 11/12/2009 01/11/2010 3 X3 3.0 11/01/2010 01/01/2010 3 X3 0.5 11/01/2010 01/01/2010 Procedure 1. Check column B if it contain decimal than separate integer and decimal i.e if value is 1.5 than insert row and split as mention above in output 2. Check C and D for date if C column Date is less than D Column than pick Date of D column other wise pick date of C Column. Can any body help me in this. Thanks in advance Anvesh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 NewInput for Anvesh_Case.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 2X_Solution.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ get name of months from date
Unfortunately *Month Function* returns *Month Index ( 1 to 12 )* not *Month NAME* Assuming A1 containts date data : 12/31/2011 Formula in B1 =MONTH(A1) returns : *12* not *December* In vba you can used MonthName Function Sub AboutMonth() Range(B1) = Month(Range(A1)) Range(C1) = *MonthName(Range(B1))* End Sub In worksheet there is no MonthName function CMIIW On Mon, May 9, 2011 at 2:44 AM, Nishant Sharma leonish...@gmail.com wrote: * Use Month formula open excel and copy this in A1 04-04-2011 =month(A1) Thanks Nishant * On Sun, May 8, 2011 at 4:48 PM, Rajesh K R rajeshkainikk...@gmail.com wrote: Hi Experts If I want to get the name of month from dates 1/4/11- April 5/5/11 - May how I can I do it with a formula or code Regards Rajesh Kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ A small query, need your help in vba coding.
Your code looks like to be run in excel 2003 only (1) In both excel 2003 / excel 2007 Range(A65000) ' last row in A column can be coded as Cells(*Rows.Count*,1) or Range(A *Rows.Count*) (2) *Value* property of a Range Object is a *DEFAULT Property* so you can ignore it For example Range(A1).*Value* = Range(C1).*Value* can be coded as: Range(A1) = Range(C1) * * On Sat, May 7, 2011 at 1:14 PM, rajan verma rajanverma1...@gmail.comwrote: Private Sub CommandButton1_Click() *Range(A65000)*.End(xlUp).Cells(2, 1) = Range(C1).Value: * Range(A65000)*.End(xlUp).Cells(1, 2) = Range(D1).Value: End Sub On Sat, May 7, 2011 at 1:56 AM, Indrajit $nai talk2indra...@gmail.comwrote: Hi All, I have a little query in vba coding, please see the attached file. In the excel file there is a Save command button, whenever I press the button it will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my query is, if the data is already in the cell no. a1:b1 it will be saved in the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone help me in that coding how do I create that loop? Thanks in advance. Indrajit -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to use Vlookup in macro
It would be easy for anyone in the group to respond, if you can send a set of sample data. On Sat, May 7, 2011 at 1:47 PM, maulik desai mauliksde...@gmail.com wrote: Hi All, I have excel database sheet I have created some excel output sheets (around 35 excel sheets) which gives me output data with the help of Vlookup fuction. But the problem is in every 3 months I have to change the database month name because of that I need to change the vlookup range also in output sheets manually (need to open each sheet change the new file name in that) Is there any way that whenever I changed the file name of database ,the same new range also updated in output sheets automatically. Thanks In advance. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Please Help
try to use this UDF (user defined function) Function UniqueList(Dat As Range) ' List of UnSorted Unique Values ' '-' Dim Cel As Range, vArr(), n As Long, Tx As String Tx = , For Each Cel In Dat If InStr(1, Tx, , Cel , ) = 0 Then Tx = Tx Cel , End If Next Cel If Len(Tx) 0 Then Tx = Left(Tx, Len(Tx) - 2) If Len(Tx) 0 Then Tx = Right(Tx, Len(Tx) - 2) UniqueList = Tx End Function On Sat, May 7, 2011 at 3:50 PM, karan 1237 karan1...@gmail.com wrote: * LIST1* *LIST1* *LIST1* *RESULT* BACK SIDE FRONT BACK, SIDE, FRONT FRONT SIDE FRONT FRONT, SIDE BACK FRONT BACK BACK, FRONT FRONT FRONT FRONT FRONT *Hi Experts, I want like this. Please help. Thnx in Advance. * ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 UDF_UniqValues.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ =Now() Keep from changing
instead of writing =NOW() or =TODAY() in a cell select a cell then press Ctrl + ;(for FIXED today's date ) press Ctrl + Shift + :(for Fixed Now time ) On Sun, May 8, 2011 at 2:12 AM, Bob bobandrich...@comcast.net wrote: If I enter the Customer in B27 I'd like for todays date to be entered in F27 automatically. How would you keep the date from changing the next day, each time the workbook is opened? Thanks in advance!!! -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ A small query, need your help in vba coding.
Dear Indrajit please try this code Private Sub CommandButton1_Click() ' siti Vi / jakarta, May 07, 2011 Dim R As Long R = WorksheetFunction.CountA(Range(A:A)) + 1 Cells(R, 1) = Cells(1, 3) Cells(R, 2) = Cells(1, 4) End Sub On Sat, May 7, 2011 at 3:26 AM, Indrajit $nai talk2indra...@gmail.comwrote: Hi All, I have a little query in vba coding, please see the attached file. In the excel file there is a Save command button, whenever I press the button it will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my query is, if the data is already in the cell no. a1:b1 it will be saved in the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone help me in that coding how do I create that loop? Thanks in advance. -- Indrajit -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ A small query, need your help in vba coding.
just a little modif: Private Sub CommandButton1_Click() Dim R As Long * R = Cells(Rows.Count, 1).End(xlUp).Row + 1* Cells(R, 1) = Cells(1, 3) Cells(R, 2) = Cells(1, 4) End Sub On Sat, May 7, 2011 at 11:38 AM, rajan verma rajanverma1...@gmail.comwrote: It macro will not work properly if these is any blank row between the Data.. It will all time replace existing Data.. On Sat, May 7, 2011 at 8:56 AM, STDEV(i) setiyowati.d...@gmail.comwrote: Dear Indrajit please try this code Private Sub CommandButton1_Click() ' siti Vi / jakarta, May 07, 2011 Dim R As Long R = WorksheetFunction.CountA(Range(A:A)) + 1 Cells(R, 1) = Cells(1, 3) Cells(R, 2) = Cells(1, 4) End Sub On Sat, May 7, 2011 at 3:26 AM, Indrajit $nai talk2indra...@gmail.comwrote: Hi All, I have a little query in vba coding, please see the attached file. In the excel file there is a Save command button, whenever I press the button it will copy the data from cell no. c1:d1 and paste in the cell a1:b1, now my query is, if the data is already in the cell no. a1:b1 it will be saved in the next cell no. means a2:b2 then a3:b3 just going on like this, can anyone help me in that coding how do I create that loop? Thanks in advance. -- Indrajit -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ How to reduce the file size
how can we forget this: http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html On Wed, Apr 27, 2011 at 6:05 PM, Sundarvelan N nsund...@gmail.com wrote: How to reduce the file size -- Thanks N.Sundarvelan 9600160150 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Convert Excel Ranges to PDF
Save As *.PDF (excel 2007) [image: save as PDF.GIF] On Thu, Apr 28, 2011 at 7:39 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Expert, I am having one excel file which contains many report, I wanted to convert the same in PDF. Below example will give you further details, Sheet 1 : from A2 : K30 Sheet 2 : from A2 : K 45 Sheet 4 : from B5 : H14 Worksheet also has other details but I wanted to convert those specific range only. Thanks Regards, Anil Bhange IP - 512320 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 save as PDF.GIF
Re: $$Excel-Macros$$ Please Help.................
by hiding application objec at time of userform initialized or activated Application.visible = False and don' forget to unhide the aplication (excel) at time of userform deactivated or unloaded Application.visible = False se attachaed workbook On Wed, Apr 27, 2011 at 1:38 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Experts, How can Hide only workbook but userform should be visilble and work as it, when we click on *Eng In out Report* button. Please see attached sheet.. Thanks in Advance -- Thanks regards, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 Query (mr_ansari).xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ A VBA Challenge.. (Arrays)
when you say it is a challenge; then you have to have your own good answer. Sub Time_Test_Array2() Dim DatRng As Range, LastRow As Long Dim r As Long, i As Long Application.ScreenUpdating = False Range(E:E, K1:K3).Clear Range(K1) = Timer LastRow = Cells(Rows.Count, 1).End(xlUp).Row Set DatRng = Range(A1:A LastRow) For i = 1 To DatRng.Rows.Count If DatRng(i, 1) Mod 2 = 0 Then Cells(i, 5) = Event Number Else Cells(i, 5) = Odd Number End If Next i Range(K2) = Timer Range(K3) = Timer - Range(K1) End Sub On Sun, Apr 24, 2011 at 9:13 PM, Deepak Pal Singh deepaktheind...@gmail.com wrote: Hi Guy's Need your help in this code.. in the following code... Sub Time_Test_Array2() Range(K1) = Now() Application.ScreenUpdating = False Range(E:E).Clear LastRow = 0 LastRow = Range(A100).End(xlUp).Row iVal = Range(A1:A LastRow).Value ReDim strArray(1 To UBound(iVal)) For i = LBound(iVal) To UBound(iVal) If iVal(i, 1) Mod 2 = 0 Then strArray(i) = Even Number Else: strArray(i) = Odd Number End If Next i Range(Cells(LBound(strArray), 5), Cells(UBound(strArray), 5)).Value = strArray 'Range(Cells(LBound(iVal), 5), Cells(UBound(iVal), 5)) = iVal ActiveSheet.Range(K2) = Now() End Sub Suppose there’re some numbers given on column “A”, I want to print whether the number is Odd or Even in column “E” using Arrays, my code is working but printing wrong values, instead of printing “Even Number” for an even number it is printing “Odd Number”, there can be a logical error in the code. All The Best.. J Thanks Regards Deepak -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel ctv_Even and Odd.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Dear Group Please Help
1. *array formula* =OFFSET(Sheet1!$B$6:$C$6,MATCH($C$4,Sheet1!$B$6:$B$55,0),0,5,2) witten on I6:J10 at one ! 2. formula wittten in one cell I5 =$C$4 On Fri, Apr 22, 2011 at 1:47 PM, Jai jaihumtu...@gmail.com wrote: Attachemtn attached -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 HELP OF HELP.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ help..
then you have to ask to the author of the spreadsheet On Fri, Apr 22, 2011 at 10:19 PM, vamsi varma tv.vamsikris...@gmail.comwrote: dear friends, i need some help.. i need to cal the macro in another spreadsheet. But unfortunately that spreadsheet is locked. I dont know the name of the macro to cal. only option i hav is to click the macro button using VBA. can anyone help in this regard? it wil helpful to me. Thanks and regards, Vamsi -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ STDEV
http://www.techonthenet.com/excel/formulas/stdev.php http://www.techonthenet.com/excel/formulas/stdev.php http://www.excelfunctions.net/Excel-Stdev-Function.html http://www.excelfunctions.net/Excel-Stdev-Function.html http://support.microsoft.com/kb/826349 and F1 function button on you keyboard when you open your excel On Wed, Apr 20, 2011 at 6:07 PM, sudhir kumar bluecore...@gmail.com wrote: hi what is STDEV? please explain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Copy results from one workbook to another
assuming that the both workbooks are openned Sub CanSomeOneHelp() Dim RgDest As Range With Workbooks(Anodize.xls).Sheets(1) .Unprotect Set RgDest = .Cells(1).CurrentRegion End With Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1) Workbooks(Quote.xls).Sheets(1).Cells(1).CurrentRegion.Copy RgDest RgDest.Parent.Protect Application.CutCopyMode = False RgFrom.ClearContents End Sub On Tue, Apr 19, 2011 at 7:02 AM, Dick bobde...@yahoo.com wrote: Can someone help with the following: In one workbook named (Quote) I would like to copy the contents of Columns A and B to another workbook named (Anodize). Where the copied contents from (Quote) would be placed on the next available blank row in Sheet1 Columns A and B. I know how to place a Command button on the (Quote) workbook. I just don't know how to code it. Once command button is clicked: 1.)Copy contents from Column A and B from (Quote) Workbook Sheet1 2.)Clear contents from Column A and B from (Quote) Workbook Sheet1 3.)Unprotect Workbook from (Anodize) Sheet1 4.)Paste contents from Column A and B from (Quote) Workbook Sheet1 TO (Anodize) Sheet1 Column A and B NEXT AVAILABLE(EMPTY) ROW. 5.) Protect Workbook (Anodize) Sheet1 Thanks so much for any 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
Re: $$Excel-Macros$$ Copy results from one workbook to another
correction on the last command. Sub CanSomeOneHelp() Dim RgDest As Range With Workbooks(Anodize.xls).Sheets(1) .Unprotect Set RgDest = .Cells(1).CurrentRegion End With Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1) Workbooks(Quote.xls).Sheets(1).Cells(1).CurrentRegion.Copy RgDest RgDest.Parent.Protect Application.CutCopyMode = False *Workbooks(Quote.xls).Sheets(1).Cells(1).CurrentRegion.ClearContents* End Sub On Wed, Apr 20, 2011 at 7:51 AM, STDEV(i) setiyowati.d...@gmail.com wrote: assuming that the both workbooks are openned Sub CanSomeOneHelp() Dim RgDest As Range With Workbooks(Anodize.xls).Sheets(1) .Unprotect Set RgDest = .Cells(1).CurrentRegion End With Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1) Workbooks(Quote.xls).Sheets(1).Cells(1).CurrentRegion.Copy RgDest RgDest.Parent.Protect Application.CutCopyMode = False RgFrom.ClearContents End Sub On Tue, Apr 19, 2011 at 7:02 AM, Dick bobde...@yahoo.com wrote: Can someone help with the following: In one workbook named (Quote) I would like to copy the contents of Columns A and B to another workbook named (Anodize). Where the copied contents from (Quote) would be placed on the next available blank row in Sheet1 Columns A and B. I know how to place a Command button on the (Quote) workbook. I just don't know how to code it. Once command button is clicked: 1.)Copy contents from Column A and B from (Quote) Workbook Sheet1 2.)Clear contents from Column A and B from (Quote) Workbook Sheet1 3.)Unprotect Workbook from (Anodize) Sheet1 4.)Paste contents from Column A and B from (Quote) Workbook Sheet1 TO (Anodize) Sheet1 Column A and B NEXT AVAILABLE(EMPTY) ROW. 5.) Protect Workbook (Anodize) Sheet1 Thanks so much for any 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
Re: $$Excel-Macros$$ How to Separate Text and Nos in a sentence ?
try to use this UDF and see if it helps.. Function GetNumbers(S As String, Index As Integer) ' ' siti Vi / jakarta, 30-08-2009 ' Dim ArrN(), i%, tmp$, n%, t$, t2$ S = Trim(S) | For i% = 1 To Len(S) - 1 t$ = Mid(S, i%, 1) t2$ = Mid(S, i% + 1, 1) If InStr(1, 0123456789., t$) 0 Then If InStr(1, 0123456789., t2$) 0 Then tmp$ = tmp$ t$ Else tmp$ = tmp$ t$ If Len(tmp$) 0 Then n = n + 1 ReDim Preserve ArrN(1 To n) ArrN(n) = Val(tmp$) tmp$ = End If End If End If Next i% GetNumbers = ArrN(Index) End Function '- formula in worksheet =GetNumbers($B3,1) =GetNumbers($B3,2) =GetNumbers($B3,3) and so on On Tue, Apr 19, 2011 at 8:44 PM, karan kanuga karankan...@gmail.com wrote: Hi, Can any1 pls let me know how do i separate the text and nos in a given sentence. PFA the sheet where i need to separate the nos and text. 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 ctv_UDF_GetNumber (extract nos from text).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel checkbox macro
unlock all cells in column A instanciate a checkbox in cell A1 (CheckBox1) create a conditional formatting for B1:C1 put the thos vba code to Sheet1 Module Private Sub CheckBox1_Click() Me.Unprotect If CheckBox1 = False Then Range(B1:C1).Locked = True Else Range(B1:C1).Locked = False End If Me.Protect End Sub (please dfind attached sample on workbook - sheet1) On Wed, Apr 20, 2011 at 4:07 AM, Tony O tonyrulesy...@yahoo.com wrote: I would like to add a checkbox in column A for an Excel spreadsheet. When the checkbox is checked, you can type in columns B, C and D. If it is unchecked, the cells are locked. Ideally, I would like columns B, C and D to be grey if they are unchecked. How would I go about doing this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 checkbox, cell locking n conditional formatting.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Fwd: SEARCH FUNCTION
plese check the attached workbook, and see if it helps. *ARRAY FORMULA* =small(if(left(C2:C29,len(F4))=F4,row(1:28)),row(1:28)) FORMULA =IF(ISERR($D6),,OFFSET(A$1,$D6,0)) On Mon, Apr 18, 2011 at 3:16 PM, C.G.Kumar kumar.bemlmum...@gmail.comwrote: PFA sample File. -- Forwarded message -- From: C.G.Kumar kumar.bemlmum...@gmail.com Date: Mon, Apr 18, 2011 at 1:45 PM Subject: SEARCH FUNCTION To: excel-macros@googlegroups.com Dear All, I intend to generate list based on search of Text String.Sample file attached. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel siti_About Filtering Formula (Medical Rate).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Password protect unprotect sheets using code
Sub ABCD() Sheets(MySheet).Unprotect yourpassword --- your program accessing to sheets(MySheet) Sheets(MySheet).Protect yourpassword End sub On Fri, Apr 15, 2011 at 6:51 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Experts, I want to password protect my work sheets, but the same time it is necessary to unprotect it for the running of macro so i have to give a code to unprotect them. how can I do it. Regards Rajesh kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ optionbuttons
You can check the GROUPNAME property of your OptionButton (active-X-Controls) A group / a set of OptionButtons shd have SAME GroupName [image: OptionButton GroupName.GIF] On Thu, Apr 14, 2011 at 8:04 AM, Dick bobde...@yahoo.com wrote: Why is some option buttons linked to each other and some not? I have 2 that will cancel each other out when the oposite one is selected. Then I have 4 others that do the same but are not affected by the first 2. Thanks for any 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 OptionButton GroupName.GIF
Re: $$Excel-Macros$$ Excel formula help
try this formula and see if it helps =SUM(OFFSET($D8:$I8,0,0,1,MONTH(TODAY( On Wed, Apr 13, 2011 at 12:23 AM, vinod rao vinod.ma...@gmail.com wrote: Hi, I need a formula adding each month one column. Attached file has a details. Regards, Vin -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_Dynamic_Sum_By_Month.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ querry
_xlfn IS http://office.microsoft.com/en-us/excel-help/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-HA010204569.aspx On Sat, Apr 9, 2011 at 11:02 PM, Dhananjay Pinjan dppin...@gmail.comwrote: Pl. explain what is *=_xlfn*.??? Is it User Defined function or what else? Regards, Dhanajay On Sat, Apr 9, 2011 at 9:22 PM, ashish koul koul.ash...@gmail.com wrote: sumifs function is avaliable in excel 2007 onwards check out the link below http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx On Sat, Apr 9, 2011 at 7:19 PM, Dhananjay Pinjan dppin...@gmail.comwrote: Dear Ashish, Pl. explain what is *=_xlfn.* SUMIFS($G:$G,$M:$M,$P6,$F:$F,$O6,$D:$D,Q$5) Regards, Dhananjay On Sat, Apr 9, 2011 at 4:07 PM, ashish koul koul.ash...@gmail.comwrote: see if it helps On Fri, Apr 8, 2011 at 7:04 PM, Nemi Gandhi nemigan...@gmail.comwrote: Please reply. -- Nemi Gandhi 98204 92963 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Compare strings in cells, return differences
*=TextDif(A2,B2)* TextDif is an UDF, like this: Function TextDif(S1 As String, S2 As String) As String Dim Arr1, Arr2 Dim n As Integer, i As Integer, t As String Arr1 = Split(S1, ,): Arr2 = Split(S2, ,) For i = LBound(Arr2) To UBound(Arr2) For n = LBound(Arr1) To UBound(Arr1) If Arr1(n) = Arr2(i) Then Arr2(i) = Next n Next i For i = LBound(Arr2) To UBound(Arr2) If Len(Arr2(i)) 0 Then t = t Arr2(i) , Next i TextDif = t If Len(t) 0 Then TextDif = Left(t, Len(t) - 1) End Function On Fri, Apr 8, 2011 at 4:46 AM, SHC stuart.hallcoo...@googlemail.comwrote: I'd like a function script that compares the strings from two cells, and returns, in a third cell, the parts of the 2nd string that do not match parts of the 1st string (I don't need to return the parts of 1st string that do not match parts of the 2nd string). Examples: Cell1: Chris Cell2: Carl,Chris,Peter Result in Cell3: Carl,Peter Cell1: Chris,Carl Cell2: Carl,Peter,Chris Result in Cell3: Peter Cell1: Chris,Dave Cell2: Carl,Chris,Peter Result: Carl,Peter Cell1: Chris,Dave,Peter,Carl Cell2: Dave,Chris,Carl Cell3 is empty Note, parts of the strings (separated by commas) will not necessarily be in the same order. Any help would be most appreciated 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 ctv_TextDif about Chris and Peter.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel help
i think mr. bhanu needs 000999 tobe 999 not 00999 On Mon, Apr 4, 2011 at 11:45 AM, §»VIPER«§ viper@gmail.com wrote: if your data in a1 type the below formula on anywhere in the same worksheet =IF(AND(ISTEXT(A1),LEFT(A1,1)=0),RIGHT(A1,LEN(A1)-1),A1) -- *Thanks Regards Thamu * -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Excel help
run this macro Sub RemoveAllZeros() Dim xCell As Range For Each xCell In ActiveSheet.UsedRange xCell = Replace(xCell, 0, ) Next End Sub since you ask for REMOVING ZERO IN EVERY CELL 1500 will be 15 2100500 will be 215 and so on On Sun, Apr 3, 2011 at 9:55 PM, bhanu prakash bhanu...@gmail.com wrote: Hi - need it help excel. Removing zeros in alpha numeric data, ex ,,07855a need help in removing zeros in every cell. Regards ,- bhanu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Excel help
then the macro should be modified like this Sub RemoveLeadingZeros() Dim xCell As Range, i As Integer, s As String For Each xCell In ActiveSheet.UsedRange s = CStr(xCell) For i = 1 To Len(xCell) If Mid(xCell, i, 1) = 0 Then s = Right(s, Len(s) - 1) Else Exit For End If Next i xCell = s Next xCell End Sub On Mon, Apr 4, 2011 at 7:34 AM, bhanu prakash bhanu...@gmail.com wrote: Guys thanks a ton for responses ,i need to remove only first letter in cell if it is zero in alpha numeric data - regards - bhanu On 04-Apr-2011 3:45 AM, STDEV(i) setiyowati.d...@gmail.com wrote: run this macro Sub RemoveAllZeros() Dim xCell As Range For Each xCell In ActiveSheet.UsedRange xCell = Replace(xCell, 0, ) Next End Sub since you ask for REMOVING ZERO IN EVERY CELL 1500 will be 15 2100500 will be 215 and so on -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Compare sheets
CompareWorksheetsAdd-in.xla !!! It is a usefull tool / add-in .. Thank you Mr Ashish best regards, STDEV(i) On Fri, Apr 1, 2011 at 12:21 AM, ashish koul koul.ash...@gmail.com wrote: try this On Thu, Mar 31, 2011 at 8:37 PM, hanumant shinde hanumant_5...@yahoo.co.in wrote: Hi Guys, is it a way where i can compare 2 sheets. lets say sheet1 and sheet2. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 : Formula Required
so... make it simplest =IF((B2=7/24)*(B2=*21*/24),NO,YES) On Thu, Mar 31, 2011 at 12:23 AM, JsinSk jsin...@gmail.com wrote: On my response I screwed up the 24 hour time format! Should be 21:00 instead of 17:00. On Mar 30, 1:17 pm, STDEV(i) setiyowati.d...@gmail.com wrote: Try this formula *=IF((B2=7/24)*(B2=9/24),NO,YES)* On Wed, Mar 30, 2011 at 6:53 PM, zinknax.zin...@gmail.com wrote: Hi All, I need formula to update column C, value if the time is between 7:00 AM to 9:00 PM YES else NO Find attached for the same. Column AColumn BColumn C DateTime (MST) 7:00 AM 9:00 PM 3/28/2011 6:26 AM YES IF LESS THAN 7:00:00 AM THEN YES 3/28/2011 6:47 AM YES IF GREATER THAN 9:00:00 PM THEN YES 3/28/2011 6:51 AM YES Between 7:00 Am to 9:00 Pm, Cell value NO 3/28/2011 6:59 AM YES 3/28/2011 7:13 AM NO 3/28/2011 7:32 AM NO 3/28/2011 7:45 AM NO 3/28/2011 7:46 AM NO Thanks, Mayur -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Calculating An Average Score from Multiple Tabs
assuming your data (one column range) are in A1:A100
and there are some 0 (blank / zero) value in these column..
try this *Array Formula*
=MIN(IF(A1:A1000,A1:A100))
On Wed, Mar 30, 2011 at 3:39 AM, John A. Smith johnasmit...@gmail.comwrote:
Thank you very much. It works great. One other question please; I have a
column with all formula's and I am looking for the smallest number in it by
using =MIN(range). I get 0 (zero) because the formula is
=IF(DY9=GONE,,D9). DY9 is an automatic status column that shows GONE
if a ship date is filled out in a previous column. D9 is the value of the
shipment yet to ship. =MAX(range) works but it senses the zero if it is
gone so the =MIN(range) doesn't.
Thank you for your kind teaching help.
John
On Tue, Mar 29, 2011 at 4:17 PM, STDEV(i) setiyowati.d...@gmail.comwrote:
Please try, and check if it helps
=IF(COUNT('Dept A:Dept C'!B3)=0,,AVERAGE('Dept A:Dept C'!B3))
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To 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 : Formula Required
Try this formula =IF((B2=7/24)*(B2=9/24),NO,YES) On Wed, Mar 30, 2011 at 6:53 PM, zinknax.zin...@gmail.com wrote: Hi All, I need formula to update column C, value if the time is between 7:00 AM to 9:00 PM YES else NO Find attached for the same. Column AColumn BColumn C DateTime (MST) 7:00 AM 9:00 PM 3/28/2011 6:26 AM YES IF LESS THAN 7:00:00 AM THEN YES 3/28/2011 6:47 AM YES IF GREATER THAN 9:00:00 PM THEN YES 3/28/2011 6:51 AM YES Between 7:00 Am to 9:00 Pm, Cell value NO 3/28/2011 6:59 AM YES 3/28/2011 7:13 AM NO 3/28/2011 7:32 AM NO 3/28/2011 7:45 AM NO 3/28/2011 7:46 AM NO Thanks, Mayur -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 different work book in one Sheet
in excel 2003 use menu: Window New Window and then: menu Window Arrange On Wed, Mar 30, 2011 at 12:54 PM, manhar prajapati prajapati.man...@gmail.com wrote: Hi expert I have a software which show two diff. work sheet in one excel under different tab. If any one know that software please mail me the name. Regards Manhar Prajapati -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 in formula to find the top 10 values
your data in B columns are TEXT data, not time data / numbers data so you can pass into LARGE function please try this array formula =TEXT(LARGE(TIMEVALUE(RIGHT(B2:B886,8)),ROW(1:10)),[hhh]:mm:ss) array formula written on one_column (10 cells) at once !! On Mon, Mar 28, 2011 at 5:59 PM, Bhushan Sabbani bsabban...@gmail.com wrote: Dear All, I am facing the problem in the formula of the large to find the top 10. Here i am attaching the sample file for the reference. Thanks in advance. Warm 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
Re: $$Excel-Macros$$ How to convert a (blood pressure number 120/80) to a format that will allow averaging of total amount of blood pressure numbers
please try this Array formula =ROUND(AVERAGE(--LEFT(A1:A3,3)),0)/ROUND(AVERAGE(--RIGHT(A1:A3,2)),0) Array Formula needs to be entered with 3 keys : Ctrl + Shift, Enter ! On Mon, Mar 28, 2011 at 12:28 AM, Susan sunni...@gmail.com wrote: Hi experts! I have a project to do given to me by a doctor that has a series of blood pressure results (numbers) from various patients. And would like me to give him the average blood pressure result. How can I get Excel 2007 to convert the 120/80 into a format that will allow me to find averages, without breaking 120/80 into two different cells. My example file would look like this: In column A A1120/80 A2119/79 A3125/60 Average of A1:A2:A3 = 121/73 Thanking you much! -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: RE: $$Excel-Macros$$ discussexcel : Macro: Column Number to Alphabetical reference
Thank you Mr. Ayush.. Thank you Mr. Bonallack Best Regards siti On Sat, Mar 26, 2011 at 9:43 PM, Ayush jainayus...@gmail.com wrote: Really Awesome formula. Thanks Siti Vi. I have published this formula on the same page along with your name. Regards Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Select numbers date wise using a formula
please visit : http://www.cpearson.com/excel/ArrayFormulas.aspx http://www.cpearson.com/excel/ArrayFormulas.aspx http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx On Sat, Mar 26, 2011 at 9:18 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Dev Thank u very much for your quick reply, unfortunately I don't have any idea about array formulas. how can I learn it properly. Regards Rajesh Kainikkara On 3/25/11, STDEV(i) setiyowati.d...@gmail.com wrote: 1. formula HELPER ( creating list of RowIndex ) array formula in multi cells =IF(ISNUMBER($A5),OFFSET(BILL!$D$1,$A5,0),) 2 lookup the table based on RowIndex individual formula in a cell =IF(ISNUMBER($A5),OFFSET(BILL!$D$1,$A5,0),) On Fri, Mar 25, 2011 at 7:40 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi, How can I select bill numbers of a particular date and show it in ascending order using a formula.Kindly check the attachment . Regards Rajesh Kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Identify the room status by color code
no attachments in your mail/posting just for info Color Index can be detected by *(1) making and UDF (user Definded Function)* Public Function ColorIndex(Rng as range) as long ColorIndex = Rng.Interior.ColorIndex End Function in worksheet you just use this NEW FUNCTION =ColorIndex(B5) another way *(2) By Define Name using Macro Excel-4 function* Please check in the attached workbook, hope it helps On Sat, Mar 26, 2011 at 10:39 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: attachment is added now On 3/26/11, vijayajith VA vijayajith...@gmail.com wrote: Hai attachment is missing thanks On Sat, Mar 26, 2011 at 7:43 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi Experts How to understand the room status, by color code.pls check the attachment. Regards Rajesh Kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel ctv_ColorIndex detector.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Re: Identify the room status by color code
please find attached and see if it helps best regards siti Vi On Sat, Mar 26, 2011 at 9:15 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: On 3/26/11, Rajesh K R rajeshkainikk...@gmail.com wrote: Hi Experts How to understand the room status, by color code.pls check the attachment. Regards Rajesh Kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ discussexcel : Macro: Column Number to Alphabetical reference
Please try my UDF for the same Public Function ColumnLetter(N As Long) As String ColumnLetter = Replace(Replace(Cells(1, N).Address, $, ), 1, ) End Function On Fri, Mar 25, 2011 at 10:40 PM, Ayush jainayus...@gmail.com wrote: Hello everyone, Here is the UDF to convert a number into Alphabetical reference. If you pass 1 to the function, it will Return A. If you pass 26, It will return Z. If you pass 27 It will return AAand so on. http://www.discussexcel.com/learn-macros/vba-macros Let me know if you have better solution. Thanks and best regards Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help on TL Recital Snapshot
an *array formula* =IFERROR(INDIRECT(C3), ) is written in a range C5:H20 at once !! about array formula: http://www.cpearson.com/excel/ArrayFormulas.aspx http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx but before you do it, you have to define some NAME in sheet GROUP, * manually* e.g.: range B3:G17as SHARIFA range B18:G16 as NEELAM and so on On Mon, Mar 21, 2011 at 6:39 AM, Intelligent But Crazy secrets...@gmail.com wrote: Hi Noorain, This is a Very good Solution. But i am unable to edit this array. can you pls tell me if i want to use this same concept of array in any other file so how can do this. please give me some more detail about using array formula in excel. On Fri, Mar 18, 2011 at 10:51 AM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Lucky, Please see attached sheet.. Rgds, Noorain Ansari -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Remove text from a string
=LEFT(A1,FIND(@,A1,FIND(@,A1)+1)-1) or =MID(A2,1,FIND(@,A2,FIND(@,A2)+1)-1) of array formula =LEFT(A3,MAX((MID(A3,ROW(INDIRECT(1:LEN(A3))),1)=@)*ROW(INDIRECT(1:LEN(A3-1) On Fri, Mar 18, 2011 at 3:16 PM, Deepak Rawat deepakexce...@gmail.comwrote: Hi Masters, My query is to remove all the characters after the last @ from the text. Example: The Raw test is deepak@gmail.comkaml...@yahoo.com The final text i want is deepak@gmail.comkamlesh Regards, Deepak Rawat -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Round the decimal part of the numbers .5 or 1
=INT(I3)+CHOOSE(MATCH(I3-INT(I3),{0,0.32999,0.82999},1),0,0.5,1)
or
=IF(ROUND(MOD(I3,1),2)=0.83,ROUNDUP(I3,0),IF(ROUND(MOD(I3,1),2)=0.33,TRUNC(I3)+0.5,TRUNC(I3)))
and more..
On Fri, Mar 18, 2011 at 10:42 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
I want to round the value 16.33 as 16.5 and 16.83 as 17 how can I do
it with a formula.
Regards
Rajesh kainikkara
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
ctv_Special_Rounding.xls
Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ how to increse rows in excel 2007 from 65 k to more
if you open a blank workbook in excell 2007 the workbook is in compatibility mode save your workbook in *.XLSX format close your workbook, and open again ta da On Mon, Mar 7, 2011 at 5:36 PM, dpk dpk1...@gmail.com wrote: how to increse rows in excel 2007 from 65 k to more -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to short repeated name
you are talking about List of Unique Values in Excel 2007 you can use REMOVE DUPLICATE in Excel 2003 use : Advanced Filter (Copy to another range / UniqRecords Only / Criteria = blank) or you can use a mega-formula see my example workbook at http://www.box.net/shared/o614r2gnrj On Tue, Mar 15, 2011 at 7:20 PM, sudhir kumar bluecore...@gmail.com wrote: Dear Experts i have list of reapted name. and i want a list which have name only one time if any new name add in list then it will add name. example List of Repeated name: A B H E A H A H I A B I List should be this type: A B H E I thanking you -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ code explanation please
excel is opening a text File the array are data for column index and length of string string to be converted to a cells Array(Array(1, 2), Array(2, 4)) text file contents: ABCDEFG converted to 2 columns column 1 = AB column 2 = CDEF If you use [Text To Column] ( of DATA menu) you will understand this code. CMIIW On Wed, Mar 16, 2011 at 12:04 AM, Skanda skanda.pokkun...@gmail.com wrote: What does the following code do?especially the array part! Application.StatusBar = Please Wait.Loading Trim(Prod) Data File Workbooks.OpenText FileName:=FileLoc, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=True, OtherChar:=~, FieldInfo:= _ Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _ Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), _ Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _ Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _ Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _ Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _ Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), _ Array(52, 2)) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ formula to add date in next column
let us be very patient, waiting for some one who give a formula that can write a Constat_Date into a cell On Mon, Mar 14, 2011 at 1:06 AM, Hems coolh...@gmail.com wrote: Hey STDEV(i), Thanks a lot but i dont want macro, i want formula for it. Let me know if is there any formula for this. Regards, Hemant On Sun, Mar 13, 2011 at 1:04 PM, STDEV(i) setiyowati.d...@gmail.comwrote: put this code into sheets' module Private Sub Worksheet_Change(ByVal Target As Range) ' siti Vi villager.g...@gmail.com villagera.g...@gmail.com ' jakarta, 11 mar 2011 ' If Target.Count = 1 Then If Target.Column = 1 Then If Target.Row 1 Then If Len(Target) 0 Then Target(1, 2) = Date End If End If End If End If End Sub On Sun, Mar 13, 2011 at 1:53 AM, Hems coolh...@gmail.com wrote: Dear All, I want today's date in next column (column B) on the day when first column ( column A) was updated. for Ex. if A3 was updated on 14th Mar'11 then B3 should display that date. If i open this file on 16th Mar'11 then also B3 should display 14th Mar'11 and not 16th Mar'11. Appreciate i get formula for this issue rather then macro. Thanks in advance Kind Regards, Hemant -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ Required Example
in the google search box, type sumproduct On Mon, Mar 14, 2011 at 2:50 PM, sudhir kumar bluecore...@gmail.com wrote: Hi Dear Members can any one sent me example of Sumproducts. thanking u -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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$$ formula to add date in next column
put this code into sheets' module Private Sub Worksheet_Change(ByVal Target As Range) ' siti Vi villagera.g...@gmail.com ' jakarta, 11 mar 2011 ' If Target.Count = 1 Then If Target.Column = 1 Then If Target.Row 1 Then If Len(Target) 0 Then Target(1, 2) = Date End If End If End If End If End Sub On Sun, Mar 13, 2011 at 1:53 AM, Hems coolh...@gmail.com wrote: Dear All, I want today's date in next column (column B) on the day when first column ( column A) was updated. for Ex. if A3 was updated on 14th Mar'11 then B3 should display that date. If i open this file on 16th Mar'11 then also B3 should display 14th Mar'11 and not 16th Mar'11. Appreciate i get formula for this issue rather then macro. Thanks in advance Kind Regards, Hemant -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To 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 ctv_auto date in column B.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
While condition '-- your macro Wend condition is an expression that evaluate to TRUE or FALSE Ex: yr data are in cell B4-down. i = 1 With Activesheet.Range(B4) While Len(.cell(i,1) 0 .cell(i,1).texttocolumns destination:=.cell(i,2), '-- etc -- i = i + 1 Wend End with On 2/10/11, Jorge Marques leote.w...@gmail.com wrote: Hi, i have a macro i´ve done, but it it has a problem, when it passes the data from cells A to various columns it stops no cell 20 in the sheet2 e the another stops at another cell number, i have the file attached here with the code below, can´t seem to discover the error, taking baby steps at this!i think the issue is on the if statement, but if i take it out, it does the macro well but indicates error in the end! Sub Macro2() ' ' Macro2 Macro ' ' Keyboard Shortcut: Ctrl+p ' Dim i As Double i = 1 While ActiveSheet.Select If ActiveCell.Range(A i) = Then Exit Sub Else Range(A i).Select Selection.TextToColumns Destination:=Range(A i), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :=;, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ TrailingMinusNumbers:=True i = i + 1 Wend End Sub Thank you very much for your 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
Re: $$Excel-Macros$$ how to get month Occurrence in no.
other stuff =MONTH(DATEVALUE(1 A1 2010)) On Tue, Dec 28, 2010 at 3:34 PM, siti Vi villager.g...@gmail.com wrote: If the word April or *another month name *is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE(1 A1 2010),M) On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young rohan.j...@gmail.com wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ vba to notepad
you can save your workbook (containing ONE worksheet) as XX.TEXT or as XX.CSV please note the notepad (a TEXT editor) is not contains multi cells like excel worksheet On Mon, Oct 18, 2010 at 2:07 PM, Krishna krishnaja...@gmail.com wrote: Dear Vijaykumar, Do you mean, an is inserted for all the data? *Can you please illustrate by an example*? Thanks, Krishna On Oct 18, 2:04 am, P.VIJAYKUMAR vijay.4...@gmail.com wrote: Dear krishna, It is possible to copy the contents of Excel to note pad.But, the only problem is the pasted content will not maintain the same format as in Excel. Regards, Vijaykumar On Mon, Oct 18, 2010 at 12:47 AM, Krishna krishnaja...@gmail.com wrote: How can we copy the contents from excel to notepad. For instance the range of cells A1:B5 to notepad? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
