Macro Output Question

Hello,

I was given some excellent help on the forum, and thanks to all. Need help 
with one more thing to finish. I was given the below macro to be able to 
enter a number in one cell and the output cell would continuously add up the 
sum. I need to take the output sum and subtract it from another cell, but I 
keep getting an error. What do I need to do to make this happen.



Thanks JR


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(.Cells, Range("C2:C15")) Then
                If IsNumeric(.Value) Then
                    Application.EnableEvents = False
                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
                    Application.EnableEvents = True
                End If
            End If
        End With
    End Sub 


0
teamjr (9)
12/22/2004 4:29:29 PM
excel 39879 articles. 2 followers. Follow

5 Replies
479 Views

Similar Articles

[PageSpeed] 58

Hi JR

see notes in-line in the code

> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>        With Target
>            If .Count > 1 Then Exit Sub
>            If Not Intersect(.Cells, Range("C2:C15")) Then
>                If IsNumeric(.Value) Then
>                    Application.EnableEvents = False
>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
'the line of code above increments the value of the cell
'so to subtract this value from another cell - change A1 to the relevant 
cell
                       Range("A1").Value = Range("A1").Value - 
..Offset(0,1).Value
>                    Application.EnableEvents = True
>                End If
>            End If
>        End With
>    End Sub


Cheers
JulieD


"JR" <teamjr@aol.com> wrote in message 
news:Jhhyd.1757$wZ2.1271@newssvr13.news.prodigy.com...
> Hello,
>
> I was given some excellent help on the forum, and thanks to all. Need help 
> with one more thing to finish. I was given the below macro to be able to 
> enter a number in one cell and the output cell would continuously add up 
> the sum. I need to take the output sum and subtract it from another cell, 
> but I keep getting an error. What do I need to do to make this happen.
>
>
>
> Thanks JR
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>        With Target
>            If .Count > 1 Then Exit Sub
>            If Not Intersect(.Cells, Range("C2:C15")) Then
>                If IsNumeric(.Value) Then
>                    Application.EnableEvents = False
>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>                    Application.EnableEvents = True
>                End If
>            End If
>        End With
>    End Sub
> 


0
JulieD1 (2295)
12/22/2004 4:36:40 PM
What kind of error do you get? 
Can't you just use a formula to do this subtraction?

Regards,

Peo Sjoblom

"JR" wrote:

> Hello,
> 
> I was given some excellent help on the forum, and thanks to all. Need help 
> with one more thing to finish. I was given the below macro to be able to 
> enter a number in one cell and the output cell would continuously add up the 
> sum. I need to take the output sum and subtract it from another cell, but I 
> keep getting an error. What do I need to do to make this happen.
> 
> 
> 
> Thanks JR
> 
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>         With Target
>             If .Count > 1 Then Exit Sub
>             If Not Intersect(.Cells, Range("C2:C15")) Then
>                 If IsNumeric(.Value) Then
>                     Application.EnableEvents = False
>                     .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>                     Application.EnableEvents = True
>                 End If
>             End If
>         End With
>     End Sub 
> 
> 
> 
0
PeoSjoblom (789)
12/22/2004 4:43:02 PM
Hello,

I am sorry, I am not explaining myself very well. I am using the below macro 
do determine a value in a cell, and I need to take this value as a digit if 
possible and subtract it from another cell.

Example:

Input to cell A1 = 1, and out put is B1=1, now I need to take this 
calculated value in B1 and subtract it from the value in D1. I think my 
problem is that it is not letting me take just the output value in B1.

Thanks JR


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(.Cells, Range("A1:A10")) Then
                If IsNumeric(.Value) Then
                    Application.EnableEvents = False
                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
                    Application.EnableEvents = True
                End If
            End If
        End With
    End Sub


