Assigning consecutive numbers to certain rows with data

[Esinvancouver]

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!

 

[Barrie Davidson]

Welcome to MrExcel!

Does your spreadsheet have a header row?

 

[Esinvancouver]

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

 

[Barrie Davidson]

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.

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]

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

you're asking for a macro
but a formula would to to my sense

=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
	A	B	C	D	E
1					1
2			data	data	2
3			data	data	3
4			data	data	4
5
6			data	data	5
7			data	data	6
8
9			data	data	7
10			data	data	8
11			data	data	9
12				data	10
13			data	data	11
14			data		12
15			data		13
16			data		14
17				data	15
18			data	data	16
19
20
21			data	data	17
22			data	data	18
sheet1

kind regards,
Erik

EDIT: changed just one character COUNT instead of COUNTA

 

[erik.van.geit]

using 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]

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.

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]

Hi Esinvancouver:

Here is a formula based solution in line with Erik's thinking, based on the data you posted ...

y051124h1.xls
	A	B	C	D	E	F
1					5601	<--Given
2	Entry#1	Cash	100		5602
3		Sales		100	5603
4	To record sales
5
6	Entry#2	Miscellaneous	50		5604
7		Office expenses
8		travel expenses
9		telephone	20		5605
10		cash		70	5606
11	To record expenses
12
13	Entry #3	Accounting fees	150		5607
14
Sheet3

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

this is then copied down

 

[Esinvancouver]

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

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