$$Excel-Macros$$ Formula to find the month btwn the financial year
Dear team, Need a help on formula to find the month btwn the years.. pls find the attachement. Note: its should calculate from Apr - Mar I tried but I not able to get the correct one. Thanks & Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Sample data.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Re: VBA
Dear Paul, As I worked on this sheet it is the worst format of this sheet.as you can see some columns are merge and some are hide I want a clear report. I know that vba will help here. I want that cells should be unmerge and delete blank columns after that and proper format for this report. If you have any idea to achieve this it would be great help. On Thursday, November 20, 2014 4:59:00 PM UTC+5:30, Mandeep Baluja wrote: > > Dear All, > > I need to work on Data sheet which contains all the cells in text format. > basically its a timing sheet. I want to change the format of all the time > values in correct format and wish to highlight those cells where people > come after 10:00 am .Hope you can understand this by going through the > sheet.Looking for a Macro which can correct the format of time > automatically. > > Regards, > Mandeep Baluja > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Macro for send mail
Dear Mr. Ladmanan, Pls do send an attachment. Warm Regards, Gawli Anil On Thu, Nov 20, 2014 at 11:53 PM, Laxmanan M wrote: > Am not gud at macros and am in learning phase.But i have been assigned to > complete this task.So am roaming around the web.Please paul help me to be > done this macro code > > lax > > On Thursday, November 20, 2014 10:39:07 PM UTC+5:30, Laxmanan M wrote: >> >> hi All, >> >> Please see the attached file as requested. What the outcomes I’m after are >> requested below. >> >> >> >> When column AJ (Name of successful candidate) is populated & AK(source of >> hire is) is either populated with either External or Agency then I’d like an >> e-mail to be sent (unsure of the e-mail address to date, I’ll add the e-mail >> address in later) >> >> >> >> The subject will contain New Start, the name of the candidate for example >> Joe Bloggs & their Start Date (column AN) >> >> >> >> The body of the e-mail doesn’t need to contain anything, if it can contain >> any of the above then great, if not don’t worry as the subject field will be >> enough. >> >> >> >> >> Thanks, >> >> laxman > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro for send mail
Am not gud at macros and am in learning phase.But i have been assigned to complete this task.So am roaming around the web.Please paul help me to be done this macro code lax On Thursday, November 20, 2014 10:39:07 PM UTC+5:30, Laxmanan M wrote: > > hi All, > > Please see the attached file as requested. What the outcomes I’m after are > requested below. > > > > When column AJ (Name of successful candidate) is populated & AK(source of > hire is) is either populated with either External or Agency then I’d like > an e-mail to be sent (unsure of the e-mail address to date, I’ll add the > e-mail address in later) > > > > The subject will contain New Start, the name of the candidate for example > Joe Bloggs & their Start Date (column AN) > > > > The body of the e-mail doesn’t need to contain anything, if it can contain > any of the above then great, if not don’t worry as the subject field will > be enough. > > > > > Thanks, > > laxman > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Macro for send mail
Are you wanting someone to write the entire macro or are you simply needing help in automating the email? Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Laxmanan M >To: excel-macros@googlegroups.com >Sent: Thursday, November 20, 2014 12:37 PM >Subject: $$Excel-Macros$$ Re: Macro for send mail > > > >Am using Normal MS Outlook application for sending mails.. > > >Thanks, >Lax > > > > > >On Thursday, November 20, 2014 10:39:07 PM UTC+5:30, Laxmanan M wrote: >hi All, >> >> >>Please see the attached file as requested. What the outcomes I’m after are >>requested below. >> >>When column AJ (Name of successful candidate) is populated & AK(source of >>hire is) is either populated with either External or Agency then I’d like an >>e-mail to be sent (unsure of the e-mail address to date, I’ll add the e-mail >>address in later) >> >>The subject will contain New Start, the name of the candidate for example Joe >>Bloggs & their Start Date (column AN) >> >>The body of the e-mail doesn’t need to contain anything, if it can contain >>any of the above then great, if not don’t worry as the subject field will be >>enough. >> >> >> >>Thanks, >>laxman >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro for send mail
Am using Normal MS Outlook application for sending mails.. Thanks, Lax On Thursday, November 20, 2014 10:39:07 PM UTC+5:30, Laxmanan M wrote: > > hi All, > > Please see the attached file as requested. What the outcomes I’m after are > requested below. > > > > When column AJ (Name of successful candidate) is populated & AK(source of > hire is) is either populated with either External or Agency then I’d like > an e-mail to be sent (unsure of the e-mail address to date, I’ll add the > e-mail address in later) > > > > The subject will contain New Start, the name of the candidate for example > Joe Bloggs & their Start Date (column AN) > > > > The body of the e-mail doesn’t need to contain anything, if it can contain > any of the above then great, if not don’t worry as the subject field will > be enough. > > > > > Thanks, > > laxman > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Macro for send mail
What email application are you using? Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Laxmanan M >To: excel-macros@googlegroups.com >Sent: Thursday, November 20, 2014 12:09 PM >Subject: $$Excel-Macros$$ Macro for send mail > > > >hi All, > > >Please see the attached file as requested. What the outcomes I’m after are >requested below. > >When column AJ (Name of successful candidate) is populated & AK(source of hire >is) is either populated with either External or Agency then I’d like an e-mail >to be sent (unsure of the e-mail address to date, I’ll add the e-mail address >in later) > >The subject will contain New Start, the name of the candidate for example Joe >Bloggs & their Start Date (column AN) > >The body of the e-mail doesn’t need to contain anything, if it can contain any >of the above then great, if not don’t worry as the subject field will be >enough. > > > >Thanks, >laxman -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Error in VBA
Hmm...
I wouldn't mind seeing the actual code (you could email me directly instead of
the group)
and I could see if there's anything I could see to streamline the code.
but..
these if() statements seem to be all checking for the same file:
\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker - Exec.mhtml
and checking to see what day TODAY is.
That approach MAY be best in your circumstances, but it seems inefficient.
On Monday, (weekday(date) = 1)
you will check to see if the file exists, then execute the
Range("P4").value = "X" line.
Then, even though you KNOW that the weekday won't be true for the rest of the
tests,
the code STILL checks to see if the file exists 5 more times!
What I would suggest is to test for the filename ONCE:
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") then
then use an IF/ElseIf/Endif statement
or a Select Case statement.
also, VBA has some system variables that make the code easier to read:
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") Then
If Weekday(Date) = vbMonday Then
Range("P4").Value = "X"
ElseIf Weekday(Date) = vbTuesday Then
Range("Q4").Value = "X"
ElseIf Weekday(Date) = vbWednesday Then
Range("R4").Value = "X"
ElseIf Weekday(Date) = vbThursday Then
Range("S4").Value = "X"
ElseIf Weekday(Date) = vbFriday Then
Range("T4").Value = "X"
ElseIf Weekday(Date) = vbSaturday Then
Range("U4").Value = "X"
End If
End If
I know this probably wouldn't apply in your case, because you have lots of
other code,
but if all you're wanting to do is put an "X" in a column representing the day
of the week,
keep in mind that column "P" is column #16.
If "P" is equivalent to Monday, and vbMonday is day #2,
then on Monday, weekday(date) + 14 would = 16, or column "P".
so you could use:
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") Then
Cells(4, Weekday(Date) + 14).Value = "X"
End If
but then again, this may not be the logic you're trying to encode!
let me know if I can help.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Thursday, November 20, 2014 9:32 AM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>one report example is following
>
>
>If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
>Exec.mhtml") And _
> Weekday(Date) = 2 Then
>
>
>
>Range("P4").Value = "X"
>
>
>
>
>
>
>End If
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 3 Then
>
>
>
>Range("Q4").Value = "X"
>
>
>
>
>
>
>
>
>End If
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 4 Then
>
>
>
>Range("R4").Value = "X"
>
>
>
>
>
>
>End If
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 5 Then
>
>
>
>Range("S4").Value = "X"
>
>
>
>
>
>
>End If
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 6 Then
>
>
>
>Range("T4").Value = "X"
>
>
>
>
>
>
>End If
>
>
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 7 Then
>
>
>
>Range("U4").Value = "X"
>
>
>
>
>
>
>End If
>
>
>and it goes on and on.
>
>
>
>
>
>
>there is a specific box for each report for each day.. the above is code if
>just for one report , just imagine how long the code is for 78 reports :)
>
>
>On Thu, Nov 20, 2014 at 9:30 AM, USMAN TARIQ wrote:
>
>actually the code is really really big :) this is just for example that i
>gave , i have like 78 reports that needed to be scored card check , and every
>report is everyday except Sunday , so its a really long code
>>
>>
>>On Thu, Nov 20, 2014 at 9:28 AM, Paul Schreiner
>>wrote:
>>
>>Well, that answers that question.
>>>you have it in the ThisWorkbook module, which is correct.
>>>
>>>Which means that it probably actually RAN, but
>>>
>>>Weekday(Date) = 2
>>>
>>>will return "false" because today (thursday) is Weekday #5
>>>
>>>
>>>
>>>Paul
>>>-
>>>“Do all the good you can,
>>>By all the means you can,
>>>In all the ways you can,
>>>In all the places you can,
>>>At all the times you can,
>>>To all the people you can,
>>>As long as ever you can.” - John
Wesley
>>>-
>>>
>>>
>>> From: USMAN TARIQ
To: excel-macros@googlegroups.com
Sent: Thursday, November 20, 2014 9:19 AM
Subj
$$Excel-Macros$$ BY using VBA code, File transfer in Filezilla
Please help to provide the solution in VBA to transfer a file from local drive to Remote drive by Filezilla(Tool) -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ To create a sheet with macro and overwrite on next attempt
Thank you everyone. On Fri, Nov 14, 2014 at 5:42 PM, Ashish Kumar wrote: > Hi Pravin, > > PFA!! > > > Edit your data in Pending CN and Highlight the row which you want to paste > in Party Wise and hit the macro. > > > > Regards > Ashish > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/mMYOkNKhtWs/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Error in VBA
may be the code could be smaller , but being a Starter in VBA , thats the
only thing that came to my mind , and the time was also limited , But
thanks to you , you make it complete in such a easy way .
On Thu, Nov 20, 2014 at 9:32 AM, USMAN TARIQ
wrote:
> one report example is following
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 2 Then
>
>
>
> Range("P4").Value = "X"
>
>
>
> End If
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 3 Then
>
>
>
> Range("Q4").Value = "X"
>
>
>
>
> End If
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 4 Then
>
>
>
> Range("R4").Value = "X"
>
>
>
> End If
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker
> - Exec.mhtml") And _
>Weekday(Date) = 5 Then
>
>
>
> Range("S4").Value = "X"
>
>
>
> End If
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 6 Then
>
>
>
> Range("T4").Value = "X"
>
>
>
> End If
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 7 Then
>
>
>
> Range("U4").Value = "X"
>
>
>
> End If
>
> and it goes on and on.
>
>
>
> there is a specific box for each report for each day.. the above is code
> if just for one report , just imagine how long the code is for 78 reports :)
>
> On Thu, Nov 20, 2014 at 9:30 AM, USMAN TARIQ
> wrote:
>
>> actually the code is really really big :) this is just for example that
>> i gave , i have like 78 reports that needed to be scored card check , and
>> every report is everyday except Sunday , so its a really long code
>>
>> On Thu, Nov 20, 2014 at 9:28 AM, Paul Schreiner
>> wrote:
>>
>>> Well, that answers that question.
>>> you have it in the ThisWorkbook module, which is correct.
>>>
>>> Which means that it probably actually RAN, but
>>>
>>> Weekday(Date) = 2
>>>
>>> will return "false" because today (thursday) is Weekday #5
>>>
>>>
>>>
>>> *Paul*
>>> -
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *“Do all the good you can,By all the means you can,In all the ways you
>>> can,In all the places you can,At all the times you can,To all the people
>>> you can,As long as ever you can.” - John Wesley*
>>> -
>>>
>>>*From:* USMAN TARIQ
>>> *To:* excel-macros@googlegroups.com
>>> *Sent:* Thursday, November 20, 2014 9:19 AM
>>>
>>> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>>>
>>> Thanks again for well versed explanation ! :) , what i did i just copied
>>> the whole code from the sheet module
>>>
>>> and then i put Private Sub Workbook_Open() in the ThisWorkBook by adding
>>> the code . It looks like the following
>>>
>>> Private Sub Workbook_Open()
>>> 'Private Sub TestFileExistence()
>>>
>>> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker
>>> - Exec.mhtml") And _
>>>Weekday(Date) = 2 Then
>>>
>>> Range("P4").Value = "X"
>>>
>>>
>>> End If
>>> End Sub
>>>
>>> On Wed, Nov 19, 2014 at 3:27 PM, Paul Schreiner
>>> wrote:
>>>
>>> What module did you put it in?
>>> (it should be in a sheet module)
>>>
>>> also, the part that says: Weekday(Date)
>>> today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
>>>
>>>
>>> also, for debugging purposes:
>>> in the VBE window, right-click on the menu area and turn on the Edit and
>>> Debug icon sets.
>>> I usually "park" them on the top menu row.
>>>
>>> the "step into" button (to the right of the "hand" icon)
>>> will allow you to step one line at a time through a macro.
>>>
>>> in a macro:
>>> click in the vertical grey border to the left of an EXECUTABLE macro
>>> line (not a comment).
>>> a small circle should appear, and the line should be highlighted in the
>>> same color.
>>>
>>>
>>> This is setting a "breakpoint".
>>>
>>> When a macro runs, it will stop at this point.
>>> you can then use the menu buttons (or function keys) to step through the
>>> macro from this point.
>>>
>>> you can also right-click variables or even things like "weekday(date)"
>>> and select "Add watch".
>>>
>>> this will put the "run-time" value of the variable or function in a
>>> Watch Window.
>>>
>>> During debug breakpoint, this window will show you the value of the
>>> variable or function.
>>>
>>> so, if you put a watch on weekday(date), as you step through the macro,
>>> it will show that the value is 4 (not 2)
>>>
>>> *Paul*
>>> -
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *“Do all the good you can,By all the means you can,In all the ways you
>>> can,In all the places you can,At all the times you can,To all the people
>>> you can,As long as ever you can.” - John Wesley*
>>> -
>>>
>>>*From:* USMAN TARIQ
Re: $$Excel-Macros$$ Error in VBA
one report example is following
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 2 Then
Range("P4").Value = "X"
End If
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 3 Then
Range("Q4").Value = "X"
End If
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 4 Then
Range("R4").Value = "X"
End If
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 5 Then
Range("S4").Value = "X"
End If
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 6 Then
Range("T4").Value = "X"
End If
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 7 Then
Range("U4").Value = "X"
End If
and it goes on and on.
there is a specific box for each report for each day.. the above is code if
just for one report , just imagine how long the code is for 78 reports :)
On Thu, Nov 20, 2014 at 9:30 AM, USMAN TARIQ
wrote:
> actually the code is really really big :) this is just for example that i
> gave , i have like 78 reports that needed to be scored card check , and
> every report is everyday except Sunday , so its a really long code
>
> On Thu, Nov 20, 2014 at 9:28 AM, Paul Schreiner
> wrote:
>
>> Well, that answers that question.
>> you have it in the ThisWorkbook module, which is correct.
>>
>> Which means that it probably actually RAN, but
>>
>> Weekday(Date) = 2
>>
>> will return "false" because today (thursday) is Weekday #5
>>
>>
>>
>> *Paul*
>> -
>>
>>
>>
>>
>>
>>
>>
>> *“Do all the good you can,By all the means you can,In all the ways you
>> can,In all the places you can,At all the times you can,To all the people
>> you can,As long as ever you can.” - John Wesley*
>> -
>>
>>*From:* USMAN TARIQ
>> *To:* excel-macros@googlegroups.com
>> *Sent:* Thursday, November 20, 2014 9:19 AM
>>
>> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>>
>> Thanks again for well versed explanation ! :) , what i did i just copied
>> the whole code from the sheet module
>>
>> and then i put Private Sub Workbook_Open() in the ThisWorkBook by adding
>> the code . It looks like the following
>>
>> Private Sub Workbook_Open()
>> 'Private Sub TestFileExistence()
>>
>> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker
>> - Exec.mhtml") And _
>>Weekday(Date) = 2 Then
>>
>> Range("P4").Value = "X"
>>
>>
>> End If
>> End Sub
>>
>> On Wed, Nov 19, 2014 at 3:27 PM, Paul Schreiner
>> wrote:
>>
>> What module did you put it in?
>> (it should be in a sheet module)
>>
>> also, the part that says: Weekday(Date)
>> today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
>>
>>
>> also, for debugging purposes:
>> in the VBE window, right-click on the menu area and turn on the Edit and
>> Debug icon sets.
>> I usually "park" them on the top menu row.
>>
>> the "step into" button (to the right of the "hand" icon)
>> will allow you to step one line at a time through a macro.
>>
>> in a macro:
>> click in the vertical grey border to the left of an EXECUTABLE macro line
>> (not a comment).
>> a small circle should appear, and the line should be highlighted in the
>> same color.
>>
>>
>> This is setting a "breakpoint".
>>
>> When a macro runs, it will stop at this point.
>> you can then use the menu buttons (or function keys) to step through the
>> macro from this point.
>>
>> you can also right-click variables or even things like "weekday(date)"
>> and select "Add watch".
>>
>> this will put the "run-time" value of the variable or function in a Watch
>> Window.
>>
>> During debug breakpoint, this window will show you the value of the
>> variable or function.
>>
>> so, if you put a watch on weekday(date), as you step through the macro,
>> it will show that the value is 4 (not 2)
>>
>> *Paul*
>> -
>>
>>
>>
>>
>>
>>
>>
>> *“Do all the good you can,By all the means you can,In all the ways you
>> can,In all the places you can,At all the times you can,To all the people
>> you can,As long as ever you can.” - John Wesley*
>> -
>>
>>*From:* USMAN TARIQ
>> *To:* excel-macros@googlegroups.com
>> *Sent:* Wednesday, November 19, 2014 3:02 PM
>>
>> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>>
>> Private Sub Worksheet_Activate()
>>
>> 'Private Sub TestFileExistence()
>>
>> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker
>> - Exec.mhtml") And _
>>Weekday(Date) = 2 Then
>>
>> Range("P4").Value = "X"
>>
>> End If
>> End Sub
>>
>> *i put like this , its not doing any thing :(*
>>
>> On Wed, Nov 19, 2014 at 2
Re: $$Excel-Macros$$ Error in VBA
actually the code is really really big :) this is just for example that i
gave , i have like 78 reports that needed to be scored card check , and
every report is everyday except Sunday , so its a really long code
On Thu, Nov 20, 2014 at 9:28 AM, Paul Schreiner
wrote:
> Well, that answers that question.
> you have it in the ThisWorkbook module, which is correct.
>
> Which means that it probably actually RAN, but
>
> Weekday(Date) = 2
>
> will return "false" because today (thursday) is Weekday #5
>
>
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>*From:* USMAN TARIQ
> *To:* excel-macros@googlegroups.com
> *Sent:* Thursday, November 20, 2014 9:19 AM
>
> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>
> Thanks again for well versed explanation ! :) , what i did i just copied
> the whole code from the sheet module
>
> and then i put Private Sub Workbook_Open() in the ThisWorkBook by adding
> the code . It looks like the following
>
> Private Sub Workbook_Open()
> 'Private Sub TestFileExistence()
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 2 Then
>
> Range("P4").Value = "X"
>
>
> End If
> End Sub
>
> On Wed, Nov 19, 2014 at 3:27 PM, Paul Schreiner
> wrote:
>
> What module did you put it in?
> (it should be in a sheet module)
>
> also, the part that says: Weekday(Date)
> today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
>
>
> also, for debugging purposes:
> in the VBE window, right-click on the menu area and turn on the Edit and
> Debug icon sets.
> I usually "park" them on the top menu row.
>
> the "step into" button (to the right of the "hand" icon)
> will allow you to step one line at a time through a macro.
>
> in a macro:
> click in the vertical grey border to the left of an EXECUTABLE macro line
> (not a comment).
> a small circle should appear, and the line should be highlighted in the
> same color.
>
>
> This is setting a "breakpoint".
>
> When a macro runs, it will stop at this point.
> you can then use the menu buttons (or function keys) to step through the
> macro from this point.
>
> you can also right-click variables or even things like "weekday(date)" and
> select "Add watch".
>
> this will put the "run-time" value of the variable or function in a Watch
> Window.
>
> During debug breakpoint, this window will show you the value of the
> variable or function.
>
> so, if you put a watch on weekday(date), as you step through the macro, it
> will show that the value is 4 (not 2)
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>*From:* USMAN TARIQ
> *To:* excel-macros@googlegroups.com
> *Sent:* Wednesday, November 19, 2014 3:02 PM
>
> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>
> Private Sub Worksheet_Activate()
>
> 'Private Sub TestFileExistence()
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 2 Then
>
> Range("P4").Value = "X"
>
> End If
> End Sub
>
> *i put like this , its not doing any thing :(*
>
> On Wed, Nov 19, 2014 at 2:59 PM, USMAN TARIQ
> wrote:
>
> Private Sub Worksheet_Activate()
>
> End Sub
>
> so do i need to put my code between the above two ?
>
> i put the
>
> On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
> wrote:
>
> Ah yes...
> More tricks of the trade.
> Soon, you'll be making this look easy!
>
> first:
> As I mentioned at the beginning of the last email, the biggest struggle
> for someone new is "terminology and communication".
>
> The PROBLEM you were having wasn't "difficult".
> what was difficult was wading through what you wanted to do and
> "translate" it to standard terminology and getting it into perspective.
>
> you had stated that you were using something called
> FileFolderExists()
>
> that's not "standard VBA" but a custom function that there is No WAY we
> could know what that did.
>
> So I think most people simply decided that they didn't want to try to
> untangle it.
>
> not that it was your FAULT, it's just that you have to learn to step back
> and try to learn to describe your situation as if you're talking to someone
> that isn't doing the EXACT SAME job you are, but something similar.
>
>
> and now for the TRICKS!
>
>
> When you're in the VB Editor, double-click on one of the Sheet modules
> like the one called Sheet1()
>
> at the top of the edit pane/panel, notice that there are two pull-down
> menus.
>
> The on
Re: $$Excel-Macros$$ Error in VBA
Well, that answers that question.
you have it in the ThisWorkbook module, which is correct.
Which means that it probably actually RAN, but
Weekday(Date) = 2
will return "false" because today (thursday) is Weekday #5
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Thursday, November 20, 2014 9:19 AM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>Thanks again for well versed explanation ! :) , what i did i just copied the
>whole code from the sheet module
>
>
>and then i put Private Sub Workbook_Open() in the ThisWorkBook by adding the
>code . It looks like the following
>
>
>Private Sub Workbook_Open()
>'Private Sub TestFileExistence()
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 2 Then
>
> Range("P4").Value = "X"
>
>
>
>
>End If
>End Sub
>
>
>On Wed, Nov 19, 2014 at 3:27 PM, Paul Schreiner wrote:
>
>What module did you put it in?
>>(it should be in a sheet module)
>>
>>also, the part that says: Weekday(Date)
>>today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
>>
>>
>>also, for debugging purposes:
>>in the VBE window, right-click on the menu area and turn on the Edit and
>>Debug icon sets.
>>I usually "park" them on the top menu row.
>>
>>the "step into" button (to the right of the "hand" icon)
>>will allow you to step one line at a time through a macro.
>>
>>in a macro:
>>click in the vertical grey border to the left of an EXECUTABLE macro line
>>(not a comment).
>>a small circle should appear, and the line should be highlighted in the same
>>color.
>>
>>
>>This is setting a "breakpoint".
>>
>>When a macro runs, it will stop at this point.
>>you can then use the menu buttons (or function keys) to step through the
>>macro from this point.
>>
>>you can also right-click variables or even things like "weekday(date)" and
>>select "Add watch".
>>
>>this will put the "run-time" value of the variable or function in a Watch
>>Window.
>>
>>During debug breakpoint, this window will show you the value of the variable
>>or function.
>>
>>so, if you put a watch on weekday(date), as you step through the macro, it
>>will show that the value is 4 (not 2)
>>
>>Paul
>>-
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-
>>
>>
>> From: USMAN TARIQ
>>>To: excel-macros@googlegroups.com
>>>Sent: Wednesday, November 19, 2014 3:02 PM
>>>
>>>Subject: Re: $$Excel-Macros$$ Error in VBA
>>>
>>>
>>>
>>>Private Sub Worksheet_Activate()
>>>
>>>
>>>'Private Sub TestFileExistence()
>>>
>>>
>>> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
>>> Exec.mhtml") And _
>>> Weekday(Date) = 2 Then
>>>
>>> Range("P4").Value = "X"
>>>
>>>
>>>End If
>>>
>>>End Sub
>>>
>>>
>>>i put like this , its not doing any thing :(
>>>
>>>
>>>On Wed, Nov 19, 2014 at 2:59 PM, USMAN TARIQ
>>>wrote:
>>>
>>>Private Sub Worksheet_Activate()
End Sub
so do i need to put my code between the above two ?
i put the
On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
wrote:
Ah yes...
>More tricks of the trade.
>Soon, you'll be making this look easy!
>
>first:
>As I mentioned at the beginning of the last email, the biggest struggle
>for someone new is "terminology and communication".
>
>The PROBLEM you were having wasn't "difficult".
>what was difficult was wading through what you wanted to do and
>"translate" it to standard terminology and getting it into perspective.
>
>you had stated that you were using something called
>FileFolderExists()
>
>that's not "standard VBA" but a custom function that there is No WAY we
>could know what that did.
>
>So I think most people simply decided that they didn't want to try to
>untangle it.
>
>not that it was your FAULT, it's just that you have to learn to step back
>and try to learn to describe your situation as if you're talking to
>someone that isn't doing the EXACT SAME job you are, but something similar.
>
>
>and now for the TRICKS!
>
>
>When you're in the VB Editor, double-click on one of the Sheet modules
>like the one called Sheet1()
>
>at the top of the edit pane/panel, notice that there are two pull-down
>menus.
>
>The one on the left usually says something like "General"
>and th
Re: $$Excel-Macros$$ Error in VBA
Thanks again for well versed explanation ! :) , what i did i just copied
the whole code from the sheet module
and then i put Private Sub Workbook_Open() in the ThisWorkBook by adding
the code . It looks like the following
Private Sub Workbook_Open()
'Private Sub TestFileExistence()
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 2 Then
Range("P4").Value = "X"
End If
End Sub
On Wed, Nov 19, 2014 at 3:27 PM, Paul Schreiner
wrote:
> What module did you put it in?
> (it should be in a sheet module)
>
> also, the part that says: Weekday(Date)
> today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
>
>
> also, for debugging purposes:
> in the VBE window, right-click on the menu area and turn on the Edit and
> Debug icon sets.
> I usually "park" them on the top menu row.
>
> the "step into" button (to the right of the "hand" icon)
> will allow you to step one line at a time through a macro.
>
> in a macro:
> click in the vertical grey border to the left of an EXECUTABLE macro line
> (not a comment).
> a small circle should appear, and the line should be highlighted in the
> same color.
>
>
> This is setting a "breakpoint".
>
> When a macro runs, it will stop at this point.
> you can then use the menu buttons (or function keys) to step through the
> macro from this point.
>
> you can also right-click variables or even things like "weekday(date)" and
> select "Add watch".
>
> this will put the "run-time" value of the variable or function in a Watch
> Window.
>
> During debug breakpoint, this window will show you the value of the
> variable or function.
>
> so, if you put a watch on weekday(date), as you step through the macro, it
> will show that the value is 4 (not 2)
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>*From:* USMAN TARIQ
> *To:* excel-macros@googlegroups.com
> *Sent:* Wednesday, November 19, 2014 3:02 PM
>
> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>
> Private Sub Worksheet_Activate()
>
> 'Private Sub TestFileExistence()
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
>Weekday(Date) = 2 Then
>
> Range("P4").Value = "X"
>
> End If
> End Sub
>
> *i put like this , its not doing any thing :(*
>
> On Wed, Nov 19, 2014 at 2:59 PM, USMAN TARIQ
> wrote:
>
> Private Sub Worksheet_Activate()
>
> End Sub
>
> so do i need to put my code between the above two ?
>
> i put the
>
> On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
> wrote:
>
> Ah yes...
> More tricks of the trade.
> Soon, you'll be making this look easy!
>
> first:
> As I mentioned at the beginning of the last email, the biggest struggle
> for someone new is "terminology and communication".
>
> The PROBLEM you were having wasn't "difficult".
> what was difficult was wading through what you wanted to do and
> "translate" it to standard terminology and getting it into perspective.
>
> you had stated that you were using something called
> FileFolderExists()
>
> that's not "standard VBA" but a custom function that there is No WAY we
> could know what that did.
>
> So I think most people simply decided that they didn't want to try to
> untangle it.
>
> not that it was your FAULT, it's just that you have to learn to step back
> and try to learn to describe your situation as if you're talking to someone
> that isn't doing the EXACT SAME job you are, but something similar.
>
>
> and now for the TRICKS!
>
>
> When you're in the VB Editor, double-click on one of the Sheet modules
> like the one called Sheet1()
>
> at the top of the edit pane/panel, notice that there are two pull-down
> menus.
>
> The one on the left usually says something like "General"
> and the one on the right says something like "(Declarations)"
>
> Now, pull down the left-hand ("General") menu.
> There should only be one selection, called "Worksheet".
> Select it.
>
> Immediately, the editor will create an Event macro called:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> This is a macro that executes whenever you select a different cell on the
> sheet!
> it passes a Range variable called "Target" to the macro.
>
> So, in this macro, if you put something like:
>
> MsgBox Target.Value
>
> whenever you select a cell, it will display the value of the cell in a
> message box.
>
> Now, with "Worksheet" selected in the left box, pull down the right-hand
> menu.
> this is a list of all of the Event macros associated with the Worksheet
> object.
>
> Most names are obvious as to when they would run:
> Activate (when you select the sheet)
> Deactivate (when you LEAVE the sheet)
> BeforeDoubleclick (runs when you double
Re: $$Excel-Macros$$ Re: Extraction desire text data from the cell by formula
Mandeep da, Your UDF function is working fine. it is really nice concept that you did. thanks once again for your valuable time regarding my issue.. i also got a solution from Paul .though currently i did not get any problem, then now i analysis whether any discrepancy is there or not .. if i get any issue i will let you know... thanks ...nur On 11/20/14, Mandeep Baluja wrote: > Hey Nur, > > Kindly find the attached UDF just type the formula in sheet First > percentage/Second percentage it will give to appropriate result. if text > does not contain second percentage value it will give wrong result. > > > > Function Firstpercentage(txt) As String > Dim k() As String > Dim x As Variant > > k() = Split(txt, "%") > Firstpercentage = k(0) > > For i = 1 To Len(Firstpercentage) > b = Mid(Firstpercentage, i, 1) > If Not b Like "[A-Z,a-z]" Then > c = c & b > End If > Next i > > Firstpercentage = c > > > > End Function > > > Function Secondpercentage(txt) As String > Dim k() As String > Dim x As Variant > > k() = Split(txt, "%") > Secondpercentage = k(0) > > For i = 1 To Len(Secondpercentage) > b = Mid(Secondpercentage, i, 1) > If Not b Like "[A-Z,a-z,@]" Then > c = c & b > End If > Next i > > Secondpercentage = c > > > > End Function > > > > > > > On Thursday, November 20, 2014 1:22:52 PM UTC+5:30, Nur wrote: >> >> Dear all, I need to extract the partial contents of a cell. My excel >> sheet contains the data in the cell A:A which is very complex format >> data . form this data , I want to get the data as I color marks form >> B2:G2. Please check the attachments. If I get any partial help I will >> be glad also .. >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Add and remove text box on ever time button click.
OK, I'll take a look at this as soon as I get some things I'm PAID to do taken care of! Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Rupesh Patil >To: excel-macros@googlegroups.com >Sent: Wednesday, November 19, 2014 11:17 PM >Subject: Re: $$Excel-Macros$$ Add and remove text box on ever time button >click. > > > >Hello Paul, > > >Thanks for reply > > >Are you saying that when you hit "Add" >you want to add another row of (5) textboxes? > >Yes, I want to add another row of (5) TextBoxes. > >And when you hit "Remove", it should >remove the row of (5) boxes? > Yes, I want to remove row of (5) tesxtboxex. > > >If you remove the second row, what do you want to have happen to the rest? >(third row move up to row 2 or ??) > > >Yes, third row move up to row 2. > >Is there a limit to the number of rows? > > > Yes, for now limit should be 12 rows of text boxes > > >what happens when you exceed the size of the userform? > > > in that case user form increasable accordingly or scroll bar will work. > > >do you want the macro to increase the size of the userform? > > >I want macro for add textboxes on Add button click every time and remove added >textboxes on Remove button click. > > >Thanks again for reply, Please help > > > > > >On Wed, Nov 19, 2014 at 6:07 PM, Paul Schreiner wrote: > >Your userform currently has (3) rows of(5) textboxes. >> >>Are you saying that when you hit "Add" >>you want to add another row of (5) textboxes? >> >>And when you hit "Remove", it should >>remove the row of (5) boxes? >> >>If you remove the second row, what do you want to have happen to the rest? >>(third row move up to row 2 or ??) >> >>Is there a limit to the number of rows? >>what happens when you exceed the size of the userform? >>do you want the macro to increase the size of the userform? >> >>Paul >>- >>“Do all the good you can, >>By all the means you can, >>In all the ways you can, >>In all the places you can, >>At all the times you can, >>To all the people you can, >>As long as ever you can.” - John Wesley >>- >> >> >> From: Rupesh Patil >>>To: excel-macros@googlegroups.com >>>Sent: Wednesday, November 19, 2014 3:24 AM >>>Subject: Re: $$Excel-Macros$$ Add and remove text box on ever time button >>>click. >>> >>> >>> >>>Please Experts help me out. >>> >>> >>>On Tue, Nov 18, 2014 at 5:07 PM, Rupesh Patil >>>wrote: >>> >>>Hello Experts, any help? On Mon, Nov 17, 2014 at 4:10 PM, Rupesh Patil wrote: Hello experts, > > >I need your help,I have one userform which attached with mail for your >reference, what I need is add text boxes vertical on button every time >click "Add Button" and remove this text boxes every time on "Remove >Button" click. and Add-Remove row accordingly in sheet. > >-- > >Thanks & Regards, >Rupesh Patil, >(9970196189). -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups >"MS EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > -- Thanks & Regards, Rupesh Patil, (9970196189), Ayushman Pvt. Ltd. >>> >>> >>> >>>-- >>> >>>Thanks & Regards, >>>Rupesh Patil, >>>(9970196189), >>>Ayushman Pvt. Ltd. >>> >>> -- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of
Re: $$Excel-Macros$$ Extraction desire text data from the cell by formula
Hi Paul , Yes it works perfectly, it will help a lots. i appreciate your nice job.. thanks...nur On 11/20/14, Paul Schreiner wrote: > Here is a formula solution. > > The macro solution was to use a "custom function". > I'll work on that. > > Paul > - > “Do all the good you can, > By all the means you can, > In all the ways you can, > In all the places you can, > At all the times you can, > To all the people you can, > As long as ever you can.” - John Wesley > - > > >> >> From: Nur Hossain >>To: excel-macros@googlegroups.com >>Cc: Paul Schreiner >>Sent: Thursday, November 20, 2014 7:47 AM >>Subject: Re: $$Excel-Macros$$ Extraction desire text data from the cell by >> formula >> >> >>Hi Paul , >>Thanks for intention to help me . I will appreciate both solution as I >>love to learn both formula & macro solution . actually my data range >>is not fixed so for bigger data range macro is suitable for this . but >>for small data range formula is best . so if you don’t have problem >>then I will be happy to get both solution as it will help me to >>develop my competence level also . Mandeep da is also working for >>solving my issue by formula .. yes I appreciate all of you . >>thanks ..nur >> >> >> >> >> >> >>On 11/20/14, Paul Schreiner wrote: >>> What type of solution are you looking for? >>> That is: >>> If I make a custom function using VBA, would you be able to modify it to >>> suit you? >>> or do you want the solution using Excel formulas? >>> >>> Paul >>> - >>> “Do all the good you can, >>> By all the means you can, >>> In all the ways you can, >>> In all the places you can, >>> At all the times you can, >>> To all the people you can, >>> As long as ever you can.” - John Wesley >>> - >>> >>> From: Nur Hossain To: excel-macros Sent: Thursday, November 20, 2014 2:52 AM Subject: $$Excel-Macros$$ Extraction desire text data from the cell by formula Dear all, I need to extract the partial contents of a cell. My excel sheet contains the data in the cell A:A which is very complex format data . form this data , I want to get the data as I color marks form B2:G2. Please check the attachments. If I get any partial help I will be glad also .. >> >>-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>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) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >>To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros. >>For more options, visit https://groups.google.com/d/optout. >> >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more opt
$$Excel-Macros$$ Re: VBA
Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks Thanks On Thursday, November 20, 2014 7:01:02 PM UTC+5:30, Mandeep Baluja wrote: > > Worked like a charm :) > > Thanks a lot . > > > > On Thursday, November 20, 2014 4:59:00 PM UTC+5:30, Mandeep Baluja wrote: >> >> Dear All, >> >> I need to work on Data sheet which contains all the cells in text format. >> basically its a timing sheet. I want to change the format of all the time >> values in correct format and wish to highlight those cells where people >> come after 10:00 am .Hope you can understand this by going through the >> sheet.Looking for a Macro which can correct the format of time >> automatically. >> >> Regards, >> Mandeep Baluja >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: VBA
Worked like a charm :) Thanks a lot . On Thursday, November 20, 2014 4:59:00 PM UTC+5:30, Mandeep Baluja wrote: > > Dear All, > > I need to work on Data sheet which contains all the cells in text format. > basically its a timing sheet. I want to change the format of all the time > values in correct format and wish to highlight those cells where people > come after 10:00 am .Hope you can understand this by going through the > sheet.Looking for a Macro which can correct the format of time > automatically. > > Regards, > Mandeep Baluja > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Extraction desire text data from the cell by formula
Hey Nur, Kindly find the attached UDF just type the formula in sheet First percentage/Second percentage it will give to appropriate result. if text does not contain second percentage value it will give wrong result. Function Firstpercentage(txt) As String Dim k() As String Dim x As Variant k() = Split(txt, "%") Firstpercentage = k(0) For i = 1 To Len(Firstpercentage) b = Mid(Firstpercentage, i, 1) If Not b Like "[A-Z,a-z]" Then c = c & b End If Next i Firstpercentage = c End Function Function Secondpercentage(txt) As String Dim k() As String Dim x As Variant k() = Split(txt, "%") Secondpercentage = k(0) For i = 1 To Len(Secondpercentage) b = Mid(Secondpercentage, i, 1) If Not b Like "[A-Z,a-z,@]" Then c = c & b End If Next i Secondpercentage = c End Function On Thursday, November 20, 2014 1:22:52 PM UTC+5:30, Nur wrote: > > Dear all, I need to extract the partial contents of a cell. My excel > sheet contains the data in the cell A:A which is very complex format > data . form this data , I want to get the data as I color marks form > B2:G2. Please check the attachments. If I get any partial help I will > be glad also .. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. notice (2)1.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Re: Extraction desire text data from the cell by formula
Dear Nur, Thanks to appreciate. Actually I was doing my office work yes it is possible to reference text inside the formula.But I need time because I have to do my ofc work too :), Soon I will submit it your percentage formula too. On Thursday, November 20, 2014 1:22:52 PM UTC+5:30, Nur wrote: > > Dear all, I need to extract the partial contents of a cell. My excel > sheet contains the data in the cell A:A which is very complex format > data . form this data , I want to get the data as I color marks form > B2:G2. Please check the attachments. If I get any partial help I will > be glad also .. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Extraction desire text data from the cell by formula
Here is a formula solution. The macro solution was to use a "custom function". I'll work on that. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Nur Hossain >To: excel-macros@googlegroups.com >Cc: Paul Schreiner >Sent: Thursday, November 20, 2014 7:47 AM >Subject: Re: $$Excel-Macros$$ Extraction desire text data from the cell by >formula > > >Hi Paul , >Thanks for intention to help me . I will appreciate both solution as I >love to learn both formula & macro solution . actually my data range >is not fixed so for bigger data range macro is suitable for this . but >for small data range formula is best . so if you don’t have problem >then I will be happy to get both solution as it will help me to >develop my competence level also . Mandeep da is also working for >solving my issue by formula .. yes I appreciate all of you . >thanks ..nur > > > > > > >On 11/20/14, Paul Schreiner wrote: >> What type of solution are you looking for? >> That is: >> If I make a custom function using VBA, would you be able to modify it to >> suit you? >> or do you want the solution using Excel formulas? >> >> Paul >> - >> “Do all the good you can, >> By all the means you can, >> In all the ways you can, >> In all the places you can, >> At all the times you can, >> To all the people you can, >> As long as ever you can.” - John Wesley >> - >> >> >>> >>> From: Nur Hossain >>>To: excel-macros >>>Sent: Thursday, November 20, 2014 2:52 AM >>>Subject: $$Excel-Macros$$ Extraction desire text data from the cell by >>> formula >>> >>> >>>Dear all, I need to extract the partial contents of a cell. My excel >>>sheet contains the data in the cell A:A which is very complex format >>>data . form this data , I want to get the data as I color marks form >>>B2:G2. Please check the attachments. If I get any partial help I will >>>be glad also .. >>> > >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. notice_solution_Paul.xlsx Description: Binary data
Re: $$Excel-Macros$$ Extraction desire text data from the cell by formula
Hi Paul , Thanks for intention to help me . I will appreciate both solution as I love to learn both formula & macro solution . actually my data range is not fixed so for bigger data range macro is suitable for this . but for small data range formula is best . so if you don’t have problem then I will be happy to get both solution as it will help me to develop my competence level also . Mandeep da is also working for solving my issue by formula .. yes I appreciate all of you . thanks ..nur On 11/20/14, Paul Schreiner wrote: > What type of solution are you looking for? > That is: > If I make a custom function using VBA, would you be able to modify it to > suit you? > or do you want the solution using Excel formulas? > > Paul > - > “Do all the good you can, > By all the means you can, > In all the ways you can, > In all the places you can, > At all the times you can, > To all the people you can, > As long as ever you can.” - John Wesley > - > > >> >> From: Nur Hossain >>To: excel-macros >>Sent: Thursday, November 20, 2014 2:52 AM >>Subject: $$Excel-Macros$$ Extraction desire text data from the cell by >> formula >> >> >>Dear all, I need to extract the partial contents of a cell. My excel >>sheet contains the data in the cell A:A which is very complex format >>data . form this data , I want to get the data as I color marks form >>B2:G2. Please check the attachments. If I get any partial help I will >>be glad also .. >> -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Extraction desire text data from the cell by formula
Mandeep da, Take my respect ,yes you did excellent job for me . the formula results are 100% correct . please take time for solving % issue. Is it possible to implement reference text inside the formula . if not possible then use another column to keep the “ reference text “ , it might be column H, I ,J, K whatever you likes except the cell B10:B13 . or you can also use another sheet for keeping reference also . it is required due to my input data range is not fixed it might go till A1:A65536. Regards, nur On 11/20/14, Mandeep Baluja wrote: > Try dis kind of formulas. Don't delete the hide rows reference you can > shift it. I will provide you a solution soon for % column too. > > Regards, > Mandeep Baluja > *Excel Specialist. * > > > > On Thursday, November 20, 2014 1:22:52 PM UTC+5:30, Nur wrote: >> >> Dear all, I need to extract the partial contents of a cell. My excel >> sheet contains the data in the cell A:A which is very complex format >> data . form this data , I want to get the data as I color marks form >> B2:G2. Please check the attachments. If I get any partial help I will >> be glad also .. >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Extraction desire text data from the cell by formula
What type of solution are you looking for? That is: If I make a custom function using VBA, would you be able to modify it to suit you? or do you want the solution using Excel formulas? Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Nur Hossain >To: excel-macros >Sent: Thursday, November 20, 2014 2:52 AM >Subject: $$Excel-Macros$$ Extraction desire text data from the cell by formula > > >Dear all, I need to extract the partial contents of a cell. My excel >sheet contains the data in the cell A:A which is very complex format >data . form this data , I want to get the data as I color marks form >B2:G2. Please check the attachments. If I get any partial help I will >be glad also .. > >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ VBA
I'm not sure how you expected to accomplish this, but: In the attached sheet, I simply - highlighted the entire sheet and did a find/replace and replaced : with : - Used Conditional Formatting and highlighted the cells > 10:00 if you're looking for some other method, or there's more "back story" behind this that makes it so this technique will not work, let me know. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Mandeep Baluja >To: excel-macros@googlegroups.com >Sent: Thursday, November 20, 2014 6:29 AM >Subject: $$Excel-Macros$$ VBA > > > >Dear All, > > >I need to work on Data sheet which contains all the cells in text format. >basically its a timing sheet. I want to change the format of all the time >values in correct format and wish to highlight those cells where people come >after 10:00 am .Hope you can understand this by going through the >sheet.Looking for a Macro which can correct the format of time automatically. > > >Regards, >Mandeep Baluja -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Datasheet.xls Description: Binary data
$$Excel-Macros$$ VBA
Dear All, I need to work on Data sheet which contains all the cells in text format. basically its a timing sheet. I want to change the format of all the time values in correct format and wish to highlight those cells where people come after 10:00 am .Hope you can understand this by going through the sheet.Looking for a Macro which can correct the format of time automatically. Regards, Mandeep Baluja -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Datasheet.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Extraction desire text data from the cell by formula
Try dis kind of formulas. Don't delete the hide rows reference you can shift it. I will provide you a solution soon for % column too. Regards, Mandeep Baluja *Excel Specialist. * On Thursday, November 20, 2014 1:22:52 PM UTC+5:30, Nur wrote: > > Dear all, I need to extract the partial contents of a cell. My excel > sheet contains the data in the cell A:A which is very complex format > data . form this data , I want to get the data as I color marks form > B2:G2. Please check the attachments. If I get any partial help I will > be glad also .. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. notice.xlsx Description: MS-Excel 2007 spreadsheet