"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message 
news:u$svATE6EHA.936@TK2MSFTNGP12.phx.gbl...
> Hi JR
>
> see notes in-line in the code
>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>        With Target
>>            If .Count > 1 Then Exit Sub
>>            If Not Intersect(.Cells, Range("C2:C15")) Then
>>                If IsNumeric(.Value) Then
>>                    Application.EnableEvents = False
>>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
> 'the line of code above increments the value of the cell
> 'so to subtract this value from another cell - change A1 to the relevant 
> cell
>                       Range("A1").Value = Range("A1").Value - 
> .Offset(0,1).Value
>>                    Application.EnableEvents = True
>>                End If
>>            End If
>>        End With
>>    End Sub
>
>
> Cheers
> JulieD
>
>
> "JR" <teamjr@aol.com> wrote in message 
> news:Jhhyd.1757$wZ2.1271@newssvr13.news.prodigy.com...
>> Hello,
>>
>> I was given some excellent help on the forum, and thanks to all. Need 
>> help with one more thing to finish. I was given the below macro to be 
>> able to enter a number in one cell and the output cell would continuously 
>> add up the sum. I need to take the output sum and subtract it from 
>> another cell, but I keep getting an error. What do I need to do to make 
>> this happen.
>>
>>
>>
>> Thanks JR
>>
>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>        With Target
>>            If .Count > 1 Then Exit Sub
>>            If Not Intersect(.Cells, Range("C2:C15")) Then
>>                If IsNumeric(.Value) Then
>>                    Application.EnableEvents = False
>>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>>                    Application.EnableEvents = True
>>                End If
>>            End If
>>        End With
>>    End Sub
>>
>
> 


0
teamjr (9)
12/22/2004 11:23:58 PM
if you have
=D1-B1

in another cell it should work ... this number should change after the code 
runs - is this what you're trying to do

Cheers
JulieD



"JR" <teamjr@aol.com> wrote in message 
news:imnyd.1960$wZ2.178@newssvr13.news.prodigy.com...
> Hello,
>
> I am sorry, I am not explaining myself very well. I am using the below 
> macro do determine a value in a cell, and I need to take this value as a 
> digit if possible and subtract it from another cell.
>
> Example:
>
> Input to cell A1 = 1, and out put is B1=1, now I need to take this 
> calculated value in B1 and subtract it from the value in D1. I think my 
> problem is that it is not letting me take just the output value in B1.
>
> Thanks JR
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>        With Target
>            If .Count > 1 Then Exit Sub
>            If Not Intersect(.Cells, Range("A1:A10")) Then
>                If IsNumeric(.Value) Then
>                    Application.EnableEvents = False
>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>                    Application.EnableEvents = True
>                End If
>            End If
>        End With
>    End Sub
>
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message 
> news:u$svATE6EHA.936@TK2MSFTNGP12.phx.gbl...
>> Hi JR
>>
>> see notes in-line in the code
>>
>>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>>        With Target
>>>            If .Count > 1 Then Exit Sub
>>>            If Not Intersect(.Cells, Range("C2:C15")) Then
>>>                If IsNumeric(.Value) Then
>>>                    Application.EnableEvents = False
>>>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>> 'the line of code above increments the value of the cell
>> 'so to subtract this value from another cell - change A1 to the relevant 
>> cell
>>                       Range("A1").Value = Range("A1").Value - 
>> .Offset(0,1).Value
>>>                    Application.EnableEvents = True
>>>                End If
>>>            End If
>>>        End With
>>>    End Sub
>>
>>
>> Cheers
>> JulieD
>>
>>
>> "JR" <teamjr@aol.com> wrote in message 
>> news:Jhhyd.1757$wZ2.1271@newssvr13.news.prodigy.com...
>>> Hello,
>>>
>>> I was given some excellent help on the forum, and thanks to all. Need 
>>> help with one more thing to finish. I was given the below macro to be 
>>> able to enter a number in one cell and the output cell would 
>>> continuously add up the sum. I need to take the output sum and subtract 
>>> it from another cell, but I keep getting an error. What do I need to do 
>>> to make this happen.
>>>
>>>
>>>
>>> Thanks JR
>>>
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>>        With Target
>>>            If .Count > 1 Then Exit Sub
>>>            If Not Intersect(.Cells, Range("C2:C15")) Then
>>>                If IsNumeric(.Value) Then
>>>                    Application.EnableEvents = False
>>>                    .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>>>                    Application.EnableEvents = True
>>>                End If
>>>            End If
>>>        End With
>>>    End Sub
>>>
>>
>>
>
> 


0
JulieD1 (2295)
12/23/2004 12:42:38 AM
Hello,

I get this error when I enter any number in any other cell:

runtime error 91
object variable or with block variable not set

refers to this line in the macro

If Not Intersect(.Cells, Range("A1:A10")) Then

