Code needs to be corrected

[RAJESH1960]

Hello guys

I have this code running through four sheets. The code is running perfectly but for one small error due to which I am not able to import it to my software. In sheet F, cell F18 should be blank. Can someone please check and edit the code so that the cell F18 is blank.

As I am unaware how to post the workbook, I am posting 2 sheets for reference. The sheet “Z” is blank. The sheet original has the bank name in cell K1.

I have inserted a button in sheet F to run the code.

acxCode-correction.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Remark	Date	Voucher Type	Voucher No.	Narration	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name
2	Matched	01-07-2021	Receipt	1011		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
3	Matched	01-07-2021	Payment	1012		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
4	Matched	02-07-2021	Receipt	1013		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
5	Matched	03-07-2021	Payment	1014		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
6	Matched	04-07-2021	Payment	1015		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
7	Matched	05-07-2021	Receipt	1016		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
8	Matched	09-07-2021	Payment	1017		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
9										0		0		0		0		0
10										0		0		0		0		0
11
F

Cell Formulas
Range	Formula
A2:A11	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(OR(B2="",C2="",F2="",G2="",H2="",I2=""),"Debit/Credit Not Matching",IF(BG2=0,"Matched","Debit/Credit Not Matching")))

acxCode-correction.xlsm
	A	B	C	D	E	F	G
1	Line	Date	Vch Type	Vch No.	Particulars	Debit	Credit
2	30	04-07-2021	Payment	1002	January		100.00
3	36	06-07-2021	Contra	1003	Cash	1000.00
4	37	06-07-2021	Contra	1004	Cash		2000.00
5	44	11-07-2021	Receipt	1005	Sunday	1015.00
6	45	11-07-2021	Payment	1006	January		100.00
7	46	12-07-2021	Receipt	1007	Sunday	1015.00
8	47	12-07-2021	Payment	1008	January		100.00
9	48	14-07-2021	Contra	1009	Cash		2000.00
10	49	14-07-2021	Contra	1010	Cash	1000.00
11
12	1	01-07-2021	Receipt	1011	Kotak Mahindra Bank	4040.00
13	2				Sunday		1015.00
14	3				Monday		2025.00
15	4				Tuesday		1000.50
16	5				Round Off	0.50
17	6	01-07-2021	Payment	1012	Kotak Mahindra Bank		1001.00
18	7				January	100.00
19	8				February	200.00
20	9				March	300.00
21	10				April	400.00
22	11				Round Off	1.00
23	12	02-07-2021	Receipt	1013	Kotak Mahindra Bank	4040.00
24	13				Sunday		1015.00
25	14				Monday		2025.00
26	15				Tuesday		1000.50
27	16				Round Off	0.50
28	18	03-07-2021	Payment	1014	Kotak Mahindra Bank		1001.00
29	19				January	100.00
30	20				February	200.00
31	21				March	300.00
32	22				April	400.00
33	23				Round Off	1.00
34	24	04-07-2021	Payment	1015	Kotak Mahindra Bank		1001.00
35	25				January	100.00
36	26				February	200.00
37	27				March	300.00
38	28				April	400.00
39	29				Round Off	1.00
40	31	05-07-2021	Receipt	1016	Kotak Mahindra Bank	4040.00
41	32				Sunday		1015.00
42	33				Monday		2025.00
43	34				Tuesday		1000.50
44	35				Round Off	0.50
45	38	09-07-2021	Payment	1017	Kotak Mahindra Bank		1001.00
46	39				January	100.00
47	40				February	200.00
48	41				March	300.00
49	42				April	400.00
50	43				Round Off	1.00
Bank

 

[RAJESH1960]

Oops Sorry. I forgot to post the code.
Option Explicit

Sub Step4()
'single entries to copy below multiple entries

