Hi Anton How are you calling the sub?
Regards David Grugeon On 20 February 2013 04:40, Anton Jopko <a...@kwic.com> wrote: > Hi David, > I tried your suggestion but still no luck. I may have to get a VBA book > that is simpler than the one i have. > Thanks for looking at my macro though, > anton > > > David Grugeon wrote: > >> Hi Anton >> >> How are you calling this sub? You are probably calling it without >> specifying Target. >> >> You might need to start it like (untested) >> >> Private Sub Accounting_type2() >> >> Dim Target as range >> >> Set Target = ActiveSheet.ActiveCell >> >> >> Regards >> David Grugeon >> >> >> On 19 February 2013 00:58, Anton Jopko <a...@kwic.com >> <mailto:a...@kwic.com>> wrote: >> >> Hi David, >> sorry for imposing on you again. i am trying to run this macro below >> but i get the error message "argument not optional". can you suggest >> what to do? i am also a newbie!! >> Thanks, >> anton >> >> ps the macro takes input as a cost of groceries, for example, in a >> cell in col 2, then accumulates the total in a column corresponding >> to the month specified in cell A2. with all months in the same row. >> the cursor then returns back to the original cell for more costs of >> groceries. >> >> then i move the cursor to another row for cost of hydro etc. >> >> Private Sub Accounting_type2(ByVal Target As Range) >> >> If Range("A2").Value = "Jan" And Target.Column = 2 Then >> Target.Offset(0, 1) = Target.Offset(0, 1) + Target >> If Range("A2").Value = "Feb" And Target.Column = 2 Then >> Target.Offset(0, 2) = Target.Offset(0, 2) + Target >> If Range("A2").Value = "Mar" And Target.Column = 2 Then >> Target.Offset(0, 3) = Target.Offset(0, 3) + Target >> If Range("A2").Value = "Apr" And Target.Column = 2 Then >> Target.Offset(0, 4) = Target.Offset(0, 4) + Target >> If Range("A2").Value = "May" And Target.Column = 2 Then >> Target.Offset(0, 5) = Target.Offset(0, 5) + Target >> If Range("A2").Value = "Jun" And Target.Column = 2 Then >> Target.Offset(0, 6) = Target.Offset(0, 6) + Target >> If Range("A2").Value = "Jul" And Target.Column = 2 Then >> Target.Offset(0, 7) = Target.Offset(0, 7) + Target >> If Range("A2").Value = "Aug" And Target.Column = 2 Then >> Target.Offset(0, 8) = Target.Offset(0, 8) + Target >> If Range("A2").Value = "Sep" And Target.Column = 2 Then >> Target.Offset(0, 9) = Target.Offset(0, 9) + Target >> If Range("A2").Value = "Oct" And Target.Column = 2 Then >> Target.Offset(0, 10) = Target.Offset(0, 10) + Target >> If Range("A2").Value = "Nov" And Target.Column = 2 Then >> Target.Offset(0, 11) = Target.Offset(0, 11) + Target >> If Range("A2").Value = "Dec" And Target.Column = 2 Then >> Target.Offset(0, 12) = Target.Offset(0, 12) + Target >> Target.Select >> End Sub >> >> David Grugeon wrote: >> >> Done. Thanks. >> >> Best Regards >> David Grugeon >> M 0429 029 836 <tel:0429%20029%20836> >> Bulloo Office 07 4621 8041 <tel:07%204621%208041> >> Aspley phone 07 3263 9809 <tel:07%203263%209809> >> Aspley Fax 07 3263 7786 <tel:07%203263%207786> >> >> >> >> -----Original Message----- >> From: Anton Jopko [mailto:a...@kwic.com <mailto:a...@kwic.com>] >> Sent: Monday, 18 February 2013 9:24 PM >> To: David Grugeon >> Subject: Re: $$Excel-Macros$$ how to get cumulative sum in a cell >> >> Hi David, >> i was wondering if your post should be put on the excel forum >> page so others >> could read your response? any thoughts? >> Thanks >> anton >> >> >> David Grugeon wrote: >> >> Have a look at this link. The solution is right at the end. >> >> >> http://social.technet.__micros**oft.com/Forums/en/__** >> officeitpro/thread/1d3e9aa0-20<http://microsoft.com/Forums/en/__officeitpro/thread/1d3e9aa0-20> >> >> <http://social.technet.**microsoft.com/Forums/en/** >> officeitpro/thread/1d3e9aa0-20<http://social.technet.microsoft.com/Forums/en/officeitpro/thread/1d3e9aa0-20> >> **> >> 69-4f7b-b6cf-c47e00d637f5 >> >> On Sunday, 17 February 2013, anton wrote: >> >> Hi David, >> I restarted the computer and now when I try to open VB >> editor, it >> tells me VBE6EXT.OLB could not be loaded. I guess I >> will have to >> take the computer back to the store so they can reload >> excel? >> Thanks for your suggestion, >> anton >> >> On Saturday, 16 February 2013 04:02:26 UTC-5, David >> Grugeon wrote: >> >> I suggest restarting the computer. In win8 it is >> not obvious >> how to do this. I type the Windows key and then >> type Shutdown. >> this brings up the shutdown App. Then you click >> Commands in >> the menu and restart. >> >> >> >> Regards >> David Grugeon >> >> >> On 16 February 2013 03:23, anton<a...@kwic.com >> <mailto:a...@kwic.com>> wrote: >> >> >> Hi Dave, >> I am afraid there is something weird going on. >> even in a >> blank spreadsheet, if I try to open the VB >> editor, it gives >> me an out of memory error. I don't believe it. >> I have 8 GB >> ram. also the macro you sent me works in excel >> 97 and exel >> 2010. but I can't open it or edit it in my >> accounting >> spreadsheet under windows 8?? >> I was able to open the macro on the old >> computer with excel >> 97 and its name is private sub >> worksheet_change(byval target >> as range) >> any suggestions greatly appreciated. >> anton >> >> On Friday, 25 June 2010 13:09:45 UTC-4, Dave >> wrote: >> >> Hi Anton, >> Glad it works well for you. >> To keep the cursor in the same cell, add >> the following >> line to your code: >> Target.Select >> So the macro will now look like this: >> >> Private Sub Worksheet_Change(ByVal Target >> As Range) >> If Target.Column = 2 Then Target.Offset(0, >> 1) = >> Target.Offset(0, 1) + Target >> Target.Select >> End Sub >> >> Regards - Dave. >> >> >> > Date: Thu, 24 Jun 2010 10:25:48 -0700 >> >> > Subject: Re: $$Excel-Macros$$ how to get cumulative >> sum in a cell >> > From: a...@kwic.com <mailto:a...@kwic.com> >> > To: excel-...@googlegroups.com >> <mailto:excel-...@**googlegroups.com<excel-...@googlegroups.com> >> > >> >> > >> > Hi Dave, >> > your code works very well. it seems to work for text >> in col B which >> > gives the same text in col C. I decided to make 2 >> columns for every >> > month of the year. so i just added more similar lines >> "if target =4 >> > etc", if target =6 etc in your code. we still have >> all the accounts on >> > paper for every month if needed.(we write down >> expenses etc in a book >> > each day but only enter data in spreadsheet once a >> year) This will >> > save us the trouble of adding up each grocery expense >> etc by hand for >> > each month and then entering it in the spreadsheet. >> > Thanks very much for your help. >> > Anton >> > ps one more question. when i press the enter key, the >> cursor moves >> > down one cell. how do i make the cursor stay on the >> same cell so i can >> > enter a series of grocery expenses without moving the >> cursor up one >> > cell each time? >> > >> > On Jun 24, 9:54 am, Dave Bonallack >> <davebonall...@hotmail.com >> <mailto:davebonall...@hotmail.**com<davebonall...@hotmail.com>>> >> wrote: >> >> > > Hi Anton,Attached is a sheet with very simple code. >> Enter a value into a cell in Column B, and >> you will see >> the adjacent cell in Col C increase by >> that amount. >> Since this is done by VBA, there is no >> formula in the >> Col C cells, so you can make manual >> corrections there if >> you want. You should note that using this >> method gives >> you no record of past entries, since only >> the last entry >> is retained in Col B cells. Previous >> entries are >> constantly over-writtenIf you enter >> non-numeric data >> into Col B, the macro will fault. If this >> sheet is just >> for personal use, then it doesn't really >> matter, but we >> could test for non-numeric input if needed. >> >> > > Regards - Dave. >> > > >> > > >> > > >> > > >> > > >> > > > Date: Thu, 24 Jun 2010 03:36:37 -0700 >> > > > Subject: Re: $$Excel-Macros$$ how to get >> cumulative sum in a cell >> > > > From: a...@kwic.com <mailto:a...@kwic.com> >> > > > To: excel-...@googlegroups.com >> <mailto:excel-...@**googlegroups.com<excel-...@googlegroups.com> >> > >> >> > > >> > > > Hi Dave, >> > > > Thanks very much for your reply. Yes this would >> be sufficient. Your >> > > > suggestion that XL can monitor changes to A2 and >> store the cumulative >> > > > total in A3 would be great. I would have about 50 >> similar accounts >> > > > such as gas, books, donations, repairs etc. >> > > > Would you be able to construct this macro? >> > > > Best Regards, >> > > > anton >> > > >> > > > On Jun 24, 1:49 am, Dave Bonallack >> <davebonall...@hotmail.com >> <mailto:davebonall...@hotmail.**com<davebonall...@hotmail.com>>> >> wrote: >> >> > > > > Hi Anton, >> > > >> > > > > XL can almost do what you want, which may be >> sufficient for you. >> > > >> > > > > If you want to be able to enter your grocery >> expenditure always in the same cell, XL >> can accumulate >> those entries in another cell using VBA. >> > > >> > > > > For example, You may enter your grocery >> expenditure always into A2, but the actual >> accumulated >> total would appear in A3 (or any other >> cell you like). >> This can be done by clicking a button >> after each entry >> into A2, or XL can monitor changes to A2 >> and do the >> accumulation automatically. >> > > >> > > > > If this suits your purpose, there are lots of >> us in the group who could help you achieve >> this. >> > > >> > > > > Regards - Dave. >> > > >> > > > > > Date: Wed, 23 Jun 2010 11:22:58 -0700 >> > > > >> >> -- >> 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<https://www.facebook.com/____discussexcel> >> >> <https://www.facebook.com/__**discussexcel<https://www.facebook.com/__discussexcel> >> > >> >> >> <https://www.facebook.com/__**discussexcel<https://www.facebook.com/__discussexcel> >> >> <https://www.facebook.com/**discussexcel<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 < >> http://googlegroups.com>. >> >> To post to this group, send email to >> >> excel-...@googlegroups.com >> <mailto:excel-...@**googlegroups.com<excel-...@googlegroups.com> >> >. >> >> Visit this group at >> >> http://groups.google.com/____**group/excel-macros?hl=en<http://groups.google.com/____group/excel-macros?hl=en> >> >> <http://groups.google.com/__**group/excel-macros?hl=en<http://groups.google.com/__group/excel-macros?hl=en> >> > >> >> >> <http://groups.google.com/__**group/excel-macros?hl=en<http://groups.google.com/__group/excel-macros?hl=en> >> >> <http://groups.google.com/**group/excel-macros?hl=en<http://groups.google.com/group/excel-macros?hl=en> >> >>. >> For more options, visit >> >> https://groups.google.com/____**groups/opt_out<https://groups.google.com/____groups/opt_out> >> >> <https://groups.google.com/__**groups/opt_out<https://groups.google.com/__groups/opt_out> >> > >> >> >> <https://groups.google.com/__**groups/opt_out<https://groups.google.com/__groups/opt_out> >> >> <https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >> >>. >> >> >> >> >> >> -- >> Regards >> David Grugeon >> >> >> >> >> >> >> >> > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
