Re: $$Excel-Macros$$ Learn Array formula's
Hi, Thanks alot. If u give any files with examples in excel or in ppt. it will help me to learn very easily. Thanks in advance. Thanks Muralidhar E On Wed, Aug 29, 2012 at 8:25 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Murlidhar, Some best sites for Array Function http://www.cpearson.com/excel/ArrayFormulas.aspx http://www.ozgrid.com/Excel/arrays.htm On Wed, Aug 29, 2012 at 3:38 PM, Muralidhar E emuralidha...@gmail.comwrote: hi to all group members, I want to learn can anyone send me the array formula's guide for beginners with examples. Thanks in advance. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- With Regards, Noorain Ansari http:// http://www.noorainansari.com/noorainansari.comhttp://www.noorainansari.com/ http:// http://www.excelvbaclinic.blogspot.com/excelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com/http://accesssqclinic.blogspot.in/ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ How many time I've opened a particular file in a day?
*Could you please inform how to work with this ...* On Thu, Aug 30, 2012 at 9:59 AM, Kuldeep Singh naukrikuld...@gmail.comwrote: Hi, Kindly see the attached file. Regards, Kuldeep Singh On Thu, Aug 30, 2012 at 1:33 AM, Indrajit $nai talk2indra...@gmail.comwrote: Hi All, Can we track through VBA, how many time I've opened a particular file in a day? If it's possible then please provide me the VBA code. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ How many time I've opened a particular file in a day?
Do you have a sheet that you can use to store the current date and a counter? If not, would you prefer to keep the count within a sheet ( hidden or not) or in an external file (like C:\temp\FileCount.txt) ? You can create an Application_Open event macro. There, read the date from either the cell or the text file. If the date is the current date, increment the counter. If it is not current (it is the first time it is opened) then save the date and initialize the counter to 1. If you share your preference for storing the date/counter, I can help create the macro. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Indrajit $nai talk2indra...@gmail.com To: excel-macros@googlegroups.com Sent: Wed, August 29, 2012 4:04:42 PM Subject: $$Excel-Macros$$ How many time I've opened a particular file in a day? Hi All, Can we track through VBA, how many time I've opened a particular file in a day? If it's possible then please provide me the VBA code. Thanks in advance. -- Indrajit Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ please help with array formula
Hi Noorin
what is logic of last number 4 in formula if I change it nothing change in
output
={INDEX($B$5:$B$29,SMALL(IF($C$5:$C$29=$G$6,ROW($C$5:$C$29),),ROW(B1))-4)}
I used excel 2003 so I changed formula like this Pl tell me logic of number
4 in last
On Tue, Aug 28, 2012 at 10:16 PM, maksood alam 786maks...@gmail.com wrote:
Thanks Noorain bhai.. its solved my qurey..
On Tue, Aug 28, 2012 at 8:35 PM, NOORAIN ANSARI
noorain.ans...@gmail.comwrote:
See attachment for more clarity
On Tue, Aug 28, 2012 at 8:08 PM, maksood alam 786maks...@gmail.comwrote:
Hi experts,
I have data in once sheet in two colums like *NAME*(colum A) and
*Marks*(colum
B). In 2nd sheet i want a formula if i enter 20 its should show me the name
of all the candidate who has 40 marks in 1st sheet. can it done with array
forumla. please help.
*Name* *Marks CT-1* SOMERSET, CHERRYL A 40 WILSON, BARBARA P 40
BLECKLEY,
SUSAN L 40 DOW, DONALD L 40 MONTE, NANCY P 40 MADEIROS, EMERY C 35 KEACH,
WALTER F 35 MADEIROS, DEBORAH MARIE 35 PAUL, RICHARD A 35 PAUL, JOAN L
35 SENSENBRENNER, SUSAN 35 BELOTTI, TIMOTHY V 35 BENNING, MALCOLM J 35
BENNING,
VIRGINIA 40 ROLLINS, ANNETTE 40 CRATTY, CYNTHIA 40 RYAN, PHYLLIS
100 SPENCER, SUSAN 100 VANALSTYNE, DAVID E 75 LINN, DANIEL B 100 BENSMAN,
SUE B 100 ROMEO, CONNIE R 75 GREENHOLZ, MICHAEL J 75 LINN, CAROL 100
RYMSKI,
JOSEPH G 100
*Marks on CT-1* *40* *List the Name of CT-1 Paper as per Scores*
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
With Regards,
Noorain Ansari
http://
http://www.noorainansari.com/noorainansari.comhttp://www.noorainansari.com/
http://
http://www.excelvbaclinic.blogspot.com/excelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com/http://accesssqclinic.blogspot.in/
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message
Re: $$Excel-Macros$$ please help me to solve this question it's very urgent
I see you have answers but why is YOUR request more URGENT than any other? Also, use a meaningful subject line and explain your question in the email and in the file. etc. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com -Original Message- From: PRAVESH KUMAR Sent: Wednesday, August 29, 2012 11:30 PM To: excel-macros ; excelvbaclinic Subject: $$Excel-Macros$$ please help me to solve this question it's very urgent Hi Team, please help me to solve this question it's very urgent waiting your response -- Thanks Regards Pravesh Kumar -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ please help with array formula
Dear Amar,
We are using No. 4 for row no., If you go through attachment, table start
from row no. 5, row no.4 is heading so, we deduct above all row Number
including header. so we are using 4 because we are deduction 4 row no.
Hope my explanation will help you.
On Thu, Aug 30, 2012 at 5:16 PM, amar takale amartak...@gmail.com wrote:
Hi Noorin
what is logic of last number 4 in formula if I change it nothing change in
output
={INDEX($B$5:$B$29,SMALL(IF($C$5:$C$29=$G$6,ROW($C$5:$C$29),),ROW(B1))-4)}
I used excel 2003 so I changed formula like this Pl tell me logic of
number 4 in last
On Tue, Aug 28, 2012 at 10:16 PM, maksood alam 786maks...@gmail.comwrote:
Thanks Noorain bhai.. its solved my qurey..
On Tue, Aug 28, 2012 at 8:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com
wrote:
See attachment for more clarity
On Tue, Aug 28, 2012 at 8:08 PM, maksood alam 786maks...@gmail.comwrote:
Hi experts,
I have data in once sheet in two colums like *NAME*(colum A) and *Marks
*(colum B). In 2nd sheet i want a formula if i enter 20 its should
show me the name of all the candidate who has 40 marks in 1st sheet. can it
done with array forumla. please help.
*Name* *Marks CT-1* SOMERSET, CHERRYL A 40 WILSON, BARBARA P 40
BLECKLEY,
SUSAN L 40 DOW, DONALD L 40 MONTE, NANCY P 40 MADEIROS, EMERY C 35 KEACH,
WALTER F 35 MADEIROS, DEBORAH MARIE 35 PAUL, RICHARD A 35 PAUL, JOAN L
35 SENSENBRENNER, SUSAN 35 BELOTTI, TIMOTHY V 35 BENNING, MALCOLM J
35 BENNING, VIRGINIA 40 ROLLINS, ANNETTE 40 CRATTY, CYNTHIA 40 RYAN,
PHYLLIS 100 SPENCER, SUSAN 100 VANALSTYNE, DAVID E 75 LINN, DANIEL
B 100 BENSMAN, SUE B 100 ROMEO, CONNIE R 75 GREENHOLZ, MICHAEL J 75 LINN,
CAROL 100 RYMSKI, JOSEPH G 100
*Marks on CT-1* *40* *List the Name of CT-1 Paper as per Scores*
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
With Regards,
Noorain Ansari
http://
http://www.noorainansari.com/noorainansari.comhttp://www.noorainansari.com/
http://
http://www.excelvbaclinic.blogspot.com/excelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com/http://accesssqclinic.blogspot.in/
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ 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
Re: $$Excel-Macros$$ Learn Array formula's
Sure murli, I will provide u shortly Sent from BlackBerry® on Airtel -Original Message- From: Muralidhar E emuralidha...@gmail.com Sender: excel-macros@googlegroups.com Date: Thu, 30 Aug 2012 12:44:38 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Learn Array formula's Hi, Thanks alot. If u give any files with examples in excel or in ppt. it will help me to learn very easily. Thanks in advance. Thanks Muralidhar E On Wed, Aug 29, 2012 at 8:25 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Murlidhar, Some best sites for Array Function http://www.cpearson.com/excel/ArrayFormulas.aspx http://www.ozgrid.com/Excel/arrays.htm On Wed, Aug 29, 2012 at 3:38 PM, Muralidhar E emuralidha...@gmail.comwrote: hi to all group members, I want to learn can anyone send me the array formula's guide for beginners with examples. Thanks in advance. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- With Regards, Noorain Ansari http:// http://www.noorainansari.com/noorainansari.comhttp://www.noorainansari.com/ http:// http://www.excelvbaclinic.blogspot.com/excelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com/http://accesssqclinic.blogspot.in/ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED
Re: $$Excel-Macros$$ Learn Array formula's
Sure Murlidhar, i will provide you shortly. On Thu, Aug 30, 2012 at 12:44 PM, Muralidhar E emuralidha...@gmail.comwrote: Hi, Thanks alot. If u give any files with examples in excel or in ppt. it will help me to learn very easily. Thanks in advance. Thanks Muralidhar E On Wed, Aug 29, 2012 at 8:25 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Murlidhar, Some best sites for Array Function http://www.cpearson.com/excel/ArrayFormulas.aspx http://www.ozgrid.com/Excel/arrays.htm On Wed, Aug 29, 2012 at 3:38 PM, Muralidhar E emuralidha...@gmail.comwrote: hi to all group members, I want to learn can anyone send me the array formula's guide for beginners with examples. Thanks in advance. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- With Regards, Noorain Ansari http:// http://www.noorainansari.com/noorainansari.comhttp://www.noorainansari.com/ http:// http://www.excelvbaclinic.blogspot.com/excelvbaclinic.comhttp://www.excelvbaclinic.blogspot.com/http://accesssqclinic.blogspot.in/ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Thanks Regards,* ** *Muralidhar E.* Think different Do Different Make different -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- With Regards, Noorain Ansari http:// http://www.noorainansari.comnoorainansari.comhttp://www.noorainansari.com http:// http://www.excelvbaclinic.blogspot.comexcelvbaclinic.comhttp://www.excelvbaclinic.blogspot.comhttp://accesssqclinic.blogspot.in/ -- Join official facebook page of this forum @
$$Excel-Macros$$ IF Formula
Hello, I have attached a .xlsx with the following values: A Column = Resolution Time - Formula is: =MAX(0,TIME(19,0,0)-MAX(MOD(C2,1),TIME(6,0,0)))+ MAX(0,(NETWORKDAYS(INT(C2)+1,INT(B2)-1)))*13/24+ MAX(0,MIN(MOD(B2,1),TIME(19,0,0))-TIME(6,0,0)) B Column = End Date and Time C Column = Start Date and Time D Column = Priority I need an IF statement that states, IF the D Column = Low or Medium, use the formula in the A Column, if not, then use B2 - C2. Thanks in advance. LJ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Test.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Macro to consolidate multiple sheets.
WithOUT looking at your file(s) something like this pseudo code ‘open each file for i=1 to 3 sheets(i).usedrange copy workbooks(“masterfile.xls”).sheets(i).cells(rows.count,1).end(xlup)(2) next i ‘close each file Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Krishnaraddi V. Madolli Sent: Thursday, August 30, 2012 8:37 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to consolidate multiple sheets. Hi Experts, I have 5 excel workbooks each consisting of 3 sheets called: Climate Data, Product Data Salary Details All these 5 excel workbooks are stored in single folder. I wanted one consolidated workbook which will be consisting of 3 sheets Climate Data, Product Data Salary Details and data from all 5 workbooks has to be consolidated in this workbook. I tried but able to extract 1 sheets at a time but not getting all 3 sheets consolidated at a time, I have attached macro I tried. I have almost 300 excel workbooks each consisting of nearly 20 tabs. Regards, Krishnaraddi V Madolli. Data Analytics Team Sedgwick Claims Management Services, Inc. Xchanging Towers, SJR iPark, EPIP Area, Whitefield Bangalore - 560 066. India. Direct Line: +1.800.920.9657 Extn 1915 Switchboard: +90-(0)80-3054 Extn 1915 Email: krishnaraddi.mado...@asia.xchanging.com krishnaraddi.mado...@sedgwickcms.com www.sedgwickcms.com | The leader in innovative claims and productivity management solutions Success is never permanent, Failure is never final, so always do not stop effort until your victory makes a history. Please consider the environment before printing this message -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. image001.png
Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition
Dear Guillett, Thank for the solution. Your answer is perfect. I wrote below macro for the same project. However it's not working properly. I tried to put the sum formula to get the total. I think error is writing the upper cell reference(e.g:G50). Do you have any idea about getting the correct cell reference for my method.Please note that I'm concerning the answer you mentioned asWRONG, it means I need the double of the correct total. --- Private Sub total() For r = 1 To 1 If Cells(r, C) = COLLECTION Then Cells(r, G) = =sum(G1:Gr) ' For an example if r=50 then Cells(50,G)=Sum(G50:G1) End If Next End Sub - Regards, Dilan On Thu, Aug 30, 2012 at 4:31 PM, dguillett1 dguille...@gmail.com wrote: See attached using this macro for the CORRECT answer Option Explicit Sub sumcollectionSAS() Dim mf As Range Dim cr As Long Dim c As Range Dim ms As Long Dim firstaddress As String Set mf = Columns(C).Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) If Not mf Is Nothing Then cr = mf.Row 'MsgBox cr With Columns(d:f) Set c = .Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then firstaddress = c.Address Do 'MsgBox c.Row ms = ms + Cells(c.Row, g) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address firstaddress End If End With 'MsgBox ms Cells(cr, g) = ms End Sub Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* Dilan De Silva dila...@gmail.com *Sent:* Thursday, August 30, 2012 3:18 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Macro to Calculate a total upon a condition Dear friends, I want to programme a macro to calculate a total. Refer *both sheets* of attached Excel sheet. When the macro runs, it should search the word COLLECTION. After the macro found the word; the cell on Coloumn G of same row(In this sheet cell G122) shoud have a formula to find total of all cells value in column G from row 1 to a raw before the COLLECTIONword located. Regards, Dilan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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
Re: $$Excel-Macros$$ IF Formula
Where do you want this IF statement to go? the format would be: =IF(OR(D2=Low,D2=Medium),A2,B2-C2) Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: LJ ljasonmil...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, August 30, 2012 9:52:59 AM Subject: $$Excel-Macros$$ IF Formula Hello, I have attached a .xlsx with the following values: A Column = Resolution Time - Formula is: =MAX(0,TIME(19,0,0)-MAX(MOD(C2,1),TIME(6,0,0)))+ MAX(0,(NETWORKDAYS(INT(C2)+1,INT(B2)-1)))*13/24+ MAX(0,MIN(MOD(B2,1),TIME(19,0,0))-TIME(6,0,0)) B Column = End Date and Time C Column = Start Date and Time D Column = Priority I need an IF statement that states, IF the D Column = Low or Medium, use the formula in the A Column, if not, then use B2 - C2. Thanks in advance. LJ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition
Your method of looping is inefficient when compared to FIND. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Dilan De Silva Sent: Thursday, August 30, 2012 8:58 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition Dear Guillett, Thank for the solution. Your answer is perfect. I wrote below macro for the same project. However it's not working properly. I tried to put the sum formula to get the total. I think error is writing the upper cell reference(e.g:G50). Do you have any idea about getting the correct cell reference for my method.Please note that I'm concerning the answer you mentioned asWRONG, it means I need the double of the correct total. --- Private Sub total() For r = 1 To 1 If Cells(r, C) = COLLECTION Then Cells(r, G) = =sum(G1:Gr) ' For an example if r=50 then Cells(50,G)=Sum(G50:G1) End If Next End Sub - Regards, Dilan On Thu, Aug 30, 2012 at 4:31 PM, dguillett1 dguille...@gmail.com wrote: See attached using this macro for the CORRECT answer Option Explicit Sub sumcollectionSAS() Dim mf As Range Dim cr As Long Dim c As Range Dim ms As Long Dim firstaddress As String Set mf = Columns(C).Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) If Not mf Is Nothing Then cr = mf.Row 'MsgBox cr With Columns(d:f) Set c = .Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then firstaddress = c.Address Do 'MsgBox c.Row ms = ms + Cells(c.Row, g) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address firstaddress End If End With 'MsgBox ms Cells(cr, g) = ms End Sub Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Dilan De Silva Sent: Thursday, August 30, 2012 3:18 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to Calculate a total upon a condition Dear friends, I want to programme a macro to calculate a total. Refer both sheets of attached Excel sheet. When the macro runs, it should search the word COLLECTION. After the macro found the word; the cell on Coloumn G of same row(In this sheet cell G122) shoud have a formula to find total of all cells value in column G from row 1 to a raw before the COLLECTIONword located. Regards, Dilan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To
Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition
It IS less efficient, but if you wanted to fix it... You're formula is NOT =sum(G1:Gr) but instead =sum(G1:G r ) (because you want a string of characters, the VALUE of a variable, then another string of characters.) Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: dguillett1 dguille...@gmail.com To: excel-macros@googlegroups.com Sent: Thu, August 30, 2012 10:17:10 AM Subject: Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition Your method of looping is inefficient when compared to FIND. Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Dilan De Silva Sent: Thursday, August 30, 2012 8:58 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Macro to Calculate a total upon a condition Dear Guillett, Thank for the solution. Your answer is perfect. I wrote below macro for the same project. However it's not working properly. I tried to put the sum formula to get the total. I think error is writing the upper cell reference(e.g:G50). Do you have any idea about getting the correct cell reference for my method.Please note that I'm concerning the answer you mentioned asWRONG, it means I need the double of the correct total. --- Private Sub total() For r = 1 To 1 If Cells(r, C) = COLLECTION Then Cells(r, G) = =sum(G1:Gr) ' For an example if r=50 then Cells(50,G)=Sum(G50:G1) End If Next End Sub - Regards, Dilan On Thu, Aug 30, 2012 at 4:31 PM, dguillett1 dguille...@gmail.com wrote: See attached using this macro for the CORRECT answer Option Explicit Sub sumcollectionSAS() Dim mf As Range Dim cr As Long Dim c As Range Dim ms As Long Dim firstaddress As String Set mf = Columns(C).Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) If Not mf Is Nothing Then cr = mf.Row 'MsgBox cr With Columns(d:f) Set c = .Find(What:=collection, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then firstaddress = c.Address Do 'MsgBox c.Row ms = ms + Cells(c.Row, g) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address firstaddress End If End With 'MsgBox ms Cells(cr, g) = ms End Sub Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: Dilan De Silva Sent: Thursday, August 30, 2012 3:18 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to Calculate a total upon a condition Dear friends, I want to programme a macro to calculate a total. Refer both sheets of attached Excel sheet. When the macro runs, it should search the word COLLECTION. After the macro found the word; the cell on Coloumn G of same row(In this sheet cell G122) shoud have a formula to find total of all cells value in column G from row 1 to a raw before the COLLECTIONword located. Regards, Dilan-- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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,
Re: $$Excel-Macros$$ Macro to consolidate multiple sheets.
Option Explicit Sub merge_multiple_workbooks() ' DECLARE ALL VARIABLES AND ARRAYS Dim fldpath Dim fld, fil, FSO As Object Dim WKB As Workbook Dim wks As Worksheet Dim shtnames() Dim Paste Dim j As Long, w As Long Dim stcol As String, lastcol As String stcol = A lastcol = iv With Application.FileDialog(msoFileDialogFolderPicker) .Title = Choose the folder .Show End With On Error Resume Next fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ If fldpath = False Then MsgBox Folder Not Selected Exit Sub End If shtnames = Array(Climate Data, Product Data, Salary Details) '\ add or remove sheets Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.StatusBar = True Application.StatusBar = Please wait till Macro merge all the files Set FSO = CreateObject(scripting.filesystemobject) Set fld = FSO.getfolder(fldpath) For Each fil In fld.Files If UCase(Right(fil.Path, 4)) = UCase(.xls) And fil.Name ThisWorkbook.Name Then Set WKB = Workbooks.Open(fil.Path) For j = LBound(shtnames) To UBound(shtnames) For Each wks In WKB.Sheets If wks.Name = shtnames(j) Then w = WKB.Sheets(shtnames(j)).Range(a65356).End(xlUp).Row If w = 2 Then WKB.Sheets(shtnames(j)).Range(stcol 2: lastcol w).Copy _ Destination:=ThisWorkbook.Sheets(shtnames(j)).Range(a65356).End(xlUp).Offset(1, 0) End If Exit For End If Next Next WKB.Close End If Next MsgBox Done Application.StatusBar = False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub On Thu, Aug 30, 2012 at 7:20 PM, dguillett1 dguille...@gmail.com wrote: WithOUT looking at your file(s) something like this pseudo code ‘open each file for i=1 to 3 sheets(i).usedrange copy workbooks(“masterfile.xls”).sheets(i).cells(rows.count,1).end(xlup)(2) next i ‘close each file Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* Krishnaraddi V. Madolli krishnaraddi.mado...@asia.xchanging.com *Sent:* Thursday, August 30, 2012 8:37 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Macro to consolidate multiple sheets. Hi Experts, I have 5 excel workbooks each consisting of 3 sheets called: Climate Data, Product Data Salary Details All these 5 excel workbooks are stored in single folder. I wanted one consolidated workbook which will be consisting of 3 sheets Climate Data, Product Data Salary Details and data from all 5 workbooks has to be consolidated in this workbook. I tried but able to extract 1 sheets at a time but not getting all 3 sheets consolidated at a time, I have attached macro I tried. I have almost 300 excel workbooks each consisting of nearly 20 tabs. Regards, Krishnaraddi V Madolli. Data Analytics Team Sedgwick Claims Management Services, Inc. Xchanging Towers, SJR iPark, EPIP Area, Whitefield Bangalore - 560 066. India. Direct Line: +1.800.920.9657 Extn 1915 Switchboard: +90-(0)80-3054 Extn 1915 Email: krishnaraddi.mado...@asia.xchanging.com krishnaraddi.mado...@sedgwickcms.com www.sedgwickcms.comhttps://outlook.us.xchanging.com/exchweb/bin/redir.asp?URL=http://www.sedgwickcms.com| *The leader in innovative claims and productivity management solutions* Success is never permanent, Failure is never final, so always do not stop effort until your victory makes a history. *[image: http://images.coolchaser.com/themes/t/404137-i311.photobucket.com-albums-kk467-volleycutiegirl-icon.png] **Please consider the environment before printing this message*** -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @
$$Excel-Macros$$ Transpose (Arr) problem with Dates
First post guys! I am trying to convert dates stored as text into proper formatted UK dates DD/MM/. A sample spreadsheet is attached. The dates as text are copied into a variant array, converted to a real date using DateSerial then copied to a new range using the WorksheetFunction.Transpose. The dates look formatted ok whilst viewing the locals in the RealDate array, but but when copied back to the new range 05/04/2012 becomes 04/05/2012. I want to avoid looping through each individual cell so grateful if someone could provide a solution. Many thanks -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. vbaCDate.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Transpose (Arr) problem with Dates
If they're in one column... Select the range data|text to columns choose fixed width and remove any lines that excel guessed choose Date (ymd) and plop it in the same range as where you picked it up. And format those (now real) dates the way you want. Or, a nice ONE liner Sub ConvertStringToDateSAS() Columns(C).TextToColumns Destination:=Range(E1), _ DataType:=xlFixedWidth, FieldInfo:=Array(0, 3) Columns(e).AutoFit End Sub Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com From: RLM Sent: Thursday, August 30, 2012 12:57 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Transpose (Arr) problem with Dates First post guys! I am trying to convert dates stored as text into proper formatted UK dates DD/MM/. A sample spreadsheet is attached. The dates as text are copied into a variant array, converted to a real date using DateSerial then copied to a new range using the WorksheetFunction.Transpose. The dates look formatted ok whilst viewing the locals in the RealDate array, but but when copied back to the new range 05/04/2012 becomes 04/05/2012. I want to avoid looping through each individual cell so grateful if someone could provide a solution. Many thanks -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Transpose (Arr) problem with Dates
Just append this line after pasting data in the range Range(E2).Resize(UBound(RealDate), 1).NumberFormat = dd/mm/ Regards, Lalit Mohan On Thursday, 30 August 2012 23:27:37 UTC+5:30, RLM wrote: First post guys! I am trying to convert dates stored as text into proper formatted UK dates DD/MM/. A sample spreadsheet is attached. The dates as text are copied into a variant array, converted to a real date using DateSerial then copied to a new range using the WorksheetFunction.Transpose. The dates look formatted ok whilst viewing the locals in the RealDate array, but but when copied back to the new range 05/04/2012 becomes 04/05/2012. I want to avoid looping through each individual cell so grateful if someone could provide a solution. Many thanks -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Urgent Need of Front End Web Developer - San Jose, CA
Job posting is not allow in this forum. You will be banned next time Sent from BlackBerry® on Airtel -Original Message- From: john meridiansoft johnmeridians...@gmail.com Sender: excel-macros@googlegroups.com Date: Thu, 30 Aug 2012 18:32:18 Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent Need of Front End Web Developer - San Jose, CA Please send resume to karth...@meridiansoft.com Hi, Greeting!! We are immediately looking for a *Front End Web Developer* for one of our direct clients. If interested or know someone looking for new opportunities please reply with your updated resume to karth...@meridiansoft.com or you can also reach me on *614-340-3331 Ext 215* to discuss this opportunity. Title: Front End Web Developer Location : San Jose, CA Duration: 3+ Months *Competencies :* * * · *Experience in using * · *Html, * · *CSS, * · *JavaScript,* · *JSPs, * · *Servlets,* · *Ajax, * · *Flash and Java Beans …* · *MVC architecture * · *web frameworks like Struts and others (e.g. Spring etc.). *** Thanks Regards Karthik | Technical Recruiter *Meridiansoft Inc.* | E-mail: karth...@meridiansoft.com| W: + 614-340-3331 Ext 215 | F: + 614 455 9111 | Gtalk: karthikmeridians...@gmail.com | Website: www.meridiansoft.com|http://www.meridiansoft.com%7C/ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ 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. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