Sheets("Bank").Select
Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Z").Select
Range("A2").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Columns("F:G").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("F2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""",RC[3],-RC[2])"
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=-RC[-1]"
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G21")
Range("F2:G21").Select
Range("F2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select

Dim sh As Worksheet
Dim NewName As String
Dim sh1 As Worksheet
Dim p As Variant
Dim pp As Variant
Dim x As Integer
Dim s()
Dim ss()
Application.ScreenUpdating = False
Application.CutCopyMode = False
Set sh = Sheets("Z")
Set sh1 = Sheets("F")
NewName = Sheets("Original").Range("K1")
s = Array(2, 3, 4, 5, 6, 7)
ss = Array(2, 3, 4, 8, 7, 9)
p = sh.Cells(Rows.Count, 1).End(3).Row
pp = sh1.Cells(Rows.Count, 3).End(3).Row + 1
For x = 0 To UBound(s)
sh.Range(sh.Cells(3, s(x)), sh.Cells(p, s(x))).Copy sh1.Cells(pp, ss(x))
Next x
sh1.Cells(pp, "f").Resize(p - 1) = NewName
Application.ScreenUpdating = True
Application.CutCopyMode = True
Sheets("F").Select
Range("B9:I19").Select
End Sub

 

[RAJESH1960]

Hello guys

I have this code running through four sheets. The code is running perfectly but for one small error due to which I am not able to import it to my software. In sheet F, cell F18 should be blank. Can someone please check and edit the code so that the cell F18 is blank.

As I am unaware how to post the workbook, I am posting 2 sheets for reference. The sheet “Z” is blank. The sheet original has the bank name in cell K1.

I have inserted a button in sheet F to run the code.

acxCode-correction.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Remark	Date	Voucher Type	Voucher No.	Narration	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name	Dr(-), Cr(+)	Ledger Name
2	Matched	01-07-2021	Receipt	1011		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
3	Matched	01-07-2021	Payment	1012		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
4	Matched	02-07-2021	Receipt	1013		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
5	Matched	03-07-2021	Payment	1014		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
6	Matched	04-07-2021	Payment	1015		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
7	Matched	05-07-2021	Receipt	1016		Kotak Mahindra Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
8	Matched	09-07-2021	Payment	1017		Kotak Mahindra Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
9										0		0		0		0		0
10										0		0		0		0		0
11
F

Cell Formulas
Range	Formula
A2:A11	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(OR(B2="",C2="",F2="",G2="",H2="",I2=""),"Debit/Credit Not Matching",IF(BG2=0,"Matched","Debit/Credit Not Matching")))

acxCode-correction.xlsm
	A	B	C	D	E	F	G
1	Line	Date	Vch Type	Vch No.	Particulars	Debit	Credit
2	30	04-07-2021	Payment	1002	January		100.00
3	36	06-07-2021	Contra	1003	Cash	1000.00
4	37	06-07-2021	Contra	1004	Cash		2000.00
5	44	11-07-2021	Receipt	1005	Sunday	1015.00
6	45	11-07-2021	Payment	1006	January		100.00
7	46	12-07-2021	Receipt	1007	Sunday	1015.00
8	47	12-07-2021	Payment	1008	January		100.00
9	48	14-07-2021	Contra	1009	Cash		2000.00
10	49	14-07-2021	Contra	1010	Cash	1000.00
11
12	1	01-07-2021	Receipt	1011	Kotak Mahindra Bank	4040.00
13	2				Sunday		1015.00
14	3				Monday		2025.00
15	4				Tuesday		1000.50
16	5				Round Off	0.50
17	6	01-07-2021	Payment	1012	Kotak Mahindra Bank		1001.00
18	7				January	100.00
19	8				February	200.00
20	9				March	300.00
21	10				April	400.00
22	11				Round Off	1.00
23	12	02-07-2021	Receipt	1013	Kotak Mahindra Bank	4040.00
24	13				Sunday		1015.00
25	14				Monday		2025.00
26	15				Tuesday		1000.50
27	16				Round Off	0.50
28	18	03-07-2021	Payment	1014	Kotak Mahindra Bank		1001.00
29	19				January	100.00
30	20				February	200.00
31	21				March	300.00
32	22				April	400.00
33	23				Round Off	1.00
34	24	04-07-2021	Payment	1015	Kotak Mahindra Bank		1001.00
35	25				January	100.00
36	26				February	200.00
37	27				March	300.00
38	28				April	400.00
39	29				Round Off	1.00
40	31	05-07-2021	Receipt	1016	Kotak Mahindra Bank	4040.00
41	32				Sunday		1015.00
42	33				Monday		2025.00
43	34				Tuesday		1000.50
44	35				Round Off	0.50
45	38	09-07-2021	Payment	1017	Kotak Mahindra Bank		1001.00
46	39				January	100.00
47	40				February	200.00
48	41				March	300.00
49	42				April	400.00
50	43				Round Off	1.00
Bank

Problem is solved.