Thank you DP Your explanation was extraordinary. In addition to solve my problem, you taught me a lot! ;)
Thank you very much again Regards Isabel On Monday, September 14, 2015 at 7:37:57 PM UTC-5, De Premor wrote: > > Hi Isabel, > > I'am glad that its work, ok here is the problem in out 2nd case > 1st, we need to step back to learn about how the function work, > cpearson.com have a bunch article about this complete with explanation & > sample, you can follow this link > *http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx > <http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx>* > > After reading on that link, i'am sure you will know the different between : > * SomeFunction (** Param1**, **Param2** ) * > * OtherFunction ( **Param3**,** [Param4] **) * > * AnotherFunc ( **[Param5]**,** [Param6]** ) * > > Parameter with the bracket "[ ... ]" is an optional parameter, we can > ignoring that. > Ex: > SomeFunction(5) <== This function will error, because the 2nd param is not > an optional, we must provide a value in that like SomeFunction(5,4) > OtherFunction(100) <== this will fine, because the next parameter is > optional > OtherFunction(100,) <==This will error, because vba will think you forgot > to write the 2nd param, so it will warn you. In another word, "you must > write something after that comma" :D > AnotherFunc(30) <== Ok > AnotherFunc(,30) <== Ok, why ? 1st param is optional, we can ignoring them. > > Now, Please look at your formula, here is the right code > 'No need that coma after "v" > > If v > 1 Then > Range("RowLong").Resize(v).Insert > Range("RowFormula").Copy > Range("RowLong").Offset(-v).Resize(v).PasteSpecial Paste:=xlFormulas > Application.CutCopyMode = False > End If > > :D > Hope it help, Rgds > [dp] > > On 14/09/2015 23:12, Isabel Cristina Ortiz wrote: > > Thank you DP! > Thank you very much for your help. It worked perfectly. > > However, I tried to do the same in Calculation2 sheet, but inserting rows > instead of columns and it did not work the same logic. It gives me a > syntax error. > Could you help me fix this? > > I used: > Dim v As Integer > v = Range("Months").Value - 1 > > If v > 1 Then > Range("RowLong").Resize(v,).Insert > Range("RowFormula").Copy > Range("RowLong").Offset(-v,).Resize(v,).PasteSpecial Paste:=xlFormulas > Application.CutCopyMode = False > End If > > Regards, > Isabel > > > > On Sunday, September 13, 2015 at 8:56:11 PM UTC-5, De Premor wrote: >> >> We can avoid using loop in this case, try to replace your looping code >> with offset and resize, try this another solution >> >> Dim u As Integer >> u = Range("Months").Value - 1 >> >> If u > 1 Then >> Range("ColumnLong").Resize(, u).Insert >> Range("ColumnFormula").Copy >> Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial >> Paste:=xlFormulas >> Application.CutCopyMode = False >> End If >> >> Rgds, >> [dp] >> >> On 14/09/2015 6:31, Isabel Cristina Ortiz wrote: >> >> Hello Ashish, >> Thank you for your response. >> This is a simplified copy of my macro. The lines you can find there are >> repeated >> multiple times in my final workbook, in different spreadsheets because >> it has a broader mathematical formulation. >> Hope this helps, >> Regards >> >> >> >> On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote: >>> >>> Can u pls share a sample file >>> On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz" <ior...@gmail.com> >>> wrote: >>> >>>> Thank you Basole for your message! >>>> I have been already using your recommendation on my code, but it still >>>> takes a lot of memory. >>>> >>>> Regards, >>>> Isabel >>>> >>>> >>>> >>>> On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole wrote: >>>>> >>>>> Hi isabel, try using "Application.ScreenUpdating = False" To Turn Off >>>>> at the start of code. and "Application.Calculation = xlCalculationManual" >>>>> To turn off the automatic calculation >>>>> at the beginning of the code >>>>> see example: >>>>> >>>>> Dim u As Integer, v As Integer >>>>> u = Range("Months").Value - 1 >>>>> Application.ScreenUpdating = False 'To Turn Off at the start of code. >>>>> Application.Calculation = xlCalculationManual 'To turn off the automatic >>>>> calculationIf u > 1 Then >>>>> For v = 1 To u - 1 >>>>> Application.Goto Range("ColumnLong"), True >>>>> Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove >>>>> Application.Goto Range("ColumnFormulas"), True >>>>> Selection.Copy >>>>> Selection.Offset(0, -2).Select >>>>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, >>>>> SkipBlanks:= _ >>>>> False, Transpose:=False >>>>> Application.CutCopyMode = False >>>>> Next v >>>>> >>>>> ElseEnd If >>>>> Application.ScreenUpdating = True 'To Turn on at the end of the code. >>>>> Application.Calculation = xlCalculationAutomatic 'To turn On the >>>>> automatic calculation >>>>> >>>>> Regards. >>>>> >>>>> >>>>> 2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz <ior...@gmail.com>: >>>>> >>>>>> Hello, >>>>>> I have been working on a workbook. It is a cash flow and macro helps >>>>>> me to change the numbers of years (columns) because each project needs >>>>>> different time periods. >>>>>> What I do is that I have two columns named as "ColumnLong" and >>>>>> "ColumnFormulas". The first one is empty and only serves as a reference >>>>>> point to add more cells to the left to it, according to the number of >>>>>> months of the cash flow. >>>>>> Then, I always check the number of months on a specific cell named >>>>>> "Months" and I have a loop, it insert a column left to "ColumnLong", >>>>>> then >>>>>> goes to "ColumnFormulas" and it paste it into the new column which is >>>>>> two >>>>>> columns left to "ColumnFormulas". >>>>>> >>>>>> What I need is a simpler code because I have several loops similar to >>>>>> this one and I am having memory problems. >>>>>> I have been working on some recommendations I found on >>>>>> http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html >>>>>> >>>>>> but wasn't able to apply to this code. >>>>>> >>>>>> I hope you can help me. >>>>>> Best regards, >>>>>> Isabel >>>>>> >>>>>> >>>>>> This is the code I am using: >>>>>> >>>>>> Dim u As Integer, v As Integer >>>>>> u = Range("Months").Value - 1 >>>>>> >>>>>> If u > 1 Then >>>>>> For v = 1 To u - 1 >>>>>> Application.Goto Range("ColumnLong"), True >>>>>> Selection.Insert Shift:=xlDown, >>>>>> CopyOrigin:=xlFormatFromLeftOrAbove >>>>>> Application.Goto Range("ColumnFormulas"), True >>>>>> Selection.Copy >>>>>> Selection.Offset(0, - 2).Select >>>>>> Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, >>>>>> SkipBlanks:= _ >>>>>> False, Transpose:=False >>>>>> Application.CutCopyMode = False >>>>>> Next v >>>>>> >>>>>> Else >>>>>> End If >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> 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. >>>>>> To post to this group, send email to excel-...@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...@googlegroups.com. >>>> To post to this group, send email to excel-...@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...@googlegroups.com. >> To post to this group, send email to excel-...@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...@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. > > > -- 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.
