Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
COL A: Items
A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Thanks for your response Bob. Unfortunately, I'm not really proficient at
this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
No, you can't have a formula and a value in a cell, so it has to be VBA.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Bob,
I Copy and Paste your code exactly the way you wrote it. is there anything else I need to know or change? Is: '<== change to suit, on your codes for me or do it as is? Thanks again. "Bob Phillips" wrote: No, you can't have a formula and a value in a cell, so it has to be VBA. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Bob,
Your code worked. I just have to incorporate it with my spreadsheet. Thanks for your help. "Bob Phillips" wrote: No, you can't have a formula and a value in a cell, so it has to be VBA. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Bob,
Your code is actually working great for me. I just have one request, I wanted to add a cell where I can actually add to the Balance Inventory. So Basically Column D is to add to Column B. Thanks in advance. COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: COL D: RESTOCK D1: D2: D3: "Bob Phillips" wrote: No, you can't have a formula and a value in a cell, so it has to be VBA. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
The code that I gave takes the value in C and adds it to the value in B and
clears C. Are you saying that now you want to take the value in D and add it to B? Clear D after? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Bob, Your code is actually working great for me. I just have one request, I wanted to add a cell where I can actually add to the Balance Inventory. So Basically Column D is to add to Column B. Thanks in advance. COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: COL D: RESTOCK D1: D2: D3: "Bob Phillips" wrote: No, you can't have a formula and a value in a cell, so it has to be VBA. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with Macro
Bob,
The code you gave me worked. I just need to add more. Col B is the current Balance. Col C is the items used so it needs to be subracted from the current balance. Col D is items added to the balance so number should be added to col B. Thanks a bunch. "Bob Phillips" wrote: The code that I gave takes the value in C and adds it to the value in B and clears C. Are you saying that now you want to take the value in D and add it to B? Clear D after? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Bob, Your code is actually working great for me. I just have one request, I wanted to add a cell where I can actually add to the Balance Inventory. So Basically Column D is to add to Column B. Thanks in advance. COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: COL D: RESTOCK D1: D2: D3: "Bob Phillips" wrote: No, you can't have a formula and a value in a cell, so it has to be VBA. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... Thanks for your response Bob. Unfortunately, I'm not really proficient at this so I can't apply it to my spreadsheet. I thought I did what you told me to do but to no avail. Is there a formula I can use instead of Macro. Say: A1 is the current balance. If I input a number on A2 (Used), A1 is automatically updated with the current balance (meaning A2 is automatically subracted from A1). Thanks again. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C:C" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If IsNumeric(.Offset(0, -1).Value) Then .Offset(0, -1).Value = .Offset(0, -1).Value - .Value .Value = "" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) " wrote in message ... COL A: Items A1: "Item 1" A2: "Item 2" A3: "Item 3" COL B: Current Balance B1: "100" B2: "150" B3: "205" COL C: USED C1: C2: C3: I need a Macro wriiten to subtract Column C from Column B; update Column B of the current balance and delete the number used at column C as soon as the current balance is updated. I hope I'm clear with my request. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |