Re: $$Excel-Macros$$ Stock position - Reg
Hi, Find the attachment... On Sun, Apr 15, 2012 at 10:27 AM, jmothilal gjmothi...@gmail.com wrote: PURCHASE / SALES ITEM QTY STOCK P LG DVD WRITER 1 1 P LG DVD WRITER 5 6 S LG DVD WRITER 1 5 P LG DVD WRITER 1 6 P LG DVD WRITER 10 16 S LG DVD WRITER 4 12 -- FORUM RULES (986+ 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 Test.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Stock position - Reg
I want to find different items sums PURCHASE / SALES ITEM QTY STOCK P LG DVD WRITER 4 4 P LG DVD WRITER 5 9 S LG DVD WRITER 1 8 P LG DVD WRITER 1 9 P LG DVD WRITER 10 19 S LG DVD WRITER 4 15 p 512 MB DDR RAM 1 1 p 18.5 Monitor 1 1 p 500 GB Hard disk 1 1 S 512 MB DDR RAM 1 0 S 18.5 Monitor 1 0 S 500 GB Hard disk 1 0 Thanks with On Sun, Apr 15, 2012 at 12:11 PM, Maries talk2mar...@gmail.com wrote: Hi, Find the attachment... On Sun, Apr 15, 2012 at 10:27 AM, jmothilal gjmothi...@gmail.com wrote: PURCHASE / SALES ITEM QTY STOCK P LG DVD WRITER 1 1 P LG DVD WRITER 5 6 S LG DVD WRITER 1 5 P LG DVD WRITER 1 6 P LG DVD WRITER 10 16 S LG DVD WRITER 4 12 -- FORUM RULES (986+ 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 -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ 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$$ Stock position - Reg
Hello Mothilal, See the attached. if you are on XL 2007 or later use SUMIFS ___ HTH, Haseeb -- FORUM RULES (986+ 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 Stock.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Most helpful Member - March 12- Don Guillett
Congratulation Don. Very happy to see your name on this thread. Regard, Haseeb -- FORUM RULES (986+ 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$$ Stock position - Reg
Thanks it working . also i find this formula =SUMPRODUCT(--(($A$2:A2=P)*($B$2:$B2=B2))*$C$2:C2)-SUMPRODUCT(--(($A$2:A2=S)*($B$2:$B2=B2))*$C$2:C2) On Sun, Apr 15, 2012 at 2:16 PM, Haseeb A haseeb.avarak...@gmail.comwrote: Hello Mothilal, See the attached. if you are on XL 2007 or later use SUMIFS ___ HTH, Haseeb -- FORUM RULES (986+ 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 -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ 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$$ Stock position - Reg
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={P,S}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ 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$$ Stock position - Reg
Thanks i am updating
Mothilal
On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A haseeb.avarak...@gmail.comwrote:
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={P,S}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ 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
--
*J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex
:Otteri, Vellore-2*
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ function call return parameter always zero
I assume this has to do with the assignment being to a variable that is passed into the function, but if so is there something more elegant than using a temporary copy for the maxrow value passed in or making it global? The function needs to know the value of maxrow on entry and needs to increment it within the function. The calling procedure needs to know the new value. maxrow in the assignment below always returns zero though its value in the function just before the exit is 2 as it should be. All 3 of the input parameters are defined in the calling procedure. maxrow = FillResults(entryval, maxrow, count) -- FORUM RULES (986+ 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$$ function call return parameter always zero
Never mind. Too late to be trying to debug code. Left out the actual assignment FillResults = x about which VBA is apparently quite picky. On Sun, Apr 15, 2012 at 2:05 AM, tangledweb domainqu...@gmail.com wrote: I assume this has to do with the assignment being to a variable that is passed into the function, but if so is there something more elegant than using a temporary copy for the maxrow value passed in or making it global? The function needs to know the value of maxrow on entry and needs to increment it within the function. The calling procedure needs to know the new value. maxrow in the assignment below always returns zero though its value in the function just before the exit is 2 as it should be. All 3 of the input parameters are defined in the calling procedure. maxrow = FillResults(entryval, maxrow, count) -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Quarter Classification
Hello Nikhil,
This will give the current quarter.
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,)
This will give the Next Quarter
=TEXT(LOOKUP(MONTH(A1)+3,{1,4,7,10,13})*29,)
___
HTH, Haseeb
--
FORUM RULES (986+ 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$$ Why is usedrange here returning the entire spreadsheet?
Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? http://blogs.office.com/b/microsoft-excel/archive/2008/10/03/what-is-the-fa stest-way-to-scan-a-large-range-in-excel.aspx It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ 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$$ If date in One then another cell to be unlock
I have pasted it in worksheet code module, but it is not working, pl tell me where I am wrong ? File is attached herewith. Regards On Sun, Apr 15, 2012 at 8:52 AM, Rajan_Verma rajanverma1...@gmail.comwrote: You need to paste it in worksheet code module. ** ** Rajan. ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Abdulgani Shaikh *Sent:* Apr/Sat/2012 02:03 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ If date in One then another cell to be unlock ** ** I have pasted this in Excel, but it is not working, pl.find attached file, where i am wrong ? Regards ** ** On Sat, Apr 14, 2012 at 12:38 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Hi, You can try this : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $C$3 Then Me.Unprotect If Target.Value = 0 Or Len(Target.Value) = 0 Then Range(E3).Locked = False Else Range(E3).Locked = True End If End If Me.Protect End Sub *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *ITP Abdulgani Shaikh *Sent:* Apr/Sat/2012 12:23 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ If date in One then another cell to be unlock Pl.find attached sample file On Sat, Apr 14, 2012 at 12:19 PM, ITP Abdulgani Shaikh itpabdulg...@gmail.com wrote: Dear Freinds, Please help me on following issue My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell C3 then Cell E3 should be auto unlock the cell. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. Regards -- FORUM RULES (986+ 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 -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com ** ** -- FORUM RULES (986+ 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)
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Thank. I will definitely read tomorrow. I am using an temp array copy of the raw data for reading values. I decided not to use an array for the output as I did not expect much gain. The program runs in seconds anyway as I just got my first I believe successful run moments ago. Still have to doublecheck values. Once I get it fully debugged would love to have your suggestions about how to make it more VBA correct. It is not that long. Probably not more than 150 lines or so of executable code and much of that is just the variable definitions. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Fwd: If data in One then another cell to be unlock
My sheet contains some tax formula and I want to keep these formula protected, if inadvertently they deleted or corrected, it will not give correct answer. If I am protecting the worksheet, it gives error at Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Please guide Thanks a lot for everything, that's what I want really. Regards On Sat, Apr 14, 2012 at 8:34 PM, dguillett1 dguille...@gmail.com wrote: On your protection, why not just leave unprotected... On your macros, see attached (Sent direct to OP) ‘--- Option Explicit Sub AddSheetSAS() ActiveWorkbook.Save Dim i As Long Dim s, k As String s = InputBox(Please Enter INITIALs of Employee as Sheet Name to be added) For i = 1 To Worksheets.Count k = Worksheets(i).Name If UCase(k) = UCase(s) Then MsgBox Sheet Already Exists Exit Sub End If Next i ActiveWorkbook.Unprotect Sheets(Master).Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = s Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Range(h13) = 12 Range(a2).Select ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub DeleteSheetSAS() Dim sht As String ActiveWorkbook.Unprotect On Error GoTo nosuchsheet sht = InputBox(Please Enter Sheet Name to be deleted) Application.DisplayAlerts = False Sheets(sht).Delete Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False Exit Sub nosuchsheet: MsgBox The sheet does not not exist Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub NextSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index + 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Sub PreviousSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index - 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* ITP Abdulgani Shaikh itpabdulg...@gmail.com *Sent:* Saturday, April 14, 2012 7:14 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock Dear Freinds, I am tax practitioner and needs to make Tax working of lot of salaried employees. I have made one template for the said purpose and needs your help on some issues. My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell E13 then Cell H13 should be auto unlock for entering data. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. I want to give following option in my Tax Working Sheet 01. ADD new sheet = This option is working correctly BUT, following buttons are not 02. DELETE sheet with option to choose sheet by entering sheet name, i have tried, but its not working. Current selected sheet goes deleted. 03. PREV = Option to go to previous sheet. (Please also considering that after first sheet, it must be stopped) 04. NEXT = Option to go to next sheet. (Please also considering that after last sheet, it must be stopped) I am attaching herewith file, please guide. Regards -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already
RE: $$Excel-Macros$$ Stock position - Reg
Stock = Opening Stock + Purchase - Sales , and there is no opening stock. So
P-S
=SUMIF(A2:D7,P,C2:C7)-SUMIF(A2:D7,S,C2:C7)
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of jmothilal
Sent: Apr/Sun/2012 02:31
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Stock position - Reg
Thanks i am updating
Mothilal
On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A haseeb.avarak...@gmail.com
wrote:
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={P,S}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ 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
--
J.Mothilal : Universal Computer Systems : # 16, Brindavan Complex :Otteri,
Vellore-2
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ 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$$ Fwd: If data in One then another cell to be unlock
Is your worksheet protected? Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Abdulgani Shaikh Sent: Apr/Sun/2012 03:14 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock My sheet contains some tax formula and I want to keep these formula protected, if inadvertently they deleted or corrected, it will not give correct answer. If I am protecting the worksheet, it gives error at Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Please guide Thanks a lot for everything, that's what I want really. Regards On Sat, Apr 14, 2012 at 8:34 PM, dguillett1 dguille...@gmail.com wrote: On your protection, why not just leave unprotected... On your macros, see attached (Sent direct to OP) '--- Option Explicit Sub AddSheetSAS() ActiveWorkbook.Save Dim i As Long Dim s, k As String s = InputBox(Please Enter INITIALs of Employee as Sheet Name to be added) For i = 1 To Worksheets.Count k = Worksheets(i).Name If UCase(k) = UCase(s) Then MsgBox Sheet Already Exists Exit Sub End If Next i ActiveWorkbook.Unprotect Sheets(Master).Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = s Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Range(h13) = 12 Range(a2).Select ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub DeleteSheetSAS() Dim sht As String ActiveWorkbook.Unprotect On Error GoTo nosuchsheet sht = InputBox(Please Enter Sheet Name to be deleted) Application.DisplayAlerts = False Sheets(sht).Delete Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False Exit Sub nosuchsheet: MsgBox The sheet does not not exist Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub NextSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index + 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Sub PreviousSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index - 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: ITP Abdulgani Shaikh mailto:itpabdulg...@gmail.com Sent: Saturday, April 14, 2012 7:14 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock Dear Freinds, I am tax practitioner and needs to make Tax working of lot of salaried employees. I have made one template for the said purpose and needs your help on some issues. My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell E13 then Cell H13 should be auto unlock for entering data. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. I want to give following option in my Tax Working Sheet 01. ADD new sheet = This option is working correctly BUT, following buttons are not 02. DELETE sheet with option to choose sheet by entering sheet name, i have tried, but its not working. Current selected sheet goes deleted. 03. PREV = Option to go to previous sheet. (Please also considering that after first sheet, it must be stopped) 04. NEXT = Option to go to next sheet. (Please also considering that after last sheet, it must be stopped) I am attaching herewith file, please guide. Regards -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Re: Quarter Classification
Hi Haseeb,
Sorry , answer is wrong.
i.e. Dt.01/05/2010 ( DD/MM/ ) ,then Next Quarter should be July , here
it's display april..
Nikhil
On Sun, Apr 15, 2012 at 2:57 PM, Haseeb A haseeb.avarak...@gmail.comwrote:
Hello Nikhil,
This will give the current quarter.
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,)
This will give the Next Quarter
=TEXT(LOOKUP(MONTH(A1)+3,{1,4,7,10,13})*29,)
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ 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$$ Most helpful Member - March 12- Don Guillett
Thanks to all who have responded or will. Someone must have know that it is my 76 th birthday today. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ayush Jain Sent: Saturday, April 14, 2012 3:36 AM To: excel-macros Subject: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett Dear members, Don Guillett has been selected as 'Most Helpful Member' for the month of March'12 He has been helping forum members from long time consistently and we are proud to have him in the forum. He is business degree holder from University of Texas and retired Regional Manager for ING. Don, Many Many Thanks for your great contribution to forum. Keep Posting !! Best regards, Ayush Jain Group Manager, Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Most helpful Member - March 12- Don Guillett
Happy Birthday Mr. Don, May God Bless you with Long Life Happy Future. At the same time enrich your knowledge with more talents. Regards, Muneer, CC... -- FORUM RULES (986+ 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$$ Most helpful Member - March 12- Don Guillett
Hurray Don. 76 ?wow Congrats and a very Happy birthday. Sent from my BlackBerry® smartphone from Airtel Ghana -Original Message- From: Mohammed Muneer mmun...@ccc.com.qa Sender: excel-macros@googlegroups.com Date: Sun, 15 Apr 2012 16:13:27 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett Happy Birthday Mr. Don, May God Bless you with Long Life Happy Future. At the same time enrich your knowledge with more talents. Regards, Muneer, CC... -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Quarter Classification
A shorter one =CHOOSE(CEILING(MONTH(A1)/3,1),April,July,October,January) Regards, Sam On Sat, Apr 14, 2012 at 6:19 PM, Nikhil Shah nikhil201...@gmail.com wrote: Hi Maries, Thanks for solving my problem. Nikhil On Sat, Apr 14, 2012 at 6:15 PM, dguillett1 dguille...@gmail.com wrote: You need to provide a file. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Nikhil Shah nikhil201...@gmail.com *Sent:* Saturday, April 14, 2012 2:38 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Quarter Classification Dear Friends, I want to make the whole year classified in to 4 quarters as January, April, July and October. As and when I give a date it should be grouping in to that particular quarter only,( the date may be random) and one more thing that the very next Quarter will be taken for the name printing and followed by next remaining quarters. Ex : [ 1 ]. If I give a date - 16/08/2009 (dd/mm/) the whole process will be like , Quarter grouping as 3rd Quarter as July Quarter and out put name will be printed as October ( the very next Quarter) then followed by Jan, April, and July. Ex : [ 2 ]. Date.15/12/2010 (dd/mm/) Quarter will be - October and the print will be January, April, July and October Ex : [ 3 ] . Date.22/03/2011 (dd/mm/) Quarter will be - January and the print will be April,July and October and January. Ex : [ 4 ]. Date.15/10/2008 (dd/mm/) Quarter will be - October and the print will be January, April,July and october I am using Excel 2003. I do want any Macro , I want Only Formula Awaiting Reply Nikhil Shah -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (986+ 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.
Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock
UN protect your worksheet manually or by code. TESTED fine on file presented. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Abdulgani Shaikh Sent: Sunday, April 15, 2012 4:43 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock My sheet contains some tax formula and I want to keep these formula protected, if inadvertently they deleted or corrected, it will not give correct answer. If I am protecting the worksheet, it gives error at Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Please guide Thanks a lot for everything, that's what I want really. Regards On Sat, Apr 14, 2012 at 8:34 PM, dguillett1 dguille...@gmail.com wrote: On your protection, why not just leave unprotected... On your macros, see attached (Sent direct to OP) ‘--- Option Explicit Sub AddSheetSAS() ActiveWorkbook.Save Dim i As Long Dim s, k As String s = InputBox(Please Enter INITIALs of Employee as Sheet Name to be added) For i = 1 To Worksheets.Count k = Worksheets(i).Name If UCase(k) = UCase(s) Then MsgBox Sheet Already Exists Exit Sub End If Next i ActiveWorkbook.Unprotect Sheets(Master).Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = s Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Range(h13) = 12 Range(a2).Select ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub DeleteSheetSAS() Dim sht As String ActiveWorkbook.Unprotect On Error GoTo nosuchsheet sht = InputBox(Please Enter Sheet Name to be deleted) Application.DisplayAlerts = False Sheets(sht).Delete Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False Exit Sub nosuchsheet: MsgBox The sheet does not not exist Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub NextSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index + 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Sub PreviousSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index - 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: ITP Abdulgani Shaikh Sent: Saturday, April 14, 2012 7:14 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock Dear Freinds, I am tax practitioner and needs to make Tax working of lot of salaried employees. I have made one template for the said purpose and needs your help on some issues. My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell E13 then Cell H13 should be auto unlock for entering data. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. I want to give following option in my Tax Working Sheet 01. ADD new sheet = This option is working correctly BUT, following buttons are not 02. DELETE sheet with option to choose sheet by entering sheet name, i have tried, but its not working. Current selected sheet goes deleted. 03. PREV = Option to go to previous sheet. (Please also considering that after first sheet, it must be stopped) 04. NEXT = Option to go to next sheet. (Please also considering that after last sheet, it must be stopped) I am attaching herewith file, please guide. Regards -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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,
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
I haven't paid much attention to this so.. If you are still having a problem with this you might consider using the FIND method to get the last value you want Send your file direct to me if desired with a complete explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Domain Admin Sent: Sunday, April 15, 2012 6:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ Quarter Classification
Hi sam, super Idea Nikhil Shah On Sun, Apr 15, 2012 at 7:01 PM, Sam Mathai Chacko samde...@gmail.comwrote: A shorter one =CHOOSE(CEILING(MONTH(A1)/3,1),April,July,October,January) Regards, Sam On Sat, Apr 14, 2012 at 6:19 PM, Nikhil Shah nikhil201...@gmail.comwrote: Hi Maries, Thanks for solving my problem. Nikhil On Sat, Apr 14, 2012 at 6:15 PM, dguillett1 dguille...@gmail.com wrote: You need to provide a file. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Nikhil Shah nikhil201...@gmail.com *Sent:* Saturday, April 14, 2012 2:38 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Quarter Classification Dear Friends, I want to make the whole year classified in to 4 quarters as January, April, July and October. As and when I give a date it should be grouping in to that particular quarter only,( the date may be random) and one more thing that the very next Quarter will be taken for the name printing and followed by next remaining quarters. Ex : [ 1 ]. If I give a date - 16/08/2009 (dd/mm/) the whole process will be like , Quarter grouping as 3rd Quarter as July Quarter and out put name will be printed as October ( the very next Quarter) then followed by Jan, April, and July. Ex : [ 2 ]. Date.15/12/2010 (dd/mm/) Quarter will be - October and the print will be January, April, July and October Ex : [ 3 ] . Date.22/03/2011 (dd/mm/) Quarter will be - January and the print will be April,July and October and January. Ex : [ 4 ]. Date.15/10/2008 (dd/mm/) Quarter will be - October and the print will be January, April,July and october I am using Excel 2003. I do want any Macro , I want Only Formula Awaiting Reply Nikhil Shah -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (986+ 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 :
Re: $$Excel-Macros$$ If date in One then another cell to be unlock
Pl.reply On Sat, Apr 14, 2012 at 2:02 PM, Abdulgani Shaikh itpabdulg...@gmail.comwrote: I have pasted this in Excel, but it is not working, pl.find attached file, where i am wrong ? Regards On Sat, Apr 14, 2012 at 12:38 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi, You can try this : ** ** Private Sub Worksheet_Change(ByVal Target As Range) ** ** If Target.Address = $C$3 Then Me.Unprotect If Target.Value = 0 Or Len(Target.Value) = 0 Then Range(E3).Locked = False Else Range(E3).Locked = True End If End If Me.Protect End Sub ** ** ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *ITP Abdulgani Shaikh *Sent:* Apr/Sat/2012 12:23 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ If date in One then another cell to be unlock ** ** Pl.find attached sample file On Sat, Apr 14, 2012 at 12:19 PM, ITP Abdulgani Shaikh itpabdulg...@gmail.com wrote: Dear Freinds, ** ** Please help me on following issue ** ** My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell C3 then Cell E3 should be auto unlock the cell. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. ** ** Regards ** ** -- FORUM RULES (986+ 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 ** ** -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ If date in One then another cell to be unlock
If your request is for a reply, I did reply. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Abdulgani Shaikh Sent: Saturday, April 14, 2012 5:18 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ If date in One then another cell to be unlock Pl.reply On Sat, Apr 14, 2012 at 2:02 PM, Abdulgani Shaikh itpabdulg...@gmail.com wrote: I have pasted this in Excel, but it is not working, pl.find attached file, where i am wrong ? Regards On Sat, Apr 14, 2012 at 12:38 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Hi, You can try this : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $C$3 Then Me.Unprotect If Target.Value = 0 Or Len(Target.Value) = 0 Then Range(E3).Locked = False Else Range(E3).Locked = True End If End If Me.Protect End Sub From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of ITP Abdulgani Shaikh Sent: Apr/Sat/2012 12:23 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ If date in One then another cell to be unlock Pl.find attached sample file On Sat, Apr 14, 2012 at 12:19 PM, ITP Abdulgani Shaikh itpabdulg...@gmail.com wrote: Dear Freinds, Please help me on following issue My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell C3 then Cell E3 should be auto unlock the cell. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. Regards -- FORUM RULES (986+ 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 -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ Re: Quarter Classification
Nikhil, I got answer July instead of April. There were two formulas in my last reply. First one for Current Quarter, 2nd one for Next Quarter. See the attached. ___ HTH, Haseeb -- FORUM RULES (986+ 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 Quarter.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Pivot table
i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ 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 Book4.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Thanks but all handled and the solution was posted. On Sun, Apr 15, 2012 at 7:36 AM, dguillett1 dguille...@gmail.com wrote: I haven't paid much attention to this so.. If you are still having a problem with this you might consider using the FIND method to get the last value you want Send your file direct to me if desired with a complete explanation. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Domain Admin Sent: Sunday, April 15, 2012 6:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
RE: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett
Perfect timing! From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Sunday, April 15, 2012 6:07 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett Thanks to all who have responded or will. Someone must have know that it is my 76 th birthday today. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Ayush Jain mailto:jainayus...@gmail.com Sent: Saturday, April 14, 2012 3:36 AM To: excel-macros mailto:excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett Dear members, Don Guillett has been selected as 'Most Helpful Member' for the month of March'12 He has been helping forum members from long time consistently and we are proud to have him in the forum. He is business degree holder from University of Texas and retired Regional Manager for ING. Don, Many Many Thanks for your great contribution to forum. Keep Posting !! Best regards, Ayush Jain Group Manager, Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Pivot table
Hi, Try this formula, =B4/OFFSET($A$3,COUNTA(A:A)-1,2) Regards, MARIES. On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee lee.sar...@gmail.com wrote: i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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 Pivot Calculation.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Pivot table
thanks it is good but one change required--- when i add more records into my base sheet , and then refresh the pivot, that column % does not get updated automatically;; also when i delete records, that column shows up 0 even though there are no records to left On Sun, Apr 15, 2012 at 3:41 PM, Maries talk2mar...@gmail.com wrote: Hi, Try this formula, =B4/OFFSET($A$3,COUNTA(A:A)-1,2) Regards, MARIES. On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee lee.sar...@gmail.com wrote: i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Why is usedrange here returning the entire spreadsheet?
Hence my confusion. Here is the code I am currently using. Sounds like you are saying the SET should cause it to fail. But it only works if I use the SET. Otherwise it will not compile. ' First let's copy everything we need into an array for efficiency With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.count, EContango)) End With On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff a...@lovetour.info wrote: Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? I assume he's contrasting with the Use a range object routine where DataRange was a Range object, and thus needed Set for it's assignment. In the Use a variant type variable routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only. Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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. --
Re: $$Excel-Macros$$ Pivot table
Hi, In that case, Dynamic range is good solution. I have used Name Range rng refers as*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) * Regards, MARIES.* * On Mon, Apr 16, 2012 at 12:14 AM, Sara Lee lee.sar...@gmail.com wrote: thanks it is good but one change required--- when i add more records into my base sheet , and then refresh the pivot, that column % does not get updated automatically;; also when i delete records, that column shows up 0 even though there are no records to left On Sun, Apr 15, 2012 at 3:41 PM, Maries talk2mar...@gmail.com wrote: Hi, Try this formula, =B4/OFFSET($A$3,COUNTA(A:A)-1,2) Regards, MARIES. On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee lee.sar...@gmail.com wrote: i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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 Pivot Calculation.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Pivot table
Hi So do i have to copy and paste this formula? On Sun, Apr 15, 2012 at 4:32 PM, Maries talk2mar...@gmail.com wrote: Hi, In that case, Dynamic range is good solution. I have used Name Range rng refers as*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) * Regards, MARIES. * * On Mon, Apr 16, 2012 at 12:14 AM, Sara Lee lee.sar...@gmail.com wrote: thanks it is good but one change required--- when i add more records into my base sheet , and then refresh the pivot, that column % does not get updated automatically;; also when i delete records, that column shows up 0 even though there are no records to left On Sun, Apr 15, 2012 at 3:41 PM, Maries talk2mar...@gmail.com wrote: Hi, Try this formula, =B4/OFFSET($A$3,COUNTA(A:A)-1,2) Regards, MARIES. On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee lee.sar...@gmail.com wrote: i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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
$$Excel-Macros$$ Find differences in two worksheets.
hi group, I had a routine question regarding the comparision of worksheets.I had searched the internet regarding the differences between two worksheets which have similiar columns headings.I want to find out the changes/differences between the two worksheets. In depth, I had a master worksheet with columns of EmpNo,Emp Name,BASIC,HRA,DA other allowances.I also get worksheet with the similiar headings every month from the HR dept to check the differences like new joinees or Resigned employees with their salary structure.what i want is to know how to find out the differences / changes between the twoI want the changes in every column in new worksheets. I tried using the pivot table.is my approach the easiest or accurate one.i would also like to know whether the same can be done using formulas or VBA. Pls Help. Thanks Regards. -- FORUM RULES (986+ 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$$ Most helpful Member - March 12- Don Guillett
On Saturday, April 14, 2012, Ayush Jain jainayus...@gmail.com wrote: Dear members, Don Guillett has been selected as 'Most Helpful Member' for the month of March'12 He has been helping forum members from long time consistently and we are proud to have him in the forum. He is business degree holder from University of Texas and retired Regional Manager for ING. Don, Many Many Thanks for your great contribution to forum. Keep Posting !! Best regards, Ayush Jain Group Manager, Microsoft MVP -- FORUM RULES (986+ 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 -- With Regards SUNNY AGARWAL -- FORUM RULES (986+ 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$$ Most helpful Member - March 12- Don Guillett
Happy Birthday Mr. Don, May God Bless you with Long Life Peaceful Future Somnath Khadilkar On 4/14/12, Ayush Jain jainayus...@gmail.com wrote: Dear members, Don Guillett has been selected as 'Most Helpful Member' for the month of March'12 He has been helping forum members from long time consistently and we are proud to have him in the forum. He is business degree holder from University of Texas and retired Regional Manager for ING. Don, Many Many Thanks for your great contribution to forum. Keep Posting !! Best regards, Ayush Jain Group Manager, Microsoft MVP -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Why is usedrange here returning the entire spreadsheet?
Your code is fine except it doesn't do what the comment says it does :) -- unless there is following code to finish the job. To copy to an array, 1. Declare a Variant variable to hold the array 2. Assign the Value2 or Value property of a range to the Variant (Don't use Set when you do so) One way to change your code to work is this, which just adds a couple lines to your existing code: Dim tmprange As Range Dim DataArray As Variant ' First, let's define the range to use With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With ' Second, let's copy everything we need into an array for efficiency DataArray = tmprange.Value2 Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Hence my confusion. Here is the code I am currently using. Sounds like you are saying the SET should cause it to fail. But it only works if I use the SET. Otherwise it will not compile. ' First let's copy everything we need into an array for efficiency With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.count, EContango)) End With On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff mailto:a...@lovetour.info a...@lovetour.info wrote: Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? I assume he's contrasting with the Use a range object routine where DataRange was a Range object, and thus needed Set for it's assignment. In the Use a variant type variable routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only. Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -Original Message- From: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff mailto:a...@lovetour.info a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
yeah all the rest was there. Still confused though. He supposedly does without SET what I cannot do unless I use SET. On Sun, Apr 15, 2012 at 7:25 PM, Asa Rossoff a...@lovetour.info wrote: Your code is fine except it doesn't do what the comment says it does :) -- unless there is following code to finish the job. To copy to an array, 1. Declare a Variant variable to hold the array 2. Assign the Value2 or Value property of a range to the Variant (Don't use Set when you do so) One way to change your code to work is this, which just adds a couple lines to your existing code: Dim tmprange As Range Dim DataArray As Variant ' First, let's define the range to use With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With ' Second, let's copy everything we need into an array for efficiency DataArray = tmprange.Value2 Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Hence my confusion. Here is the code I am currently using. Sounds like you are saying the SET should cause it to fail. But it only works if I use the SET. Otherwise it will not compile. ' First let's copy everything we need into an array for efficiency With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.count, EContango)) End With On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff a...@lovetour.info wrote: Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? I assume he's contrasting with the Use a range object routine where DataRange was a Range object, and thus needed Set for it's assignment. In the Use a variant type variable routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only. Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? The msgbox example seems to have an error but with other reading I think maybe but not certain I got it. I got the array version of the rounding to work. The evaluate version is slightly faster but the array version way faster than the for each or for index versions. -- FORUM RULES (986+ 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
RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Does it help to say that: With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With DataArray = tmprange.Value2 is equivalent to: With Sheets(RawData) DataArray = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)).Value2 End With ? -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 7:49 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? yeah all the rest was there. Still confused though. He supposedly does without SET what I cannot do unless I use SET. On Sun, Apr 15, 2012 at 7:25 PM, Asa Rossoff a...@lovetour.info wrote: Your code is fine except it doesn't do what the comment says it does :) -- unless there is following code to finish the job. To copy to an array, 1. Declare a Variant variable to hold the array 2. Assign the Value2 or Value property of a range to the Variant (Don't use Set when you do so) One way to change your code to work is this, which just adds a couple lines to your existing code: Dim tmprange As Range Dim DataArray As Variant ' First, let's define the range to use With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With ' Second, let's copy everything we need into an array for efficiency DataArray = tmprange.Value2 Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Hence my confusion. Here is the code I am currently using. Sounds like you are saying the SET should cause it to fail. But it only works if I use the SET. Otherwise it will not compile. ' First let's copy everything we need into an array for efficiency With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.count, EContango)) End With On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff a...@lovetour.info wrote: Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? I assume he's contrasting with the Use a range object routine where DataRange was a Range object, and thus needed Set for it's assignment. In the Use a variant type variable routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only. Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet?
Ah, all in one step. Yes thanks. On Sun, Apr 15, 2012 at 7:54 PM, Asa Rossoff a...@lovetour.info wrote: Does it help to say that: With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With DataArray = tmprange.Value2 is equivalent to: With Sheets(RawData) DataArray = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)).Value2 End With ? -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 7:49 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? yeah all the rest was there. Still confused though. He supposedly does without SET what I cannot do unless I use SET. On Sun, Apr 15, 2012 at 7:25 PM, Asa Rossoff a...@lovetour.info wrote: Your code is fine except it doesn't do what the comment says it does :) -- unless there is following code to finish the job. To copy to an array, 1. Declare a Variant variable to hold the array 2. Assign the Value2 or Value property of a range to the Variant (Don't use Set when you do so) One way to change your code to work is this, which just adds a couple lines to your existing code: Dim tmprange As Range Dim DataArray As Variant ' First, let's define the range to use With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count, EContango)) End With ' Second, let's copy everything we need into an array for efficiency DataArray = tmprange.Value2 Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 1:18 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Hence my confusion. Here is the code I am currently using. Sounds like you are saying the SET should cause it to fail. But it only works if I use the SET. Otherwise it will not compile. ' First let's copy everything we need into an array for efficiency With Sheets(RawData) Set tmprange = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.count, EContango)) End With On Sun, Apr 15, 2012 at 1:02 PM, Asa Rossoff a...@lovetour.info wrote: Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? I assume he's contrasting with the Use a range object routine where DataRange was a Range object, and thus needed Set for it's assignment. In the Use a variant type variable routine, DataRange is as you know being used to store an array of values, thus Set wouldn't work. -- Set is for objects only. Agreed, don't worry if you're satisfied with the performance. You can always massage things later, at your leisure, if desired. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Sunday, April 15, 2012 4:05 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? Well on the read side we definitely match except he makes the point of saying he is not using SET. Why is that? What is the difference? It does indicate that if performance was any issue I should create an output array and fill it then copy to the Results sheet range but seeing as the program takes less than 3 seconds to run even with the output cell fill real time active and I can at worst have 60X this much data I doubt I will worry about it. I am curious if it is making a noticable difference on the read side but not curious enough to rewrite it just to find out. I suspect the effect is small at this scale though. 4K X 8 colums vs 100K X 50 so just .6% of the data volume he tests. On Sun, Apr 15, 2012 at 2:27 AM, Asa Rossoff a...@lovetour.info wrote: Just came across a simple example using an array to manipulate cell values, then put only values that got changed back to the worksheet. It also has a range/index equivalent sample, and a range/selection/offset example (this is often the sort of thing first-time macro writers end up with after modifying recorded macros). The read and write speed of the three methods are compared. Excel Blog: What is the fastest way to scan a large range in Excel? It doesn't cover use of Evaluate to perform operations on a range without a VBA loop, but it's a good article with straightforward examples. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Wednesday, April 11, 2012 10:49 AM To:
Re: $$Excel-Macros$$ Re: Quarter Classification
Hi Hasseb, Now answer is right , i.e. Date 01/05/2010 ( DD/MM/ ) Format , Currently it's showing Curr.Qtr = April ; Next Qtr = July ..it is ok After July , Now I want Next Qtr = October ; Next Qtr = January Nikhil On Sun, Apr 15, 2012 at 11:07 PM, Haseeb A haseeb.avarak...@gmail.comwrote: Nikhil, I got answer July instead of April. There were two formulas in my last reply. First one for Current Quarter, 2nd one for Next Quarter. See the attached. ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ 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$$ Fwd: If data in One then another cell to be unlock
Yes my file is protected and I want to keep it protected, because though file is created by me, users are different and any change in data/ formula will differ its result. Please guide. On Sun, Apr 15, 2012 at 4:24 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Is your worksheet protected? ** ** Rajan. ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Abdulgani Shaikh *Sent:* Apr/Sun/2012 03:14 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock ** ** My sheet contains some tax formula and I want to keep these formula protected, if inadvertently they deleted or corrected, it will not give correct answer. ** ** If I am protecting the worksheet, it gives error at Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents ** ** Please guide ** ** Thanks a lot for everything, that's what I want really. ** ** Regards On Sat, Apr 14, 2012 at 8:34 PM, dguillett1 dguille...@gmail.com wrote:* *** On your protection, why not just leave unprotected... On your macros, see attached (Sent direct to OP) ‘--- Option Explicit Sub AddSheetSAS() ActiveWorkbook.Save Dim i As Long Dim s, k As String s = InputBox(Please Enter INITIALs of Employee as Sheet Name to be added) For i = 1 To Worksheets.Count k = Worksheets(i).Name If UCase(k) = UCase(s) Then MsgBox Sheet Already Exists Exit Sub End If Next i ActiveWorkbook.Unprotect Sheets(Master).Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = s Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Range(h13) = 12 Range(a2).Select ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub DeleteSheetSAS() Dim sht As String ActiveWorkbook.Unprotect On Error GoTo nosuchsheet sht = InputBox(Please Enter Sheet Name to be deleted) Application.DisplayAlerts = False Sheets(sht).Delete Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False Exit Sub nosuchsheet: MsgBox The sheet does not not exist Application.DisplayAlerts = True ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Sub NextSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index + 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Sub PreviousSheetSAS() On Error Resume Next Sheets(ActiveSheet.Index - 1).Activate If Err.Number 0 Then Sheets(1).Activate End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* ITP Abdulgani Shaikh itpabdulg...@gmail.com *Sent:* Saturday, April 14, 2012 7:14 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock Dear Freinds, I am tax practitioner and needs to make Tax working of lot of salaried employees. I have made one template for the said purpose and needs your help on some issues. My worksheet is protected, out of all cells, some cells are unprotected for entering data. If I am entering data in Cell E13 then Cell H13 should be auto unlock for entering data. and if there is no data in Cell C3 or zero in C3 then Cell E3 should be auto locked. I want to give following option in my Tax Working Sheet 01. ADD new sheet = This option is working correctly BUT, following buttons are not 02. DELETE sheet with option to choose sheet by entering sheet name, i have tried, but its not working. Current selected sheet goes deleted. 03. PREV = Option to go to previous sheet. (Please also considering that after first sheet, it must be stopped) 04. NEXT = Option to go to next sheet. (Please also considering that after last sheet, it must be stopped) I am attaching herewith file, please guide. Regards -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- FORUM RULES (986+ 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
Re: $$Excel-Macros$$ Pivot table
Hi, To get the percentage, *Copy paste* or *Drag Down* the formula from * Sheet1-D4*. On Mon, Apr 16, 2012 at 1:10 AM, Sara Lee lee.sar...@gmail.com wrote: Hi So do i have to copy and paste this formula? On Sun, Apr 15, 2012 at 4:32 PM, Maries talk2mar...@gmail.com wrote: Hi, In that case, Dynamic range is good solution. I have used Name Range rng refers as*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) * Regards, MARIES. * * On Mon, Apr 16, 2012 at 12:14 AM, Sara Lee lee.sar...@gmail.com wrote: thanks it is good but one change required--- when i add more records into my base sheet , and then refresh the pivot, that column % does not get updated automatically;; also when i delete records, that column shows up 0 even though there are no records to left On Sun, Apr 15, 2012 at 3:41 PM, Maries talk2mar...@gmail.com wrote: Hi, Try this formula, =B4/OFFSET($A$3,COUNTA(A:A)-1,2) Regards, MARIES. On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee lee.sar...@gmail.comwrote: i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i have added a calculation column - right most column of the pivot... now when i delete the row of the data in sheet 1 say mumbai row, and then refresh the pivot... then the last column gets messed up showing div/0 error is therer any way to correct that last column formula in the pivot so that it updates itself when data is refresehed. thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for
Re: $$Excel-Macros$$ Stock position - Reg
Difference items need one more conditions
Mothilal
On Sun, Apr 15, 2012 at 4:19 PM, Rajan_Verma rajanverma1...@gmail.comwrote:
** **
Stock = Opening Stock + Purchase – Sales , and there is no opening stock.
So P-S
=SUMIF(A2:D7,P,C2:C7)-SUMIF(A2:D7,S,C2:C7)
** **
*From:* excel-macros@googlegroups.com [mailto:
excel-macros@googlegroups.com] *On Behalf Of *jmothilal
*Sent:* Apr/Sun/2012 02:31
*To:* excel-macros@googlegroups.com
*Subject:* Re: $$Excel-Macros$$ Stock position - Reg
** **
Thanks i am updating
Mothilal
On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A haseeb.avarak...@gmail.com
wrote:
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={P,S}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ 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
--
*J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex
:Otteri, Vellore-2*
** **
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ 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
--
*J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex
:Otteri, Vellore-2*
--
FORUM RULES (986+ 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