Thanks JR


>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>         With Target
>>             If .Count > 1 Then Exit Sub
>>             If Not Intersect(.Cells, Range("C2:C15")) Then
>>                 If IsNumeric(.Value) Then
>>                     Application.EnableEvents = False
>>                     .Offset(0, 1).Value = .Offset(0, 1).Value + .Value
>>                     Application.EnableEvents = True
>>                 End If
>>             End If
>>         End With
>>     End Sub
>>
>>
>> 


0
teamjr (9)
12/23/2004 1:28:45 PM
Reply:

Similar Artilces:

Two questions on saving sent messages
Hi, all, I am a new Outlook user (formerly Notes) and want to do two things in Outlook: 1) I do not want to save all sent messages, as some can be discarded immediately. So, I'd like to, for example, have a selection window pop up after I send each message asking me "Save" or "Don't Save" or something like that. Any way to do that in Outlook? 2) For those messages I want to save after sending, I'd like to direct them to a specific, existing folder. I've seen the posts on creating rules to do this, but my interest is different. Since notes to someone ...

Checkbox enable question
I have a subform with freight components on it. I have it were if I click a checkbox it'll run the following code where it'll disenable the textbox selections until the checkbox is clicked "active" (enabled or checked again). This works fine, but when I go to a new record, if clicked "not active", the new record textbox remains disenabled. Is there away for that to effect the direct record and not all records? Thanks in advance. Ryan Private Sub FreightDivisionActive_Click() If FreightDivisionActive = False Then FreightAmount.Enabled = False ElseIf ...

Complex Date chart question
I have a chart of data (2036 rows) that includes a date in each entry/row. Some of the dates occur once, some of them occur several times. I would like to create a chart that shows time (3 1/2 year period) along the X-axis and the numbers of times each date occurs on the Y-axis (between 1 and 20). How do I do this??? Each time I try I just get a jumbled mess. I think a line or scatter graph would work best. One option would be to create a pivot table on your chart source. Your row field would be the dates and your data field would be a count of the dates. Once the pivot table...

Password Protect Macros
Is it possible to password protect your macros, so that no one can mess with them or update. Thanks, Open your workbook Open the VBE Select your project Tools|VBAProject Properties|Protection tab Give it a memorable password. Be aware that this protection isn't foolproof. There are password breakers/avoiders that are available for money or free on the web. Charles Reid wrote: > > Is it possible to password protect your macros, so that no one can mess with > them or update. > > Thanks, -- Dave Peterson hey sir, thanks for the info, it seems to work, but not e...

macro to login GP using Window XP Schedule Tasks stopped working
I have create a batch file that runs a macro to login to Great Plains and then print out report to a text file then logs out of Great Plains. This is run every night on the server so I use the 'Schedule Tasks' in Window XP to do this. I normally logon to the server using Remote Desktop Connection to see if it is running. I actually see GP being login to, print the report & then logs out. It has been working fine about 90% of the time for the last 2 months. But I sometime would get an "Error: Field on window is inactive or disabled. Last Line: 20". In my ...

Simple deletion macro
Hi, Is there any simpler (than below) method of deleting a bunch of rows based on some criteria? (in this case, empty cells). I run the macro below, but it takes forever to execute. Thanks, Best regards Yarroll Dim i As Long, ile As Long ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B")) For i = ile To 1 Step -1 If Cells(i, 1).Value = "" Then Rows(i).Delete End If Next End Sub Yarroll, Use Auto-filter on column B, and select a criteria of non-blanks. Delete all visible rows, with a blue row number to the left. -- HTH Bob Phillips ... looking out across P...

Circular logging question #2
If I create a new store with circular logging enabled, do I still need to restart the information store service? Thank you. Circular logging is activated at the Storage Group level. The GUI will prompt you to take all DBs offline and then online. Circular should never be used on back end servers. /Simon "justsimplequestions" <sigmatechnology@googlemail.com> wrote in message news:1173981863.481309.98010@o5g2000hsb.googlegroups.com... > If I create a new store with circular logging enabled, do I still need > to restart the information store service? > > Thank yo...

