#1
|
||||
|
||||
Auto Numbering of data except blanks starting from number n
I spent hours finding the formula for auto numbering of column A of several linked sheets starting from a given number but I always got error messages after a blank cell. I do not know where to position the starting number in the formulas that I tried. If I type 58 on say Cell A34 of sheet 1, the rest of the numbers down the rows of the active sheet and other sheets should follow. There should be blank in the Number column if the data in Column B is blank. Please help.
Sheet 1 A-----------------------------B No.--------------------------Data 58---------------------------Red --------------------------- 59---------------------------White --------------------------- --------------------------- 60---------------------------Red Sheet 2 A----------------------------B No.--------------------------Data 61---------------------------Apple --------------------------- 62---------------------------Lemon 63---------------------------Orange Sheet 3 A----------------------------B No.--------------------------Data 64---------------------------Cow 65---------------------------Carabao |
#2
|
|||
|
|||
What formula are you using ?
|
#3
|
|||
|
|||
.
Here is a VBA approach : Code:
Option Explicit Sub numcells() Dim i As Integer Dim x As Range i = Range("C2").Value For Each x In Range("B2:B100") If x <> "" Then x.Offset(0, -1) = i i = i + 1 End If Next End Sub |
#4
|
|||
|
|||
another possibility:
assuming the 58 in your example is in A34, try this in A35 and drag down Code:
=IF(B35="","",MAX($A$34:$A34)+1) Code:
=MAX(Sheet1!$A$34:$A$500)+1 |
#5
|
||||
|
||||
Thank you, but the formula returns numbers that automatically start at 1, it should be 59, the next number after 58. I have copied the sample made by Logit with a macro. I would prefer a formula if it could be done please.
|
#6
|
||||
|
||||
Please read my previous message, in reply to your formula. I have attached the sample data. Thanks.
|
#7
|
||||
|
||||
The macro doesn't work if I add another month with separate serial numbers in sheet 1.
|
#8
|
|||
|
|||
That's because your post didn't mention anything about additional ranges where you would be starting over with a new number.
|
#9
|
|||
|
|||
.
Provide a nun working example of your spreadsheet, how you envision it will be when all is completed. If you have additional ranges that will begin with a different number, be certain you show that on the worksheet. If there will be different ranges in different columns, show those as well. It is best to provide all of the information to begin with, rather than provide it piecemeal. Something you determine is needed after receiving a working solution may very well interfere with the first provided solution - as has occurred with your last post. |
#10
|
||||
|
||||
Remiss of me. My knowledge of macro is limited to recording of repetitious commands. I am excited when others like you introduce a macro that I could practice on in my work. I have attached the sample transactions, and added some data. I'm living in the boondocks, internet signal here is erratic.
|
#11
|
||||
|
||||
Quote:
|
#12
|
||||
|
||||
Quote:
|
#13
|
|||
|
|||
.
Here is the updated macro : Code:
Option Explicit Sub numcells() Dim i As Integer Dim x As Range For Each x In Range("B5:B100") '<-- Edit range as required. If IsNumeric(x.Offset(0, -1).Value) And x.Offset(0, -1).Value > 0 Then i = x.Offset(0, -1).Value End If If x <> "" Then If x.Offset(0, -1) = "" Then x.Offset(0, -1) = i i = i + 1 End If End If Next End Sub |
#14
|
||||
|
||||
Thank you Logit for the updated macro. I will copy this to my actual worksheet and see how it goes. I am using NoSparks formula in the meantime. Could you please help in my previous post about re How to Return Data from a List into a Different Form...
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return Multiple values from data except blanks with two criteria | Marcia | Excel | 7 | 09-07-2018 12:38 AM |
Delete starting number *) and delete Unneeded data in series | frustrated teacher | Word VBA | 5 | 12-07-2014 06:53 PM |
Starting page numbering after initial pages | plradoff | Word | 3 | 02-15-2014 08:59 PM |
Copying data from sheet with deleted columns creates blanks | ZGreyArea | Excel | 1 | 11-20-2013 10:12 AM |
Page numbering starting on 2 after section break | pamm13 | Word | 1 | 06-22-2011 11:10 AM |