Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Number Formula
Is this what you mean?
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<=== change to suit Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "Invoice" And .Offset(0, 2).Value = "" Then nMax = Me.Evaluate("MAX(IF(A1:A1000=""Invoice"",C1:C1000) )") .Offset(0, 2).Value = nMax + 1 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 Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Number Formula
Hi, Bob, thanks for responding. I tried entering the code by right clicking,
choosing "View Code" and pasting it in, but when I try to run it I get an error message "Can't Find Project or Library". Maybe I'm not running it right? I tried choosing "Run", which didn't work, then I tried typing "=Worksheet_Change" in a blank cell on the spreadsheet. Is this how the macro should be run? Sorry if this is a stupid question. I just don't know much about VBA and macros. "Bob Phillips" wrote: Is this what you mean? Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<=== change to suit Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "Invoice" And .Offset(0, 2).Value = "" Then nMax = Me.Evaluate("MAX(IF(A1:A1000=""Invoice"",C1:C1000) )") .Offset(0, 2).Value = nMax + 1 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 Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Number Formula
it should automatically trigger when you change anything ion column a on
that worksheet. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, Bob, thanks for responding. I tried entering the code by right clicking, choosing "View Code" and pasting it in, but when I try to run it I get an error message "Can't Find Project or Library". Maybe I'm not running it right? I tried choosing "Run", which didn't work, then I tried typing "=Worksheet_Change" in a blank cell on the spreadsheet. Is this how the macro should be run? Sorry if this is a stupid question. I just don't know much about VBA and macros. "Bob Phillips" wrote: Is this what you mean? Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<=== change to suit Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "Invoice" And .Offset(0, 2).Value = "" Then nMax = Me.Evaluate("MAX(IF(A1:A1000=""Invoice"",C1:C1000) )") .Offset(0, 2).Value = nMax + 1 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 Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Number Formula
Hi, Bob, okay, now I get it. When I type "Invoice" in any cell in column A
then column C changes. I misunderstood how it worked. Thanks for your help! :) "Bob Phillips" wrote: it should automatically trigger when you change anything ion column a on that worksheet. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, Bob, thanks for responding. I tried entering the code by right clicking, choosing "View Code" and pasting it in, but when I try to run it I get an error message "Can't Find Project or Library". Maybe I'm not running it right? I tried choosing "Run", which didn't work, then I tried typing "=Worksheet_Change" in a blank cell on the spreadsheet. Is this how the macro should be run? Sorry if this is a stupid question. I just don't know much about VBA and macros. "Bob Phillips" wrote: Is this what you mean? Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<=== change to suit Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "Invoice" And .Offset(0, 2).Value = "" Then nMax = Me.Evaluate("MAX(IF(A1:A1000=""Invoice"",C1:C1000) )") .Offset(0, 2).Value = nMax + 1 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 Phillips (replace somewhere in email address with gmail if mailing direct) "FJ" wrote in message ... Hi, I need to create an automatic numbering formula in Excel that will work as follows: For every row that has the word "Invoice" in cell A1, I want cell C1 to number automatically. There are also blank rows in the spreadsheet, and there will be rows added and deleted from time to time. Is there any way to write a formula to automatically number something like this, updating the numbering when rows are added and deleted? I've tried various "IF" and "COUNT" formulas, but to no avail. I hope I've explained this clearly. Thanks in advance for any information. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
If formula | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
formula that gives me a random number less than other number | Excel Worksheet Functions | |||
Looking for function or formula to calculate number that is revers | Excel Worksheet Functions |