Pivot Table Question #10
I have all the data I want in a pivot table in an Excel Spreadsheet. I've got the columns and rows all set, but I'm having a problem with the data. Instead of displaying the data I want to see, it's counting it. Is there anyway to just display the data? This is the first time I'm trying to use a pivot table. Right click on the values you are seeing, click on field settings and then choose SUM on the left as opposed to COUNT. This usually happens when you have blanks in your data, so if you select all your source data beforehand, do Edit / Go To / Special / Blanks, ...

Macro-error 400- macro doesn't work on other computer
I sent an excel workbook to another computer. On that computer, I created a macro button and assigned the macro to it (which is in "This Workbook"), but I get "400" error show up and the macro doesn/t work. Do I have the macro in the wrong place? Joe I seached the VBA help for "trappable errors" and found this for 400: Form already displayed; can't show modally (Error 400) You can't use the Show method to display a visible form as modal. This error has the following cause and solution: You tried to use Show, with the style argument set to 1 � vbMod...

Insert email addresses with a macro
Hi, How do I write a macro in Outlook to enable me to insert pre-defined email addresses straight into an existing open email that is ready to send? I know it can be done by using a distribution list but that's a few extra mouse clicks and when you have to do this 20 times a day every day every click counts! Thanks, Phillip You can create a template that is preaddressed or use a hyperlinked button to fill in the field. http://www.outlook-tips.net/archives/2003/20030805.htm - use a mailto: hyperlink. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hour...

license question
I know that it is legal to put a copy of Publisher on a desktop computer and a laptop, but someone told me today that they thought it was legal for teachers to have a copy installed on their home computer and also install one at school for their use. Can someone tell me if this is true. If so, how do you go about activating the school installation? This would help me a ton as I am using Pub 2002 at home and Pub 2000 at school and taking a very large booklet file back and forth to work on it both places in different versions is driving me crazy! Thanks. Karen --- Outgoing mail is certifie...

Beginner's Question
I never schedule anything between midnight and 8:00 a.m. How can I customize Outlook so that these times do not appear on my screen in the one-day or work-week calendars? there's no way to do that. Judy Gleeson MVP Outlook Outlook trainer and author of Productiv_IT with Outlook www.acorntraining.com.au Canberra, Australia Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information on it. Samuel Johnson (1709 - 1784), quoted in Boswell's Life of Johnson <NOSPAM@joinme.com> wrote in message news:45b1ac54.143207821@news.dallas.sbcglobal....

Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I select each time and then have the first step of the macro select the 12 cells immediately to the right of the cell I select each time. Currently my macro runs perfectly except that the macro will only begin in the exact same cell every time the marco is run. This means that the same 13 cells are used by the macro every time. Where as I need the marco to begin in a different cell that I more or less randomly select and then the macro should select the 12 cells immediately to the right of that cell. I know someone out there...

Access, how to base a query field criteria in a macro
I have a form based on a query 'maindata' . I have based the criteria in the query as one field from form [switchboard]![hawk] or the same field from a different form [switchboard_dept]![hawk]. If the first criteria is false (i used the switchboard_dept form) it asks for the first criteria, is it possible to step through this without the user seeing this? or is it possible to put the criteria in macro's? ...

Few questions
1. I need license for 11nd station and Looks like I know more than my agent. I need new sales rep to buy 11nd license, scanner,drawer,Line display. If any one in california or Who Know what I need. They can contact me gill@goldrush.com 2. How I disable plus button. I hate when My cashier open register using plus button. New cashier make toomany overrings. 3. How I get Payout/Drop report ? When cashier makes mistake on pay out/ drop. I have to go through all the Journal view. Is there any other way I can view only payout/ drops ? Sir I did'nt see any attachment I am reviewing thr...

OWA with SSL question
Hello, I have build OWA server to Exchange 5.5 SP4, when I have login to the outlook I have not see there the lock and not get to https I have got the authentication negotiation for user and password but it goes thru port 80 not 443. How I can change it to work with SSL? Thanks in advanced. You have to set the requirements for SSL on the IIS virtual dir, and/or pages.. and have the appropriate keys intalled for IIS. It does not automagically redirect connections to the default http://server/exchange virutal to httpS://server/exchange that is up to you to enforce. "news.mic...

