Assigning consecutive numbers to certain rows with data

Esinvancouver

New Member
Joined
Nov 24, 2005
Messages
3
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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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

Application.DisplayAlerts = False
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
Application.DisplayAlerts = True

End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
2datadata2
3datadata3
4datadata4
5 
6datadata5
7datadata6
8 
9datadata7
10datadata8
11datadata9
12data10
13datadata11
14data12
15data13
16data14
17data15
18datadata16
19 
20 
21datadata17
22datadata18
sheet1


kind regards,
Erik

EDIT: changed just one character COUNT instead of COUNTA
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Esinvancouver

New Member
Joined
Nov 24, 2005
Messages
3
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. :cry:

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.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Erik, your formula almost works... there are just some numbers that were skipped
:oops: 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,352
Messages
5,595,655
Members
414,006
Latest member
Davefromlondon

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?

Disable AdBlock

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
Top