Replace text with reference cell with the help of a code

[RAJESH1960]

Hello guys

I am trying to add a code in the beginning of the already existing code. I have 2 steps to write. I am not able to record the below with the help of a macro and to write the code is too complicated for me. Need help to understand and write the code.

• select the column C and replace “(as per details)” with the name in cell K1.
• In column G my application won’t work if the voucher numbers are same. So, I want to give the voucher numbers in the series of 1001 to as many vouchers are there in the sheet. In this case there are 11 vouchers. They have to be replaced with 1001 to 1011.

Test Multiple voucher workings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	W Test Purchases										Kotak Mahindra Bank
2	Kotak Mahindra Bank Book
3
4
5	1-Apr-2021 to 14-Jul-2021
6	Date	Particulars				Vch Type	Vch No.	Debit	Credit
7	01-07-2021	Cr	(as per details)			Receipt	1	4040.00
8			Sunday						1015.00 Cr
9			Monday						2025.00 Cr
10			Tuesday						1000.50 Cr
11			Round Off					0.50 Dr
12	01-07-2021	Dr	(as per details)			Payment	1		1001.00
13			January					100.00 Dr
14			February					200.00 Dr
15			March					300.00 Dr
16			April					400.00 Dr
17			Round Off					1.00 Dr
18	02-07-2021	Cr	(as per details)			Receipt	2	4040.00
19			Sunday						1015.00 Cr
20			Monday						2025.00 Cr
21			Tuesday						1000.50 Cr
22			Round Off					0.50 Dr
23	03-07-2021	Dr	(as per details)			Payment	2		1001.00
24			January					100.00 Dr
25			February					200.00 Dr
26			March					300.00 Dr
27			April					400.00 Dr
28			Round Off					1.00 Dr
29	04-07-2021	Dr	(as per details)			Payment	3		1001.00
30			January					100.00 Dr
31			February					200.00 Dr
32			March					300.00 Dr
33			April					400.00 Dr
34			Round Off					1.00 Dr
35	05-07-2021	Cr	(as per details)			Receipt	3	4040.00
36			Sunday						1015.00 Cr
37			Monday						2025.00 Cr
38			Tuesday						1000.50 Cr
39			Round Off					0.50 Dr
40	06-07-2021	Cr	Cash			Contra	1	1000.00
41	06-07-2021	Dr	Cash			Contra	2		2000.00
42	09-07-2021	Dr	(as per details)			Payment	8		1001.00
43			January					100.00 Dr
44			February					200.00 Dr
45			March					300.00 Dr
46			April					400.00 Dr
47			Round Off					1.00 Dr
48	14-07-2021	Dr	Cash			Contra	3		2000.00
49	14-07-2021	Cr	Cash			Contra	4	1000.00
50			14120.00						8004.00
51		Dr	Closing Balance						6116.00
52			14120.00						14120.00
Bank 5

 

[Snakehips]

@RAJESH1960 Maybe this?

Sub VchNos()
Dim NewName As String
Dim VchNo As Integer

NewName = Range("K1")
VchNo = 1000

For Each Cell In Range("G7:G" & Range("C" & Rows.Count).End(xlUp).row)
    If Not Cell = vbNullString Then
        VchNo = VchNo + 1
        Cell.Value = VchNo
     End If
     
    If Cell.Offset(0, -4) = "(as per details)" Then Cell.Offset(0, -4).Value = NewName
       
Next Cell
End Sub

 

[RAJESH1960]

@RAJESH1960 Maybe this?

Sub VchNos()
Dim NewName As String
Dim VchNo As Integer

NewName = Range("K1")
VchNo = 1000

For Each Cell In Range("G7:G" & Range("C" & Rows.Count).End(xlUp).row)
    If Not Cell = vbNullString Then
        VchNo = VchNo + 1
        Cell.Value = VchNo
     End If
    
    If Cell.Offset(0, -4) = "(as per details)" Then Cell.Offset(0, -4).Value = NewName
      
Next Cell
End Sub

Snakehips, I am getting a compile error - variable not defined ...For Each Cell In Range

 

[RAJESH1960]

Snakehips, I am getting a compile error - variable not defined ...For Each Cell In Range

I added a line Dim cell As Variant and it worked. Thanks Snakehips.?

 

[RAJESH1960]

Snakehips, I had to rearrange my data due to some problems. I am trying to run your code in a sheet where the columns have changed. I changed the code as per the columns. When I run the first half of the code separately it is running fine but when I add the second half where I have to replace “(as per details”) with the cell reference I am not able to get the result. I am getting a compile error. Can you please see what is wrong with the code.?
Sub VoucherNos()
Dim VchNo As Integer
Dim CELL As Variant

VchNo = 1000
For Each CELL In Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)
If Not CELL = vbNullString Then
VchNo = VchNo + 1
CELL.Value = VchNo
End If
Next
Dim NewName As String
NewName = Sheets("Original").Range("K1")
If CELL.Offset(0, -4) = "(as per details)" Then CELL.Offset(0, -4).Value = NewName

