# Assigning consecutive numbers to certain rows with data

#### Esinvancouver

##### New Member
Hi. This is my first post. I find that there is a wealth of information on these boards but could not find the answer to my question.

Every month I need to do the same procedure several times, and there must be a way to write a macro for it.

I do accounting and I need to assign a number to each line of my journal entries, which I do in excel. I need to assign consecutive 4-digit numbers in column E for rows that have data in either column C and column D. When there is no data in either column C and column D, column E will be blank. When there is again some data in either column C and column D, column E will be the next number up from the previous one, where the previous number may be one or more rows away. The 4-digit numbers does not necessary start from 0001. It may start from, say, 5602 and is determined by the first number I input in column E. An example is shown below.

(Column A),(Column B),(Column C),(Column D),(Column E)
Entry#1, Cash, 100,(blank), 5602
(blank), Sales, (blank), 100, 5603
To record sales, (blank), (blank), (blank), (blank)
(blank row)
Entry#2, Miscellaneous, 50, (blank), 5604
(blank), Office expense, (blank), (blank), (blank)
(blank), Travel expense, (blank), (blank), (blank)
(blank), Telephone, 20, (blank), 5605
(blank), Cash, (blank), 70, 5606
To record expenses, (blank), (blank), (blank), (blank)
(blank row)
Entry #3, Accounting fees, 150, (blank), 5607
... and so on...

I'm using Excel2002. Any help would be very much appreciated. Thanks a lot!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to MrExcel!

No, it doesn't have a header row now. But I suppose I can add one if it's required...

Does this code work for you? Note that you don't have to enter a number in column E, you're prompted for the starting number.
Code:
``````Option Explicit

Sub GenerateNumbers()
'Written by Barrie Davidson
Dim DataRange As Range
Dim FilteredRange As Range, c As Range
Dim LineNumber As Double

Rows("1:1").Insert Shift:=xlDown
Range("A1").FormulaR1C1 = "1"
Range("B1").FormulaR1C1 = "2"
Range("C1").FormulaR1C1 = "3"
Range("D1").FormulaR1C1 = "4"
Range("E1").FormulaR1C1 = "5"
Set DataRange = Range("A1:E" & _
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
DataRange.Offset(1, 4).Resize(DataRange.Rows.Count - 1, 1).FormulaR1C1 = _
"=OR(ISBLANK(RC[-2])=FALSE,ISBLANK(RC[-1])=FALSE)"
DataRange.AutoFilter Field:=5, Criteria1:="TRUE"
On Error Resume Next
Set FilteredRange = DataRange.Offset(1, 4).Resize(DataRange.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If FilteredRange Is Nothing Then
DataRange.Offset(1, 4).Resize(DataRange.Rows.Count - 1, 1).ClearContents
GoTo ExitRoutine
End If
LineNumber = CDbl(InputBox("Enter beginning line number"))
For Each c In FilteredRange
c = LineNumber
LineNumber = LineNumber + 1
Next c
DataRange.AutoFilter Field:=5, Criteria1:="FALSE"
On Error Resume Next
Set FilteredRange = DataRange.Offset(1, 4).Resize(DataRange.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If FilteredRange Is Nothing Then
GoTo ExitRoutine
Else
FilteredRange.ClearContents
End If
ExitRoutine:
DataRange.AutoFilter
Rows("1:1").Delete

End Sub``````

Hello, Esinvancouver !
Welcome to the Board !!!!

you're asking for a macro
but a formula would to to my sense
Code:
``=IF(COUNTA(C2:D2)=0,"",COUNT(\$E\$1:E1)+\$E\$1)``
copied down
if you prefer VBA then it could use this technique to have fast results (instead of looping through all rows)
Map1.xls
ABCDE
11
5
8
12data10
14data12
15data13
16data14
17data15
19
20
sheet1

kind regards,
Erik

EDIT: changed just one character COUNT instead of COUNTA

using code
Code:
``````Option Explicit

Sub enumerate_data()
Dim LR As Long

Const FR = 2    'first row with data
LR = Columns("C:D").Find("*", [C1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row

With Range(Cells(FR, "E"), Cells(LR, "E"))
.Formula = "=IF(COUNTA(RC[-2]:RC[-1])=0,"""",COUNT(R1C5:R[-1]C)+R1C5)"
.Value = .Value
End With
End Sub``````

Or another possible code solution…
The following assumes:
(1) That this will be run with your “journal” sheet being the active sheet.
(2) That column E is blank except for the numbers you mentioned.
Code:
``````Sub Demo()
Dim c As Range, Rng As Range
Dim StrtRw As Long, LstRw As Long, NxtNum As Long

StrtRw = Columns("E").Find(What:="*", After:=[E1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LstRw = Columns("C:D").Find(What:="*", After:=[C1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set Rng = Range(Cells(StrtRw, 3), Cells(LstRw, 3))
NxtNum = Cells(StrtRw, 5).Value

For Each c In Rng
If c <> "" Or c(1, 2) <> "" Then
c(1, 3).Value = NxtNum
NxtNum = NxtNum + 1
End If
Next c
End Sub``````
Hope it helps.
Got to go. Have a huge bird I've got to cook.

Happy Thanksgiving to all, whether you celebrate it or not.
Dan

[EDIT:]
Changed the parameters for Rng to avoid overwriting previously existing numbers with new ones.

Hi Esinvancouver:

Here is a formula based solution in line with Erik's thinking, based on the data you posted ...
y051124h1.xls
ABCDEF
15601<--Given
2Entry#1Cash1005602
3Sales1005603
4To record sales
5
6Entry#2Miscellaneous505604
7Office expenses
8travel expenses
9telephone205605
10cash705606
11To record expenses
12
13Entry #3Accounting fees1505607
14
Sheet3

formula in cell E2 is ... =IF(COUNT(\$C2:\$D2),COUNTIF(\$C\$2:\$D2,"<>")+\$E\$1,"")

this is then copied down

Wow, thanks for the responses!

Barrie, your macro works like a charm. Unfortunately, I just realized that my numbers need to be generated in column G and not column E, and I don't know how to modify the macro for this.

Erik, your formula almost works... there are just some numbers that were skipped.

Yogi, I ended up modifying your formula because it is counting the blank cells that have an "IF" formula inside. I changed it to
=IF(COUNT(\$C2:\$D2),COUNT(\$C\$2:\$D2)+\$E\$1,"") and now it seems to work.

Once again, thanks for all your help!!!

Erik, your formula almost works... there are just some numbers that were skipped
oops, this was a translationproblem: working in Dutch, wrongly translated the second syntax
you must have COUNT instead of COUNTA

I edited my posts: what a difference in one single character !!

should work very nicely now !

best regards,
Erik

Replies
8
Views
147
Replies
2
Views
229
Replies
5
Views
507
Replies
1
Views
338
Replies
2
Views
324

1,216,536
Messages
6,131,231
Members
449,637
Latest member
icecreampie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back