including blanks or text values in macro-entered formulas
When I try to use the following codes, I get error messages. How do get around the problem? Cells(2,2).Select ActiveCell.Formula = "=if(A1=1,"",1)" or Cells(2,2).Select ActiveCell.Formula = "=if(A1=1,"High","Low")" Thanks so much for any assistance Matthe -- Message posted from http://www.ExcelForum.com Just as in XL, quotation marks within a string need to be doubled (or use CHR(34)): ActiveCell.Formula = "=IF(A1=1,"""",1)" and ActiveCell.Formula = "=IF(A1=1,""High"",...

Printing question #3
My boss has an Excel spreadsheet that has 20 or so worksheets. On each worksheet there are several hyperlinks to web pages. He wants to print out each worksheet and all their related hyperlinked pages. Right now he has to do it manually which takes a lot of time. Is it possible to do this. If so, how? Thanks! -- looking... ...

CScrollBar Question
How do I reterive the width of a vertical scroll bar from the theme information so it looks consistant with the rest of the OS when doing a CScrollBar::Create? The word Theme is throwing me off a bit. I haven't personally used them yet. But in general if you want information about size of things you use GetSystemMetrics(...) SM_CXVSCROLL, SM_CYVSCROLL Width of a vertical scroll bar, in pixels; and height of the arrow bitmap on a vertical scroll bar, in pixels. AliR. <chris.dannemiller@gmail.com> wrote in message news:1132685006.629024.97570@g49g2000cwa.googlegroups....

Reminder feature question
hello, i was wondering if there is an option that will allow the reminder feature to operate <still pop-up the reminder>, even if outlook is closed. thank you indera This is a client feature that requires Outlook to be running. You can always sync your calendar with a PDA device and have that display the reminder. Hope this helps. -- Greg Mansius [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Dr. Indera" <dr.inderaBUGOFF@verizon.net> wrote in message news:%23iqSgS8JEHA.2456@TK2MSFTNGP12.phx.gbl... > hello, > ...

if, else questions
I have a sheet with 3 columns: A, B, C A is a dropdown with following values compile, edit, audit B is a dropdown and has the same values as A compile, edit, audit C is a text box that auto populates with either "Yes" or "No" depending on what we select for A and B If I select same values for A and B then I want a Yes for C If I select different values for A and B then I want a No Also, I have seen an IF statement in a cell that has text values. The IF statement compares two cells with text values but has a '>' and a '<' comparison made. For e...

EX2K3 SP1
Hi there, I upgraded to SP1. I have noticed over OWA that you are now enforced to log in via: DOMAIN\username instead of previously just username (OWA would prepend the DOMAIN\ section). Is there a way to get this "old" behaviour back? "Straife" <straife@myrealbox.com> wrote: >Hi there, > >I upgraded to SP1. I have noticed over OWA that you are now enforced to log >in via: > >DOMAIN\username > >instead of previously just username (OWA would prepend the DOMAIN\ section). > > >Is there a way to get this "old" behavio...

Vista questions
Hi, My father passed away recently and I received his laptop (with Vista Home Premium pre-installed) as a part of my inheritance. The problem is that the hardware is exclusionary. That is, the manufacturer made it so that all other operating systems are prohibited and I can't install any other operating system on it. As there is a sentimental value attached to the computer I prefer not to sell it, but so far the experience was frustrating and a reminder why I stopped using Windows in the first place. Below are some questions, solutions to which, would help make my ...

My 3 questions
Hi, all I have 3 three questions, could anybody give me a help, thanks a lot! 1) How to change the color of the pen in MFC? 2) How to make a application frame window start up at maximized size and user can not resize the window and move the window to other position? 3) How to hide the frame window title bar when application start up? -ja "ja" <josndrsn@yahoo.com> wrote in message news:%23O7cfWQ7GHA.940@TK2MSFTNGP03.phx.gbl... > Hi, all > I have 3 three questions, could anybody give me a help, thanks a lot! > 1) How to change the color of t...

Simple loop question
Hello, I have a control that has a number in it (e.g. 5) I need to repeat a task for the of time in my control. I guess it should be something like Dim MyCounter as long MyCounter = me.counter Do while myCounter = 0 ....do something mycounter = mycounter -1 loop Thak you, Silvio Change the "Do while myCounter = 0" line to: Do while myCounter > 0 ----- HTH Mr. B http://www.askdoctoraccess.com/ Doctor Access Downloads Page: http://www.askdoctoraccess.com/DownloadPage.htm "Silvio" wrote: > Hello, I have a control that has a...