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> 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
>> Bulloo Office 07 4621 8041
>> Aspley phone 07 3263 9809
>> Aspley Fax 07 3263 7786
>>
>>
>> -----Original Message-----
>> From: Anton Jopko [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.**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> 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
>>> > To: 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> 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
>>> > > > To: 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> 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>
>>> >
>>>
>>> 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.
>>
>>> 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>
>>> >.
>>> 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>
>>> >.
>>>
>>>
>>>
>>>
>>>
>>> --
>>> 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.