End With
End Sub

Book1
	A	B	C	D	E	F	G
1	Line	Date	Vch Type	Vch No.	Particulars	Debit	Credit
2	17	02-07-2021	Receipt	3	Sunday	1015.00
3	30	04-07-2021	Payment	6	January		100.00
4	36	06-07-2021	Contra	1	Cash	1000.00
5	37	06-07-2021	Contra	2	Cash		2000.00
6	44	11-07-2021	Receipt	11	Sunday	1015.00
7	45	11-07-2021	Payment	10	January		100.00
8	46	12-07-2021	Receipt	12	Sunday	1015.00
9	47	12-07-2021	Payment	11	January		100.00
10	48	14-07-2021	Contra	3	Cash		2000.00
11	49	14-07-2021	Contra	4	Cash	1000.00
12
13	1	01-07-2021	Receipt	1	(as per details)	4040.00
14	2				Sunday		1015.00
15	3				Monday		2025.00
16	4				Tuesday		1000.50
17	5				Round Off	0.50
18	6	01-07-2021	Payment	1	(as per details)		1001.00
19	7				January	100.00
20	8				February	200.00
21	9				March	300.00
22	10				April	400.00
23	11				Round Off	1.00
24	12	02-07-2021	Receipt	2	(as per details)	4040.00
25	13				Sunday		1015.00
26	14				Monday		2025.00
27	15				Tuesday		1000.50
28	16				Round Off	0.50
29	18	03-07-2021	Payment	2	(as per details)		1001.00
30	19				January	100.00
31	20				February	200.00
32	21				March	300.00
33	22				April	400.00
34	23				Round Off	1.00
35	24	04-07-2021	Payment	3	(as per details)		1001.00
36	25				January	100.00
37	26				February	200.00
38	27				March	300.00
39	28				April	400.00
40	29				Round Off	1.00
41	31	05-07-2021	Receipt	4	(as per details)	4040.00
42	32				Sunday		1015.00
43	33				Monday		2025.00
44	34				Tuesday		1000.50
45	35				Round Off	0.50
46	38	09-07-2021	Payment	9	(as per details)		1001.00
47	39				January	100.00
48	40				February	200.00
49	41				March	300.00
50	42				April	400.00
51	43				Round Off	1.00
Bank

 

[Snakehips]

@RAJESH1960 Try this.

Sub VchNos()
Dim NewName As String
Dim VchNo As Integer
Dim Cell As Range

NewName = Range("K1")
VchNo = 1000
'Step thro Voucher column D2:D'Lastrow'as determined by last entry in E
For Each Cell In Range("D2:D" & Range("E" & Rows.Count).End(xlUp).row)
    If Not Cell = vbNullString Then  'if not an empty cell then is a voucher number so update it
        VchNo = VchNo + 1
        Cell.Value = VchNo
     End If
 'Particular column is E which is 1 column offset right from D
 'Check and update if required
    If Cell.Offset(0, 1) = "(as per details)" Then Cell.Offset(0, 1).Value = NewName
       
Next Cell
End Sub

 

[RAJESH1960]

@RAJESH1960 Try this.

Sub VchNos()
Dim NewName As String
Dim VchNo As Integer
Dim Cell As Range

NewName = Range("K1")
VchNo = 1000
'Step thro Voucher column D2:D'Lastrow'as determined by last entry in E
For Each Cell In Range("D2:D" & Range("E" & Rows.Count).End(xlUp).row)
    If Not Cell = vbNullString Then  'if not an empty cell then is a voucher number so update it
        VchNo = VchNo + 1
        Cell.Value = VchNo
     End If
 'Particular column is E which is 1 column offset right from D
 'Check and update if required
    If Cell.Offset(0, 1) = "(as per details)" Then Cell.Offset(0, 1).Value = NewName
     
Next Cell
End Sub

checking.... I am a bit slow.

 

[RAJESH1960]

@RAJESH1960 Try this.

Sub VchNos()
Dim NewName As String
Dim VchNo As Integer
Dim Cell As Range

NewName = Range("K1")
VchNo = 1000
'Step thro Voucher column D2:D'Lastrow'as determined by last entry in E
For Each Cell In Range("D2:D" & Range("E" & Rows.Count).End(xlUp).row)
    If Not Cell = vbNullString Then  'if not an empty cell then is a voucher number so update it
        VchNo = VchNo + 1
        Cell.Value = VchNo
     End If
 'Particular column is E which is 1 column offset right from D
 'Check and update if required
    If Cell.Offset(0, 1) = "(as per details)" Then Cell.Offset(0, 1).Value = NewName
      
Next Cell
End Sub

I had to change just one line and it worked perfectly. Thanks once again Snakehips.
NewName = Sheets("Original").Range("K1")

 

[RAJESH1960]

Thanks for the explanation line by line. It really helps me to understand better.

I had to change just one line and it worked perfectly. Thanks once again Snakehips.
NewName = Sheets("Original").Range("K1")