Re: $$Excel-Macros$$ Re: Quarter Classification
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Then in C2
=TEXT((1B2)+99,)
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
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
--
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
Copy_Quarter.xls
Description: MS-Excel spreadsheet
$$Excel-Macros$$ Reg-Convert this no to text
Hi..Experts.. i m facing a problem to convert a number in text, so kindly help me on this problem -- P Before printing, think about the environment. Devendra sahay -- 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 Convert this number to text.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Time Calcuation
*Hi Experts, Please see the attached File. There i am getting error in time calculation. please do the needful. Thanks in advance. * Thanks Regards,* * *Deba * -- 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 Copy of test.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Time Calcuation
TEXT(B5+C5,[h]:mm:ss) On Mon, Apr 16, 2012 at 12:00 PM, Deba Ranjan drdeva...@gmail.com wrote: *Hi Experts, Please see the attached File. There i am getting error in time calculation. please do the needful. Thanks in advance. * Thanks Regards,* * *Deba * -- 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 -- Thanks Regards Anil Kumar -- 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$$ Time Calcuation
*Hi Experts, Its not working Please do in the sheet please, so that i will clarified. thanks * Thanks Regards,* * *Deba Ranjan P*** On Mon, Apr 16, 2012 at 12:20 PM, anil panchal anil.kan...@gmail.comwrote: TEXT(B5+C5,[h]:mm:ss) On Mon, Apr 16, 2012 at 12:00 PM, Deba Ranjan drdeva...@gmail.com wrote: *Hi Experts, Please see the attached File. There i am getting error in time calculation. please do the needful. Thanks in advance. * Thanks Regards,* * *Deba * -- 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 -- Thanks Regards Anil Kumar -- 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$$ Time Calcuation
Hi, Try it, =TEXT((B5-INT(B5))+(C5-INT(C5)),[h]:mm:ss) On Mon, Apr 16, 2012 at 10:50 AM, anil panchal anil.kan...@gmail.comwrote: TEXT(B5+C5,[h]:mm:ss) -- 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
Thanks a lot Sir, I am updating my sheets and will get back to you. Regards On Sun, Apr 15, 2012 at 10:13 PM, dguillett1 dguille...@gmail.com wrote: If your request is for a reply, I did reply. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Abdulgani Shaikh itpabdulg...@gmail.com *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.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
Re: $$Excel-Macros$$ Time Calcuation
*PFA...* On Mon, Apr 16, 2012 at 11:04 AM, Maries talk2mar...@gmail.com wrote: Hi, Try it, =TEXT((B5-INT(B5))+(C5-INT(C5)),[h]:mm:ss) On Mon, Apr 16, 2012 at 10:50 AM, anil panchal anil.kan...@gmail.comwrote: TEXT(B5+C5,[h]:mm:ss) -- 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$$ Time Calcuation
*Thanks for the help , its brilliant working. * Thanks Regards,* * *Deba Ranjan P*** On Mon, Apr 16, 2012 at 12:34 PM, Maries talk2mar...@gmail.com wrote: Hi, Try it, =TEXT((B5-INT(B5))+(C5-INT(C5)),[h]:mm:ss) On Mon, Apr 16, 2012 at 10:50 AM, anil panchal anil.kan...@gmail.comwrote: TEXT(B5+C5,[h]:mm:ss) -- 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
Dear All,
Why Use *29???
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Thanks Regards
Anil Kumar
113784
On Mon, Apr 16, 2012 at 11:36 AM, Haseeb A haseeb.avarak...@gmail.comwrote:
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Then in C2
=TEXT((1B2)+99,)
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
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
--
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
--
Thanks Regards
Anil Kumar
--
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$$ Urgent help required in converting a number into text
Hi, Function English(ByVal N As Currency) As String Const Thousand = 1000@ Const Million = Thousand * Thousand Const Billion = Thousand * Million Const Trillion = Thousand * Billion If (N = 0@) Then English = zero: Exit Function Dim Buf As String: If (N 0@) Then Buf = negative Else Buf = Dim Frac As Currency: Frac = Abs(N - Fix(N)) If (N 0@ Or Frac 0@) Then N = Abs(Fix(N)) Dim AtLeastOne As Integer: AtLeastOne = N = 1 If (N = Trillion) Then Debug.Print N Buf = Buf EnglishDigitGroup(Int(N / Trillion)) trillion N = N - Int(N / Trillion) * Trillion If (N = 1@) Then Buf = Buf End If If (N = Billion) Then Debug.Print N Buf = Buf EnglishDigitGroup(Int(N / Billion)) billion N = N - Int(N / Billion) * Billion If (N = 1@) Then Buf = Buf End If If (N = Million) Then Debug.Print N Buf = Buf EnglishDigitGroup(N \ Million) million N = N Mod Million If (N = 1@) Then Buf = Buf End If If (N = Thousand) Then Debug.Print N Buf = Buf EnglishDigitGroup(N \ Thousand) thousand N = N Mod Thousand If (N = 1@) Then Buf = Buf End If If (N = 1@) Then Debug.Print N Buf = Buf EnglishDigitGroup(N) End If If (Frac = 0@) Then Buf = Buf exactly ElseIf (Int(Frac * 100@) = Frac * 100@) Then If AtLeastOne Then Buf = Buf and Buf = Buf Format$(Frac * 100@, 00) /100 Else If AtLeastOne Then Buf = Buf and Buf = Buf Format$(Frac * 1@, ) /1 End If English = Buf End Function Private Function EnglishDigitGroup(ByVal N As Integer) As String Const Hundred = hundred Const One = one Const Two = two Const Three = three Const Four = four Const Five = five Const Six = six Const Seven = seven Const Eight = eight Const Nine = nine Dim Buf As String: Buf = Dim Flag As Integer: Flag = False Select Case (N \ 100) Case 0: Buf = : Flag = False Case 1: Buf = One Hundred: Flag = True Case 2: Buf = Two Hundred: Flag = True Case 3: Buf = Three Hundred: Flag = True Case 4: Buf = Four Hundred: Flag = True Case 5: Buf = Five Hundred: Flag = True Case 6: Buf = Six Hundred: Flag = True Case 7: Buf = Seven Hundred: Flag = True Case 8: Buf = Eight Hundred: Flag = True Case 9: Buf = Nine Hundred: Flag = True End Select If (Flag False) Then N = N Mod 100 If (N 0) Then If (Flag False) Then Buf = Buf Else EnglishDigitGroup = Buf Exit Function End If Select Case (N \ 10) Case 0, 1: Flag = False Case 2: Buf = Buf twenty: Flag = True Case 3: Buf = Buf thirty: Flag = True Case 4: Buf = Buf forty: Flag = True Case 5: Buf = Buf fifty: Flag = True Case 6: Buf = Buf sixty: Flag = True Case 7: Buf = Buf seventy: Flag = True Case 8: Buf = Buf eighty: Flag = True Case 9: Buf = Buf ninety: Flag = True End Select If (Flag False) Then N = N Mod 10 If (N 0) Then If (Flag False) Then Buf = Buf - Else EnglishDigitGroup = Buf Exit Function End If Select Case (N) Case 0: Case 1: Buf = Buf One Case 2: Buf = Buf Two Case 3: Buf = Buf Three Case 4: Buf = Buf Four Case 5: Buf = Buf Five Case 6: Buf = Buf Six Case 7: Buf = Buf Seven Case 8: Buf = Buf Eight Case 9: Buf = Buf Nine Case 10: Buf = Buf ten Case 11: Buf = Buf eleven Case 12: Buf = Buf twelve Case 13: Buf = Buf thirteen Case 14: Buf = Buf fourteen Case 15: Buf = Buf fifteen Case 16: Buf = Buf sixteen Case 17: Buf = Buf seventeen Case 18: Buf = Buf eighteen Case 19: Buf = Buf nineteen End Select EnglishDigitGroup = Buf End Function On Mon, Apr 16, 2012 at 11:52 AM, Devendra Sahay devendrasahanypt...@gmail.com wrote: Hi... Please help me on this problem.. -- P Before printing, think about the environment. Devendra sahay -- 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
Re: $$Excel-Macros$$ Re: Quarter Classification
Hi Anil,
Its very good formula made by Mr.Haseeb. 29 is used to pick the dates.
check below,
[image: Inline image 1]
like 28,30 also can use.
Regards,
MARIES.
On Mon, Apr 16, 2012 at 11:25 AM, anil panchal anil.kan...@gmail.comwrote:
Dear All,
Why Use *29???
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Thanks Regards
Anil Kumar
113784
On Mon, Apr 16, 2012 at 11:36 AM, Haseeb A
haseeb.avarak...@gmail.comwrote:
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Then in C2
=TEXT((1B2)+99,)
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
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
--
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
--
Thanks Regards
Anil Kumar
--
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
123.JPG
Re: $$Excel-Macros$$ Re: Quarter Classification
Hi Haseeb,
Nice Formula..
Thanks
Nikhil
On Mon, Apr 16, 2012 at 11:36 AM, Haseeb A haseeb.avarak...@gmail.comwrote:
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Then in C2
=TEXT((1B2)+99,)
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
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
--
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$$ Time Calcuation
Dear Deba, Maries solution is nice... You can also use below formula with custom format [h]:mm:ss =($B5-ROUNDDOWN($B5,0))+($C5-ROUNDDOWN($C5,0)) -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Mon, Apr 16, 2012 at 12:40 PM, Deba Ranjan drdeva...@gmail.com wrote: *Thanks for the help , its brilliant working. * Thanks Regards,* * *Deba Ranjan P*** On Mon, Apr 16, 2012 at 12:34 PM, Maries talk2mar...@gmail.com wrote: Hi, Try it, =TEXT((B5-INT(B5))+(C5-INT(C5)),[h]:mm:ss) On Mon, Apr 16, 2012 at 10:50 AM, anil panchal anil.kan...@gmail.comwrote: TEXT(B5+C5,[h]:mm:ss) -- 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$$ Find differences in two worksheets.
Provide file(s) and complete explanation and examples. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Mr excel Sent: Sunday, April 15, 2012 9:08 PM To: excel-macros@googlegroups.com Subject: $$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 -- 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$$ Find differences in two worksheets.
Dear Mr. Excel, http://www.office-addins.com/plugins-reviews/excel-compare-workbooks-worksheets/ -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Mon, Apr 16, 2012 at 7:38 AM, Mr excel excelkeec...@gmail.com wrote: 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 -- 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$$ SPLIT into many FILES
If we have sample file then we can better understand the real issue , please attach your file. Rajan -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Somnath Khadilkar Sent: Apr/Mon/2012 07:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ SPLIT into many FILES Sir, any help ? =Mangal Ho On 4/10/12, Somnath Khadilkar khadilka...@gmail.com wrote: Sir, A while ago, you had sent me a file, [ which I am enclosing ] to convert the DATA file into MANY SHEETS depeding upon filter condition, That is V useful, but I would like to generate NewFIles [ instead of New Sheets in the same XL file, since, i want to email them [using AUTOmailer, but I reqd separate files], hence sir, i need your help. =mangal ho On 4/10/12, dguillett1 dguille...@gmail.com wrote: A looping macro can be developed to filter the data and send it to each. Properly done you need only ONE mouse click. Or, if it takes awhile to run, set to run after you go home for the day. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Somnath Khadilkar Sent: Monday, April 09, 2012 11:58 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ SPLIT into many FILES , depeding UPON... Dear Sir, The foremost reason is I need to EMAIL this file to the Respective CityCircle, they require ONLY there own CITY info. The separate files so created will be saving lot of our SPACE .Currently anyway I am doing it MANUALLY [ rathersorry state!! ] Print out of the repo, we do at our HdOfice. and w/o a macro it takes too much of time...[ over five hundred LEFT clicks alone!! ] =mangal ho On 4/9/12, dguillett1 dguille...@gmail.com wrote: I haven't followed all of this but it seems to me that there is no reason for separate files. To get reports or print you can use filtering with/without macros and use only ONE file. Good design??? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Somnath Khadilkar Sent: Monday, April 09, 2012 9:58 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition Sir, there are many many colm.in the sheets [ that mighht complex the issue ] , but as a sample I am able to limit to the TWO fields and one QTY field. I need all the colm A thru C here in the RESPECTIVE files. --- issue 2. I am able to generate a datafile [ thru some 'C' code , can you guide me to 'CONVERT' these into XLS files thru some tool, [ w/o openining/saving individuay, as these are TOO many, I need to AUTOMAIL ] THIS is altogether a diff. issue, but any help will be highly appreciated ] =Mangal Ho On 4/9/12, Rajan_Verma rajanverma1...@gmail.com wrote: So , what the excel file will conatin? Only one Row? -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Somnath Khadilkar Sent: Apr/Mon/2012 08:11 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition Dear Sir, The attachment is file with more data in it [ only ONE work SHeet , say sheet1 ] 1. the col.A contains CITY code [ say currently upto 10 eg AHM, DEL, DEL.. ] 2. the col.B contains item-code say item-1, item-2, item-3...[ for that matter mango,apple,banana ] currently upto 25 distinct 'FRUITS' 3. Colm C contains just a number showing how many of these exists say 1,2, 600 etc.. now the output after running thr macro should be containining a subfolder say 'data' and XL files in it by name say AHM-item-1, AHM-item-10, [ if quantity exists for the perticular city/item-codeXX ] =mangal ho On 4/9/12, Rajan_Verma rajanverma1...@gmail.com wrote: Can you add more data and explain how you want to bifurcate the data? Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Somnath Khadilkar Sent: Apr/Mon/2012 07:37 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ one to many FILES , depeding upon filter condition Dear Sir, I reqd the output to be upto say 25 * 10 distict FILES [ sir, not diff WorkSheets ] the names of these files will be combination of the 'CITY' and 'ITEM' code eg del-item001, hyd-item002, [ in a named subfolder, so that I can routinely keep track. ] thanks in adv. PS: if the file exists --- may be just OVERwrite it...[ if possible w/o prompting the user] =mangal ho On 4/9/12, Somnath Khadilkar khadilka...@gmail.com wrote: Dear Sir, MY request is to get the data into MULTIFLE FILES depending upon FILTER Selected. The file names should be say ahm-item-1, ahm-item-2 etc.. [ these will be unique combination] in a subdirectory named say XXYY,
RE: $$Excel-Macros$$ Fwd: If data in One then another cell to be unlock
Ok, if your file is protected , you have no option to remove data from worksheet without unprotecting , Worksheets(SheetName).Unprotect Password Range(A1:J58).SpecialCells(xlCellTypeConstants, 1).ClearContents Worksheets(SheetName).Protect Password Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Abdulgani Shaikh Sent: Apr/Mon/2012 09:29 To: excel-macros@googlegroups.com Subject: 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.com wrote: 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
RE: $$Excel-Macros$$ Creation of dashboard
yes.. Many freelancers are there on group. Let us know if you have any query related excel and VBA J Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deba Ranjan Sent: Apr/Mon/2012 12:50 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Creation of dashboard Hi Expert, Can you please create a dashboard for the attached file. Thanks Regards, Deba Ranjan P -- 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
Congratulations Don. thanks for your help !!! Chilexcel 2012/4/15 Somnath Khadilkar khadilka...@gmail.com 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 -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- 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
Don I also worked at ING (Chile) Congratulations again Chilexcel 2012/4/16 ChilExcel chilexcel...@gmail.com Congratulations Don. thanks for your help !!! Chilexcel 2012/4/15 Somnath Khadilkar khadilka...@gmail.com 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 -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- 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$$ Folder Structure - VBA Code
HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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
Did you NOT get this Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAidSoftware dguille...@gmail.com Application.EnableEvents = True If Target.Address = $C$3 Then Me.Unprotect With Range(E3) If Target = 0 Or Len(Application.Trim(Target)) = 0 Then .Locked = True Else .Locked = False .Select End If End With Me.Protect End If End Sub 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
$$Excel-Macros$$ Folder Structure - VBA Code
HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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$$ Folder Structure - VBA Code
HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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$$ inventory in excel
hi, how to maintain inventory in excel. if any one have samples pls let me know Thank you with regards Raghu -- 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$$ Folder Structure - VBA Code
Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. '=== Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function '=== Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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$$ Folder Structure - VBA Code
HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. '=== Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function '=== Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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$$ inventory in excel
Hi, Check these links... http://www.excelmagic.com/inventorymagic.html http://www.excel-skills.com/excel_templates.asp#InventoryUsage On Mon, Apr 16, 2012 at 6:22 PM, raghu gr balaji.bra...@gmail.com wrote: hi, how to maintain inventory in excel. if any one have samples pls let me know Thank you with regards Raghu -- 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$$ Folder Structure - VBA Code
So basically you want this : Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) With Application.FileDialog(msoFileDialogFolderPicker) .Show Set ObjFolder = objFso.GetFolder(.SelectedItems(1)) End With ActiveSheet.UsedRange.ClearContents Range(A1:B1).Value = Array(Folder, Files) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder1 ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function Function LoopThroughEachFolder1(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path For Each objFl In objFldLoop.Files Range(A1).Offset(lngCounter, 1).Value = objFl.Name lngCounter = lngCounter + 1 Next LoopThroughEachFolder objFldLoop Next End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:20 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. '= == Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function '= == Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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
Re: $$Excel-Macros$$ Folder Structure - VBA Code
HI Rajan, Awesome its working fine as per my requirement, need a small correction, the subfolder’s is not working for more than one folder, sorry I am not so good @ VBA hence I can get it on my own so I am troubling you. I require to have the last continent of each folder. Example : Main Folder Subfolder 1 Subfolder 2 Subfolder 2.3 Subfolder 2.4 Thanks and regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: So basically you want this : Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) With Application.FileDialog(msoFileDialogFolderPicker) .Show Set ObjFolder = objFso.GetFolder(.SelectedItems(1)) End With ActiveSheet.UsedRange.ClearContents Range(A1:B1).Value = Array(Folder, Files) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder1 ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function Function LoopThroughEachFolder1(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path For Each objFl In objFldLoop.Files Range(A1).Offset(lngCounter, 1).Value = objFl.Name lngCounter = lngCounter + 1 Next LoopThroughEachFolder objFldLoop Next End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:20 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. '= == Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function '= == Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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$$ Folder Structure - VBA Code
Please find attached sheet, I think its iterating each sub folders and files. Rajan -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Awesome its working fine as per my requirement, need a small correction, the subfolder's is not working for more than one folder, sorry I am not so good @ VBA hence I can get it on my own so I am troubling you. I require to have the last continent of each folder. Example : Main Folder Subfolder 1 Subfolder 2 Subfolder 2.3 Subfolder 2.4 Thanks and regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: So basically you want this : Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) With Application.FileDialog(msoFileDialogFolderPicker) .Show Set ObjFolder = objFso.GetFolder(.SelectedItems(1)) End With ActiveSheet.UsedRange.ClearContents Range(A1:B1).Value = Array(Folder, Files) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder1 ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function Function LoopThroughEachFolder1(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path For Each objFl In objFldLoop.Files Range(A1).Offset(lngCounter, 1).Value = objFl.Name lngCounter = lngCounter + 1 Next LoopThroughEachFolder objFldLoop Next End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:20 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. ' = == Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function ' = == Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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.
Re: $$Excel-Macros$$ Folder Structure - VBA Code
HI Rajan, Please find the attached file.I have run the macro and unable to get the folder contains of the C:\Documents and Settings\pavan\Desktop\New Folder\Gk\Copy of Gk which also contians A B xls. Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Please find attached sheet, I think its iterating each sub folders and files. Rajan -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Awesome its working fine as per my requirement, need a small correction, the subfolder's is not working for more than one folder, sorry I am not so good @ VBA hence I can get it on my own so I am troubling you. I require to have the last continent of each folder. Example : Main Folder Subfolder 1 Subfolder 2 Subfolder 2.3 Subfolder 2.4 Thanks and regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: So basically you want this : Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) With Application.FileDialog(msoFileDialogFolderPicker) .Show Set ObjFolder = objFso.GetFolder(.SelectedItems(1)) End With ActiveSheet.UsedRange.ClearContents Range(A1:B1).Value = Array(Folder, Files) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder1 ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function Function LoopThroughEachFolder1(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path For Each objFl In objFldLoop.Files Range(A1).Offset(lngCounter, 1).Value = objFl.Name lngCounter = lngCounter + 1 Next LoopThroughEachFolder objFldLoop Next End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:20 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. ' = == Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function ' = == Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\SF4\ C:\OLD\Main\SF1\SF2\SF3\SF4\ Regards, Pavan kumar G -- 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)
$$Excel-Macros$$ format in user form text box
hi group, how to put number format and date format in text box in user form. Thanks Regards Rajesh Mahapatra -- 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$$ Folder Structure - VBA Code
Ah,, Got the problem actually I have call Another Function , Now you can check Rajan -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 09:04 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Please find the attached file.I have run the macro and unable to get the folder contains of the C:\Documents and Settings\pavan\Desktop\New Folder\Gk\Copy of Gk which also contians A B xls. Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Please find attached sheet, I think its iterating each sub folders and files. Rajan -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Awesome its working fine as per my requirement, need a small correction, the subfolder's is not working for more than one folder, sorry I am not so good @ VBA hence I can get it on my own so I am troubling you. I require to have the last continent of each folder. Example : Main Folder Subfolder 1 Subfolder 2 Subfolder 2.3 Subfolder 2.4 Thanks and regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: So basically you want this : Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) With Application.FileDialog(msoFileDialogFolderPicker) .Show Set ObjFolder = objFso.GetFolder(.SelectedItems(1)) End With ActiveSheet.UsedRange.ClearContents Range(A1:B1).Value = Array(Folder, Files) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder1 ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function Function LoopThroughEachFolder1(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path For Each objFl In objFldLoop.Files Range(A1).Offset(lngCounter, 1).Value = objFl.Name lngCounter = lngCounter + 1 Next LoopThroughEachFolder objFldLoop Next End Function -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 08:20 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Folder Structure - VBA Code HI Rajan, Thanks for the replay, its working fine, i need little more help on the same, i like to have the folder contains in the next colm(forgot to mention in my earlier mails). Thanks and Regards, Pavan Kumar G On 4/16/12, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Pavan, Please try this , give your folder path instead of C:\PenDrive\, it will give you folder structure.. I am unable to get the files in UNZIP folder ,I will come back to you on this. '=== = = == Public ObjFolder As Object Public objFso As Object Public objFldLoop As Object Public lngCounter As Long Dim objFl As Object Sub GetFolderStructure() ' lngCounter = 0 Set objFso = CreateObject(Scripting.FileSystemObject) Set ObjFolder = objFso.GetFolder(C:\PenDrive\) Range(A1).Offset(lngCounter).Value = ObjFolder.Path LoopThroughEachFolder ObjFolder End Sub Function LoopThroughEachFolder(fldFolder As Object) For Each objFldLoop In fldFolder.subFolders lngCounter = lngCounter + 1 Range(A1).Offset(lngCounter).Value = objFldLoop.Path LoopThroughEachFolder objFldLoop Next End Function '=== = = == Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pavan Kumar Sent: Apr/Mon/2012 07:45 To: excel-macros Subject: $$Excel-Macros$$ Folder Structure - VBA Code HI Group, I am looking for a VBA code to get the Folder Structure, name and type (the code also needs to get the folder structure of Zip files too) Exmpl: C:\OLD\Main\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\ C:\OLD\Main\SF1\SF2\SF3\ C:\OLD\Main\SF1\SF2\SF3\
$$Excel-Macros$$ Need help with code to automate copy/paste
Hi everybody! I am hoping someone may be able to jelp me out. I am trying to write a macro that will take a formula that i write and copy it, move to the right seven spaces, paste, move seven spaces again, paste, ect.. Right now I need to have the formula pasted all of the way through column AZZ. Does anybody have anything made that I can tweak, or can anybody point me in a direction to find anything. I am quite the novice when it comes to writing VBA from scratch, but I am sure I can get something going if I am given something to start with. Your help is appreciated!! Thanks! Erick -- 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$$ Need help with code to automate copy/paste
Hi Erick, I Will glad to help but can you please explain more what you actually want? I mean what will be the input and output.? Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Erick C Sent: Apr/Mon/2012 09:43 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help with code to automate copy/paste Hi everybody! I am hoping someone may be able to jelp me out. I am trying to write a macro that will take a formula that i write and copy it, move to the right seven spaces, paste, move seven spaces again, paste, ect.. Right now I need to have the formula pasted all of the way through column AZZ. Does anybody have anything made that I can tweak, or can anybody point me in a direction to find anything. I am quite the novice when it comes to writing VBA from scratch, but I am sure I can get something going if I am given something to start with. Your help is appreciated!! Thanks! Erick -- 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$$ Need help with code to automate copy/paste
try something like this Sub Macro1() ' suppose ur forumula is in a1 Range(A1).Copy For i = 9 To Range(azz1).Column Cells(1, i).Select Selection.PasteSpecial Paste:=xlPasteFormulas i = i + 7 Next End Sub On Mon, Apr 16, 2012 at 9:51 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi Erick, I Will glad to help but can you please explain more what you actually want? I mean what will be the input and output.? Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Erick C Sent: Apr/Mon/2012 09:43 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help with code to automate copy/paste Hi everybody! I am hoping someone may be able to jelp me out. I am trying to write a macro that will take a formula that i write and copy it, move to the right seven spaces, paste, move seven spaces again, paste, ect.. Right now I need to have the formula pasted all of the way through column AZZ. Does anybody have anything made that I can tweak, or can anybody point me in a direction to find anything. I am quite the novice when it comes to writing VBA from scratch, but I am sure I can get something going if I am given something to start with. Your help is appreciated!! Thanks! Erick -- 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 -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* http://www.accessvbamacros.com/ P Before printing, think about the environment. -- 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$$ Need help with code to automate copy/paste
Hi Ashish, Thank you, your suggestion works perfectly! Thanls again for the prompt response! -- 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$$ Group Total
Dear Group, 1) I want to get the total of group in summary, please review the sheet and suggest the formula. 2) Secondly how to sort the entire groups rows (without describing the order of inner rows) of groups in ascending descending orders if groups are more then 100 . Plz suggest. Regards, Aamir Shahzad -- 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 Group query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Funny video about beautiful children around the world
Funny video about beautiful children around the worldhttp://i-do-you-know-i.blogspot.com/2012/04/blog-post_15.html -- 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$$ Funny video about beautiful children around the world
Hi, Please follow below mentioned forum rule 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. On Mon, Apr 16, 2012 at 10:58 PM, Do You Know doyouknow...@gmail.comwrote: Funny video about beautiful children around the worldhttp://i-do-you-know-i.blogspot.com/2012/04/blog-post_15.html -- 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 -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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$$ Group Total
Hi To Get Summary , you can just filter in 2nd Column on Total and Copy paste the Visible cells only to anywhere. Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Aamir Shahzad Sent: Apr/Mon/2012 10:53 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Group Total Dear Group, 1) I want to get the total of group in summary, please review the sheet and suggest the formula. 2) Secondly how to sort the entire groups rows (without describing the order of inner rows) of groups in ascending descending orders if groups are more then 100 . Plz suggest. Regards, Aamir Shahzad -- 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$$ Reg-Convert this no to text
thanks Rajan for this useful addin. what is the short key of add in window? Aamir Shahzad On Mon, Apr 16, 2012 at 6:22 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Install the attached Add-in Hope it will help to get desired You can use =InWord(A1) to convert the digit in Number.. but I was wrote it to convert any amount to Text like 100 to “Rs ONE Hundred Only” so you can use =SUBSTITUTE(SUBSTITUTE(InWord(A1),Rs,),Only,) Rajan. *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Devendra Sahay *Sent:* Apr/Mon/2012 11:46 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Reg-Convert this no to text Hi..Experts.. i m facing a problem to convert a number in text, so kindly help me on this problem -- P Before printing, think about the environment. Devendra sahay -- 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 -- Regards, Aamir Shahzad -- 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$$ Group Total
this is easy to copy paste but I need the formula for this if groups in summary are not in order. Plz also provide the solution of second query. Thanks Aamir Shahzad On Mon, Apr 16, 2012 at 10:35 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi To Get Summary , you can just filter in 2nd Column on Total and Copy paste the Visible cells only to anywhere. Rajan *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Aamir Shahzad *Sent:* Apr/Mon/2012 10:53 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Group Total Dear Group, 1) I want to get the total of group in summary, please review the sheet and suggest the formula. 2) Secondly how to sort the entire groups rows (without describing the order of inner rows) of groups in ascending descending orders if groups are more then 100 . Plz suggest. Regards, Aamir Shahzad -- 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 -- Regards, Aamir Shahzad -- 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$$ Group Total
Hi Please see the attached Sheet.. I Add a helper column in main Data sheet to sort the Groups . Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Aamir Shahzad Sent: Apr/Mon/2012 11:15 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Group Total this is easy to copy paste but I need the formula for this if groups in summary are not in order. Plz also provide the solution of second query. Thanks Aamir Shahzad On Mon, Apr 16, 2012 at 10:35 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Hi To Get Summary , you can just filter in 2nd Column on Total and Copy paste the Visible cells only to anywhere. Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Aamir Shahzad Sent: Apr/Mon/2012 10:53 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Group Total Dear Group, 1) I want to get the total of group in summary, please review the sheet and suggest the formula. 2) Secondly how to sort the entire groups rows (without describing the order of inner rows) of groups in ascending descending orders if groups are more then 100 . Plz suggest. Regards, Aamir Shahzad -- 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 -- Regards, Aamir Shahzad -- 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 Copy of Group query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Need help with code to automate copy/paste
or UN tested Sub Macro1()’adjust your destination range azz1 dim I as long For i = 9 To Range(azz1).Column step 7 Range(A1).Copy Cells(1, i) Next End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: ashish koul Sent: Monday, April 16, 2012 11:26 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need help with code to automate copy/paste try something like this On Mon, Apr 16, 2012 at 9:51 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Hi Erick, I Will glad to help but can you please explain more what you actually want? I mean what will be the input and output.? Rajan. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Erick C Sent: Apr/Mon/2012 09:43 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help with code to automate copy/paste Hi everybody! I am hoping someone may be able to jelp me out. I am trying to write a macro that will take a formula that i write and copy it, move to the right seven spaces, paste, move seven spaces again, paste, ect.. Right now I need to have the formula pasted all of the way through column AZZ. Does anybody have anything made that I can tweak, or can anybody point me in a direction to find anything. I am quite the novice when it comes to writing VBA from scratch, but I am sure I can get something going if I am given something to start with. Your help is appreciated!! Thanks! Erick -- 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 -- Regards Ashish Koul http://www.excelvbamacros.com/ http://www.accessvbamacros.com/ P Before printing, think about the environment. -- 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$$ Group Total
I had the same idea for sorting the group data, well what about the summary for get the total only if the heading of format is same. Aamir Shahzad On Mon, Apr 16, 2012 at 11:09 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Hi Please see the attached Sheet.. I Add a helper column in main Data sheet to sort the Groups . Rajan. *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Aamir Shahzad *Sent:* Apr/Mon/2012 11:15 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Group Total this is easy to copy paste but I need the formula for this if groups in summary are not in order. Plz also provide the solution of second query. Thanks Aamir Shahzad On Mon, Apr 16, 2012 at 10:35 PM, Rajan_Verma rajanverma1...@gmail.com wrote: Hi To Get Summary , you can just filter in 2nd Column on Total and Copy paste the Visible cells only to anywhere. Rajan *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Aamir Shahzad *Sent:* Apr/Mon/2012 10:53 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Group Total Dear Group, 1) I want to get the total of group in summary, please review the sheet and suggest the formula. 2) Secondly how to sort the entire groups rows (without describing the order of inner rows) of groups in ascending descending orders if groups are more then 100 . Plz suggest. Regards, Aamir Shahzad -- 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 -- Regards, Aamir Shahzad -- 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$$ Why is usedrange here returning the entire spreadsheet?
An older email I don't think you got a direct reply to... With respect to your statement that the non intersect version below will not always work. You said it is because usedrange does not always start at A1 (it will for this case but still agree bad practice to count on it that being the case.). In your case it works because you are using usedrange for the entire sheet and return wherever that intersects the data column even if that column of data is not starting at the first row? But my case if the data does not start at the first row may still work but not be efficient because of a lot of wasted empty cells at the top? No, using an absolute column number (one that corresponds with the entire worksheet) to refer to a column relative to a specific range (the UsedRange in this instance) will plain select the wrong column unless the first column of the range happens to be the first column of the worksheet. You'd have the same issue addressing rows of a range using row numbers relative to the worksheet unless the range happens to start in the first row of the worksheet. This applies whether you use the Columns property of the range, the Rows property, the Cells property, the Range property, or the Offset property -- all of which are relative to the range applied to. The intersect method allows use of absolute worksheet references to select two ranges (in this case a column of the worksheet and the UsedRange of the worksheet), and then return a range where those two ranges overlap. You could use a really inefficient line of code to calculate the relative column/row/range from the absolute column/row/range you already know, and then use the Columns/Rows/Cells/Range/Offset property of the range to return the subrange of interest, but it just doesn't make sense to do so. It sounds like your UsedRange happens to start in A1, making absolute and relative row and column numbers the same, but I recommend learning to do it right.. Should you deside to change your worksheet layout (or someone else does) and have, say, a blank column where column A is now, your code will select the wrong column by 1, and debugging to find out why might be difficult, since even when the variables specify the correct column numbers, the code will fail. Set InputRange = Application.Intersect(Sheets(ContangoSource).UsedRange, Sheets(ContangoSource).Columns(ConDate)) with this Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Monday, April 09, 2012 8:31 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? All for the education. Keep it coming. Once I have things working I will go back and try to make the program more efficient and elegant. The statement below does not work if you remove the .value. Back to the not finding the match function error. WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) With respect to your statement that the non intersect version below will not always work. You said it is because usedrange does not always start at A1 (it will for this case but still agree bad practice to count on it that being the case.). In your case it works because you are using usedrange for the entire sheet and return wherever that intersects the data column even if that column of data is not starting at the first row? But my case if the data does not start at the first row may still work but not be efficient because of a lot of wasted empty cells at the top? Set InputRange = Application.Intersect(Sheets(ContangoSource).UsedRange, Sheets(ContangoSource).Columns(ConDate)) with this Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) = On Mon, Apr 9, 2012 at 3:38 AM, Asa Rossoff a...@lovetour.info wrote: contangoindex = _ WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) RE: passing an array like this, without respect to using Intersect; Since WorksheetFunction.Match accepts an actual range even though Help describes the argument as an array, it may well perform best if you just pass the range reference (no .value at the end). That way it can decide how to handle the data best for itself. The most important issue was to limit the size of the range properly. -Original Message- From: Asa Rossoff [mailto:a...@lovetour.info] Sent: Monday, April 09, 2012 2:35 AM To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? This works. Is there a reason why it is not better? Yes. It won't always work. RANGE.Columns is relative to that range (as is RANGE.Rows,
$$Excel-Macros$$ watch does not break and can filling spreadsheet be made to scroll?
I set a watch with maxrow=111 and set to break if true.Program runs and in the watch window shows maxrow=111though also some odd message about out of context but it does not break. Program runs to the end. Thoughts? Also while the program is running it is filliing in several columns of a sheet. Is there a way to have the sheet scroll while filling so the filling rows are visible? -- 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: Group Total
Hello Aamir, Assume always will have 'TOTAL' in the bottom of each group, If so you can use VLOOKUP. See the attached. For the sorting is an Array Formula, so must be with CSE. I have added some dummy data for various groups. Use dynamic range, if you have more data. ___ 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 Copy_Group_query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Creation of dashboard
*Hi hilary, That is same what i expect, Very Thanks for that, Can you please tell me is there any software or tricks to learn all those things ? Thank you very much. !! * Thanks Regards,* * *Deba Ranjan P*** On Mon, Apr 16, 2012 at 7:15 PM, hilary lomotey resp...@gmail.com wrote: hi Deba The file is to big to send to group mail, but i hope this is what you want. -- 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$$ watch does not break and can filling spreadsheet be made to scroll?
Hi Howard, The watch should be setup like this, in the Add Watch window: Expression:maxrow = 111 Context - Procedure: (procedure with maxrow in scope) Module: (module with above procedure) Watch Type: Break When Value Is True If you look in the watch window after creating the above watch, and code is not currently executing, you will see: Expression Value Type Context maxrow = 111 Out of context Empty Modulename.Procedurename It just reflects the settings you used when setting up the watch. While code is running, if you are in break mode, you'll see the value of the expression maxrow = 111 (which will be True or False) in the Value column of the Watch Window as long as the listed context is in the current call stack (currently executing procedures). Code execution should stop as soon as the expression evaluates to True. Also while the program is running it is filliing in several columns of a sheet. Is there a way to have the sheet scroll while filling so the filling rows are visible? Yes, but you should realize it will slow things down considerably. If it is a lot of cells getting filled in, it will take much longer, yet you will probably still end up scrolling faster than they can read. Some methods of scrolling: .RANGE.Show - scrolls so cell represented by RANGE is in center of window pane; doesn't activate it. Fails if RANGE is not in active document. .RANGE.Select - in the case of a single cell, scrolls to center of window pane; activates cell (slower) .WINDOW.ScrollRow, .ScrollColumn (also PANE.ScrollRow, .ScrollColumn) - scroll so selected row or column is at upper-left of WINDOW or PANE. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Monday, April 16, 2012 5:47 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ watch does not break and can filling spreadsheet be made to scroll? I set a watch with maxrow=111 and set to break if true.Program runs and in the watch window shows maxrow=111though also some odd message about out of context but it does not break. Program runs to the end. Thoughts? Also while the program is running it is filliing in several columns of a sheet. Is there a way to have the sheet scroll while filling so the filling rows are visible? -- 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: Creation of dashboard
Hi Deba, Perhaps some of Chandoo's dashboard resources are of interest. He also offers online classes and seminars at various real worl locations: http://chandoo.org/wp/excel-dashboards/ Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deba Ranjan Sent: Monday, April 16, 2012 9:05 PM To: excel-macros@googlegroups.com Cc: hilary lomotey Subject: $$Excel-Macros$$ Re: Creation of dashboard Hi hilary, That is same what i expect, Very Thanks for that, Can you please tell me is there any software or tricks to learn all those things ? Thank you very much. !! Thanks Regards, Deba Ranjan P On Mon, Apr 16, 2012 at 7:15 PM, hilary lomotey resp...@gmail.com wrote: hi Deba The file is to big to send to group mail, but i hope this is what you want. -- 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
