Re: $$Excel-Macros$$ Fwd: FILTER ISSUE
attach file to email re-send.. + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 9:24 AM, big smile bigsmile...@gmail.com wrote: -- Forwarded message -- From: big smile bigsmile...@gmail.com Date: Mon, Sep 22, 2014 at 9:23 AM Subject: Fwd: FILTER ISSUE To: big smile bigsmile...@gmail.com TRAIL WORK SHEET.xlsm https://docs.google.com/file/d/0BwUVOOH7PyBTcUJxV3E0d1lzTFU/edit?usp=drive_web Respected Excel Guru / Friends I have 6 entries in excel files from Raw 3 to Raw 8 I want the formula that --- When ever i filter any raw by selecting any family name - Ex I filter Dhimant Parmar - EX R7 then i want in B1 What ever is in f - column after filter. When ever i change my filter and the value should change according to f column .. I will me more thankful if you share the formula as i want this system in many excel files . Thanks to all have a nice day. From Chandresh Savla . -- 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$$ FILTER ISSUE
Respected Excel Guru / Friends I have 6 entries in excel files from Raw 3 to Raw 8 I want the formula that --- When ever i filter any raw by selecting any family name - Ex I filter Dhimant Parmar - EX R7 then i want in B1 What ever is in f - column after filter. When ever i change my filter and the value should change according to f column .. I will me more thankful if you share the formula as i want this system in many excel files . Thanks to all have a nice day. From Chandresh Savla . -- 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. TRAIL WORK SHEET 1.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ FILTER ISSUE
Chandresh bhai, it is not issue but requirement..here you go... Add formula =row() in column P each cell corresponding to data. Add formula =INDIRECT(RSUBTOTAL(6,$P$2:$P$9)C6,FALSE) to cell B2. So as when you filter data you will get your desired result. Note: This will work only for filter giving one output cell. Cheers!! + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 12:48 PM, big smile bigsmile...@gmail.com wrote: Respected Excel Guru / Friends I have 6 entries in excel files from Raw 3 to Raw 8 I want the formula that --- When ever i filter any raw by selecting any family name - Ex I filter Dhimant Parmar - EX R7 then i want in B1 What ever is in f - column after filter. When ever i change my filter and the value should change according to f column .. I will me more thankful if you share the formula as i want this system in many excel files . Thanks to all have a nice day. From Chandresh Savla . -- 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. TRAIL WORK SHEET 1.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ If Condition Problem
Sir, i created an add-in for my excel work but i am trying to write a condition it will only run in Computer Name= PCEARTH otherwise sub programs will be disabled automatically or not working thanks Sub CompName() Dim tSName As String Dim ThisComputerName As String ThisComputerName = Environ(computername) tSName = ThisComputerName ActiveSheet.Range(S2).Value = ThisComputerName If (ActiveSheet.Range(S2).Value = PCEARTH) Then ActiveSheet.Range(S1).Value =INDIA Else MsgBox (Sorry You are Not a Authorised User) ' below sub programs must be disabled sub PerInfo(ByVal Control As IRibbonControl) sub Edu(ByVal Control As IRibbonControl) sub Service(ByVal Control As IRibbonControl) End If End Sub -- 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$$ FILTER ISSUE
Thank you Vaibhav Bhai for your good command over Excel Formulas God Bless you .. You are help is very good to this group. Keep it up. On Mon, Sep 22, 2014 at 2:44 PM, Vaibhav Joshi v...@vabs.in wrote: Chandresh bhai, it is not issue but requirement..here you go... Add formula =row() in column P each cell corresponding to data. Add formula =INDIRECT(RSUBTOTAL(6,$P$2:$P$9)C6,FALSE) to cell B2. So as when you filter data you will get your desired result. Note: This will work only for filter giving one output cell. Cheers!! + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 12:48 PM, big smile bigsmile...@gmail.com wrote: Respected Excel Guru / Friends I have 6 entries in excel files from Raw 3 to Raw 8 I want the formula that --- When ever i filter any raw by selecting any family name - Ex I filter Dhimant Parmar - EX R7 then i want in B1 What ever is in f - column after filter. When ever i change my filter and the value should change according to f column .. I will me more thankful if you share the formula as i want this system in many excel files . Thanks to all have a nice day. From Chandresh Savla . -- 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. -- 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$$ If Condition Problem
Hi Use this code: Sub CompName() Dim ThisComputerName As String Dim myAddIn As AddIn If UCase(Environ(computername)) = PCEARTH Then ActiveSheet.Range(S1).Value = INDIA Else MsgBox (Sorry You are Not a Authorised User) ' below sub programs must be disabled For Each myAddIn In AddIns Debug.Print myAddIn.Name If myAddIn.Name = MYADDIN_NAME1.xlam _ Or myAddIn.Name = MYADDIN_NAME2.xlam _ Or myAddIn.Name = MYADDIN_NAME2.xla _ Then myAddIn.Installed = False End If Next End If End Sub here MYADDIN_NAME1.xlam is name of your adding file, replace with your adding file name delete additional file name. Cheers!! + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 2:56 PM, my excel myxlg...@gmail.com wrote: Sir, i created an add-in for my excel work but i am trying to write a condition it will only run in Computer Name= PCEARTH otherwise sub programs will be disabled automatically or not working thanks Sub CompName() Dim tSName As String Dim ThisComputerName As String ThisComputerName = Environ(computername) tSName = ThisComputerName ActiveSheet.Range(S2).Value = ThisComputerName If (ActiveSheet.Range(S2).Value = PCEARTH) Then ActiveSheet.Range(S1).Value =INDIA Else MsgBox (Sorry You are Not a Authorised User) ' below sub programs must be disabled sub PerInfo(ByVal Control As IRibbonControl) sub Edu(ByVal Control As IRibbonControl) sub Service(ByVal Control As IRibbonControl) End If End Sub -- 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$$ Moving Completed rows to sheet2
Hi FA, Click blue button to Run Macro.. Cheers!! + *I did not do this for you. God is here working through me for you.* On Sun, Sep 21, 2014 at 8:55 PM, Eugene Bernard eugene.bern...@gmail.com wrote: -- Forwarded message -- From: Eugene Bernard eugene.bern...@gmail.com Date: Sun, Sep 21, 2014 at 8:54 PM Subject: $$Excel-Macros$$ Moving Completed rows to sheet2 To: excel-macros@googlegroups.com Dear all, Please find attached a sample worksheet, where in i like to move the completed rows to sheet 2 from sheet 1. In the attached example, rows having code 12 is completed which needs to be moved to sheet2. where as 11 is not to be moved since 3 more rows with code 11 is having the status without status updated as C. Please help me with macro to do this activity. TIA Eugene -- 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. -- 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. Example.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ How to fix a vba code that returns a cell reference
It's VERY confusing trying to follow what you're doing... it seems like you're using chr() functions to convert characters to STRINGS? which doesn't make ANY sense. I'd like to first address your solution, then ask what it is you're trying to do? (lol) For your code: ActiveSheet.Cells(i, 2).Value = = Chr(73) Chr(70) Chr(40) Chr(68) NextRw Chr(60) Chr(34) Chr(34) Chr(44) Chr(68) NextRw Chr(44) Chr(34) Chr(34) Chr(41) The fact is: = is Chr(61) So, if (for some odd reason) you're wanting to use character codes, why not ALWAYS use character codes. .Value = Chr(61) Chr(73) Chr(40) ... ad nauseum... If you're wanting to use text strings, why not use text strings? I test string is opened with a delimiter () and closed with the same delimter. = tells VBA that the = within the two quotes is to be treated as a text string, not evaluated as an operator. = Chr(73) Chr(70) is concatenating the string = with the character I and the character F so why not use =IF ?? Say NextRw is 45 You expect the cell formula to read: =IF(D45 , D45, ) right? In VBA you build a text string =IF(D45 , D45, ) Except: to include a () in a formula you have to 'double-up'. so, () would cause it to display as () to get two of them () you have to do () and (), or () So your code looks like: ActiveSheet.Cells(i, 2).Value = =IF(D45 , D45, ) But, you want to replace 45 with the current, runtime value if NextRw. You simply start a text sting with , stop it with , concatenate the value of NextRw, then switch back to concatenating text: =IF(D NextRw, D NextRw ,) so your VBA code looks like: ActiveSheet.Cells(i, 2).Value = =IF(D NextRw, D NextRw , ) --- Now, on to my second question: WHY? Do you want the cell (cells(i,2)) to contain the value or do you really want it to contain the formula? why not make VBA do the work? Why not use something like If (Activesheet(cells(nextrw,D).value ) _ and (Activesheet(cells(nextrw,D).value 0)) then activesheet.cells(i,2).value = =D nextrw end if 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: Mr_Bill egge...@gmail.com To: excel-macros@googlegroups.com Sent: Sunday, September 21, 2014 3:19 PM Subject: $$Excel-Macros$$ How to fix a vba code that returns a cell reference How this works is if data is input in the next available cell in a column this code sees what cell it was and in another of area of the sheet input the cell location like =YXX the XX being the cell number. and Y being the Column using the nextrw from this line NextRw = ws.Range(A190).End(xlUp).Offset(2, 0).Row I was able to come up with this ActiveSheet.Cells(i, 3).Value = = Chr(68) NextRw Which does work but need to edit it so if the main cell is blank or has a -this would return a blank I tried Evaluate(=IF(NextRw,NextRw,)) kind of a hail mary So went back to what worked before but the code looks ridiculous and wonder can someone please fix this so it looks like a formula that is easier to edit. Dont laugh too hard but here is what I came up with for a simple formula that should come out looking like this sampel =IF(YXX,YXX,) the YXX are what the script finds using the NextRw ActiveSheet.Cells(i, 2).Value = = Chr(73) Chr(70) Chr(40) Chr(68) NextRw Chr(60) Chr(34) Chr(34) Chr(44) Chr(68) NextRw Chr(44) Chr(34) Chr(34) Chr(41) It does work just hard to read and edit. Copy of the stripped down WorkBook -- 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
Re: $$Excel-Macros$$ FILTER ISSUE
Cheers Bro.. Keep Smiling :) + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 3:39 PM, big smile bigsmile...@gmail.com wrote: Thank you Vaibhav Bhai for your good command over Excel Formulas God Bless you .. You are help is very good to this group. Keep it up. On Mon, Sep 22, 2014 at 2:44 PM, Vaibhav Joshi v...@vabs.in wrote: Chandresh bhai, it is not issue but requirement..here you go... Add formula =row() in column P each cell corresponding to data. Add formula =INDIRECT(RSUBTOTAL(6,$P$2:$P$9)C6,FALSE) to cell B2. So as when you filter data you will get your desired result. Note: This will work only for filter giving one output cell. Cheers!! + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 12:48 PM, big smile bigsmile...@gmail.com wrote: Respected Excel Guru / Friends I have 6 entries in excel files from Raw 3 to Raw 8 I want the formula that --- When ever i filter any raw by selecting any family name - Ex I filter Dhimant Parmar - EX R7 then i want in B1 What ever is in f - column after filter. When ever i change my filter and the value should change according to f column .. I will me more thankful if you share the formula as i want this system in many excel files . Thanks to all have a nice day. From Chandresh Savla . -- 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. -- 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
Re: $$Excel-Macros$$ Macro to highlight Max Value in a Column and copy/transpose data in another file
Hi Dear, Please find the Attached file ... Regards P On Mon, Sep 22, 2014 at 2:53 PM, prkhan56 prkha...@gmail.com wrote: Dear All, I am using Excel 2010 and have attached a file with my problem. I need Two Macros for my problem. There are two Files (both in a shared location - Path S:\...\...\) File 1 has sheet name SM File 2 has sheet name Input Data (shown here to explain the problem) Data in set of 24 rows i.e for 24 Hours x 10 days Data Set is starting from A25 to Z First Macro should run upto Row 264 and highlight the Max Value in Col T 24 Rows data should be considered when highlighting the Max Value eg Row 25 - 48, 49 - 72 and so on till Row 264 Second Macro should run only when the Cell pointer is on the highlighted Cell in Col T It should copy the values found in the highlighted Row and paste it in File 2 as follows: Step 1 - Values from Cell C to I should be copied and transposed in File 2 in Cell B9 down Step 2 - Values from Cell J to M should be copied and transposed in File 2 in Cell C9 down Step 3 - Values from Cell N to S should be copied and transposed in File 2 in Cell D9 down Step 4 - Values from Cell T to Z should be copied and transposed in File 2 in Cell E9 down Colored only for sake of explanation Second Macro should run only once and then as and when required, as further work needs to be done after copy/transposing the data. TIA Rashid -- 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. -- PramodSingh -- 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. File no 1-Group.xlsb Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
Re: $$Excel-Macros$$ Track the system Lock
If you want this if you fear that screen lock may interrupt your running macro, then you can use application.screenupdating=false in the beginning of your code. Cheers On Sep 19, 2014 5:49 PM, Vaibhav Joshi v...@vabs.in wrote: PFA for locking screen.. Cheers!! + *I did not do this for you. God is here working through me for you.* On Fri, Sep 19, 2014 at 5:48 PM, Vaibhav Joshi v...@vabs.in wrote: Hi What you want to disable? + *I did not do this for you. God is here working through me for you.* On Fri, Sep 19, 2014 at 9:02 AM, Ganesh N ganesh190...@gmail.com wrote: i need VBA code for below task I want to do disable and the (windows lock) *window+L* or (lock workstation) or *Crtl+alt+Del*. can every one give the code for that...? -- 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$$ Macro to highlight Max Value in a Column and copy/transpose data in another file
Hi Pramod, Thanks for your prompt reply. Works great! The second macro should run when the Cell Pointer is in Column T on a Highlighted Cell (in red). *Can red be given the RGB value mentioned on my sample data?* I want to copy and transpose cells from the Row where the Max value is found in Col T. (Cells to be copied are colored and shown under various Steps) See the output required is in the Input Data Sheet. I have made a sample output on Input Data sheet for values on Row 25 on SM Data sheet I have shown the Input Data shown here for sake of explanation. It would be in File 2 which would be on the shared folder Path S:\..\...\ Hope it is clear now. Thanks once again for your time. Rashid On Monday, September 22, 2014 5:23:52 PM UTC+4, pramodb35 wrote: Hi Dear, Please find the Attached file ... Regards P On Mon, Sep 22, 2014 at 2:53 PM, prkhan56 prkh...@gmail.com javascript: wrote: Dear All, I am using Excel 2010 and have attached a file with my problem. I need Two Macros for my problem. There are two Files (both in a shared location - Path S:\...\...\) File 1 has sheet name SM File 2 has sheet name Input Data (shown here to explain the problem) Data in set of 24 rows i.e for 24 Hours x 10 days Data Set is starting from A25 to Z First Macro should run upto Row 264 and highlight the Max Value in Col T 24 Rows data should be considered when highlighting the Max Value eg Row 25 - 48, 49 - 72 and so on till Row 264 Second Macro should run only when the Cell pointer is on the highlighted Cell in Col T It should copy the values found in the highlighted Row and paste it in File 2 as follows: Step 1 - Values from Cell C to I should be copied and transposed in File 2 in Cell B9 down Step 2 - Values from Cell J to M should be copied and transposed in File 2 in Cell C9 down Step 3 - Values from Cell N to S should be copied and transposed in File 2 in Cell D9 down Step 4 - Values from Cell T to Z should be copied and transposed in File 2 in Cell E9 down Colored only for sake of explanation Second Macro should run only once and then as and when required, as further work needs to be done after copy/transposing the data. TIA Rashid -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- PramodSingh -- 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$$ Moving Completed rows to sheet2
Dear Joshi, Thanks for your reply. As already explained in my first post, rows having code with 11 is not to be treated as completed, since 3 rows having codes as 11 is with status tag blank. The logic for moving the rows from sheet1 to sheet2 is rows with status tag with C if it has unique code, or else if it has same code for 3 rows, then all 3 rows should have status with C Regards Eugene On Mon, Sep 22, 2014 at 5:30 PM, Vaibhav Joshi v...@vabs.in wrote: Hi FA, Click blue button to Run Macro.. Cheers!! + *I did not do this for you. God is here working through me for you.* On Sun, Sep 21, 2014 at 8:55 PM, Eugene Bernard eugene.bern...@gmail.com wrote: -- Forwarded message -- From: Eugene Bernard eugene.bern...@gmail.com Date: Sun, Sep 21, 2014 at 8:54 PM Subject: $$Excel-Macros$$ Moving Completed rows to sheet2 To: excel-macros@googlegroups.com Dear all, Please find attached a sample worksheet, where in i like to move the completed rows to sheet 2 from sheet 1. In the attached example, rows having code 12 is completed which needs to be moved to sheet2. where as 11 is not to be moved since 3 more rows with code 11 is having the status without status updated as C. Please help me with macro to do this activity. TIA Eugene -- 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. -- 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
Fwd: $$Excel-Macros$$ Moving Completed rows to sheet2
Dear Joshi, Thanks for your reply. As already explained in my first post, rows having code with 11 is not to be treated as completed, since 3 rows having codes as 11 is with status tag blank. The logic for moving the rows from sheet1 to sheet2 is rows with status tag with C if it has unique code, or else if it has same code for 3 rows, then all 3 rows should have status with C Regards Eugene On Mon, Sep 22, 2014 at 5:30 PM, Vaibhav Joshi v...@vabs.in wrote: Hi FA, Click blue button to Run Macro.. Cheers!! + *I did not do this for you. God is here working through me for you.* On Sun, Sep 21, 2014 at 8:55 PM, Eugene Bernard eugene.bern...@gmail.com wrote: -- Forwarded message -- From: Eugene Bernard eugene.bern...@gmail.com Date: Sun, Sep 21, 2014 at 8:54 PM Subject: $$Excel-Macros$$ Moving Completed rows to sheet2 To: excel-macros@googlegroups.com Dear all, Please find attached a sample worksheet, where in i like to move the completed rows to sheet 2 from sheet 1. In the attached example, rows having code 12 is completed which needs to be moved to sheet2. where as 11 is not to be moved since 3 more rows with code 11 is having the status without status updated as C. Please help me with macro to do this activity. TIA Eugene -- 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. -- 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
Re: $$Excel-Macros$$ How to fix a vba code that returns a cell reference
Thank you for trying to explain a solution. The concept is very straight forward I just wanted the value input in next available row between 6-199 and a formula refrencing that cell in the matching column between 202-231. I have a freelancer working on this project now so all is good. On Monday, September 22, 2014 5:21:50 AM UTC-7, Paul Schreiner wrote: It's VERY confusing trying to follow what you're doing... it seems like you're using chr() functions to convert characters to STRINGS? which doesn't make ANY sense. I'd like to first address your solution, then ask what it is you're trying to do? (lol) For your code: ActiveSheet.Cells(i, 2).Value = = Chr(73) Chr(70) Chr(40) Chr(68) NextRw Chr(60) Chr(34) Chr(34) Chr(44) Chr(68) NextRw Chr(44) Chr(34) Chr(34) Chr(41) The fact is: = is Chr(61) So, if (for some odd reason) you're wanting to use character codes, why not ALWAYS use character codes. .Value = Chr(61) Chr(73) Chr(40) ... ad nauseum... If you're wanting to use text strings, why not use text strings? I test string is opened with a delimiter () and closed with the same delimter. = tells VBA that the = within the two quotes is to be treated as a text string, not evaluated as an operator. = Chr(73) Chr(70) is concatenating the string = with the character I and the character F so why not use =IF ?? Say NextRw is 45 You expect the cell formula to read: =IF(D45 , D45, ) right? In VBA you build a text string =IF(D45 , D45, ) Except: to include a () in a formula you have to 'double-up'. so, () would cause it to display as () to get two of them () you have to do () and (), or () So your code looks like: ActiveSheet.Cells(i, 2).Value = =IF(D45 , D45, ) But, you want to replace 45 with the current, runtime value if NextRw. You simply start a text sting with , stop it with , concatenate the value of NextRw, then switch back to concatenating text: =IF(D NextRw, D NextRw ,) so your VBA code looks like: ActiveSheet.Cells(i, 2).Value = =IF(D NextRw, D NextRw , ) --- Now, on to my second question: WHY? Do you want the cell (cells(i,2)) to contain the value or do you really want it to contain the formula? why not make VBA do the work? Why not use something like If (Activesheet(cells(nextrw,D).value ) _ and (Activesheet(cells(nextrw,D).value 0)) then activesheet.cells(i,2).value = =D nextrw end if *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:* Mr_Bill egg...@gmail.com javascript: *To:* excel-...@googlegroups.com javascript: *Sent:* Sunday, September 21, 2014 3:19 PM *Subject:* $$Excel-Macros$$ How to fix a vba code that returns a cell reference How this works is if data is input in the next available cell in a column this code sees what cell it was and in another of area of the sheet input the cell location like =YXX the XX being the cell number. and Y being the Column using the nextrw from this line NextRw = ws.Range(A190).End(xlUp).Offset(2, 0).Row I was able to come up with this ActiveSheet.Cells(i, 3).Value = = Chr(68) NextRw Which does work but need to edit it so if the main cell is blank or has a -this would return a blank I tried Evaluate(=IF(NextRw,NextRw,)) kind of a hail mary So went back to what worked before but the code looks ridiculous and wonder can someone please fix this so it looks like a formula that is easier to edit. Dont laugh too hard but here is what I came up with for a simple formula that should come out looking like this sampel =IF(YXX,YXX,) the YXX are what the script finds using the NextRw ActiveSheet.Cells(i, 2).Value = = Chr(73) Chr(70) Chr(40) Chr(68) NextRw Chr(60) Chr(34) Chr(34) Chr(44) Chr(68) NextRw Chr(44) Chr(34) Chr(34) Chr(41) It does work just hard to read and edit. Copy of the stripped down WorkBook https://www.dropbox.com/s/04po9tbca5zvv13/again%20another%20stripped%20down%20version.xlsm?dl=0 -- 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
Re: $$Excel-Macros$$ If Condition Problem
thanks a lot sir On Mon, Sep 22, 2014 at 3:41 AM, Vaibhav Joshi v...@vabs.in wrote: Hi Use this code: Sub CompName() Dim ThisComputerName As String Dim myAddIn As AddIn If UCase(Environ(computername)) = PCEARTH Then ActiveSheet.Range(S1).Value = INDIA Else MsgBox (Sorry You are Not a Authorised User) ' below sub programs must be disabled For Each myAddIn In AddIns Debug.Print myAddIn.Name If myAddIn.Name = MYADDIN_NAME1.xlam _ Or myAddIn.Name = MYADDIN_NAME2.xlam _ Or myAddIn.Name = MYADDIN_NAME2.xla _ Then myAddIn.Installed = False End If Next End If End Sub here MYADDIN_NAME1.xlam is name of your adding file, replace with your adding file name delete additional file name. Cheers!! + *I did not do this for you. God is here working through me for you.* On Mon, Sep 22, 2014 at 2:56 PM, my excel myxlg...@gmail.com wrote: Sir, i created an add-in for my excel work but i am trying to write a condition it will only run in Computer Name= PCEARTH otherwise sub programs will be disabled automatically or not working thanks Sub CompName() Dim tSName As String Dim ThisComputerName As String ThisComputerName = Environ(computername) tSName = ThisComputerName ActiveSheet.Range(S2).Value = ThisComputerName If (ActiveSheet.Range(S2).Value = PCEARTH) Then ActiveSheet.Range(S1).Value =INDIA Else MsgBox (Sorry You are Not a Authorised User) ' below sub programs must be disabled sub PerInfo(ByVal Control As IRibbonControl) sub Edu(ByVal Control As IRibbonControl) sub Service(ByVal Control As IRibbonControl) End If End Sub -- 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. -- 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
Re: $$Excel-Macros$$ Loop in cell in different sheet in a workbook
Thank you very much Mr.Paul. I have tried it. Succesfull. Can I ask again? Divided by small number square 0.2, how can i get the value, because the VBA assume it zero. Actually i don't want to assume it zero value. On Fri, Sep 19, 2014 at 9:31 AM, Paul Schreiner schreiner_p...@att.net wrote: What is the actual name of your worksheets? Is one actually called Sheet1 or have you renamed it something else? For instance, if the first sheet in your workbook is called Data, in the VBA explorer window, it will be called: Sheet1 (Data) If you want to refer to the value of Cells(32 + j, 3 + i) in the Data sheet, you would either use: Worksheets(Data).Cells(32 + j, 3 + i).Value or Sheet1.Cells(32 + j, 3 + i).Value *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:* josua sitinjak josuasitinja...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thursday, September 18, 2014 9:41 PM *Subject:* $$Excel-Macros$$ Loop in cell in different sheet in a workbook Good morning MS EXCEL AND VBA MACROS member. I'm newbie in macros. I would like to ask something. Please help me, I want to loop in a value in different sheet in a workbook at macro excel. ex : I want to compute x value in sheet 2, but to compute the x value, i need y value in sheet 1. I have tried some method, but the macros said, subs out of range. Anyone can help me? Every x value always related to every y value. I mean x1 to y1, x2 to y2, etc... This is the code i type in sheet 2. Sub Hitung_Sound_Pressure_Level () Dim i, j, A, c, Pe0 As Double A = 0.0705 Pe0 = 0.2 c = 2 ^ 0.5 For j = 0 To 10 For i = 0 To 10 Cells(32 + j, 3 + i).Value = 10 * Log((A / ((Worksheets(Sheet1).Cells(32 + j, 3 + i).Value) * c)) ^ 2 / (Pe0 ^ 2)) Next i Next j End Sub -- 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 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/iM8xfH_ghWY/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
