RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello Guys,
In the code, the formula is copied from G1 & H1 and when I double click it, the line fills the formula till the G1:H22 range which is correct in this case. But the range could be different in other cases. When I copy the whole range and paste it below the data the formula is again calculated till G1:H22 which is not right. How do I change the code so that it will work till the last value of the cell?
Option Explicit
Sub AutoFill()
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
Range("G1:H1").Select
Selection.AutoFill Destination:=Range("G1:H23")
Range("G1:H22").Select
End Sub
In the code, the formula is copied from G1 & H1 and when I double click it, the line fills the formula till the G1:H22 range which is correct in this case. But the range could be different in other cases. When I copy the whole range and paste it below the data the formula is again calculated till G1:H22 which is not right. How do I change the code so that it will work till the last value of the cell?
Option Explicit
Sub AutoFill()
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
Range("G1:H1").Select
Selection.AutoFill Destination:=Range("G1:H23")
Range("G1:H22").Select
End Sub
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | 1 | 30-06-2021 | Receipt | 100 | (as per details) | 4040 | ||||||
2 | 2 | 30-06-2021 | Receipt | 100 | Sunday | 1015.00 | ||||||
3 | 3 | 30-06-2021 | Receipt | 100 | Monday | 2025.00 | ||||||
4 | 4 | 30-06-2021 | Receipt | 100 | Tuesday | 1000.50 | ||||||
5 | 5 | 30-06-2021 | Receipt | 100 | Round Off | 1 | ||||||
6 | 8 | 04-07-2021 | Payment | 103 | (as per details) | 1001.00 | ||||||
7 | 9 | 04-07-2021 | Payment | 103 | January | 100 | ||||||
8 | 10 | 04-07-2021 | Payment | 103 | February | 200 | ||||||
9 | 11 | 04-07-2021 | Payment | 103 | March | 300 | ||||||
10 | 12 | 04-07-2021 | Payment | 103 | April | 400 | ||||||
11 | 13 | 04-07-2021 | Payment | 103 | Round Off | 1 | ||||||
12 | 14 | 05-07-2021 | Receipt | 104 | (as per details) | 4040 | ||||||
13 | 15 | 05-07-2021 | Receipt | 104 | Sunday | 1015.00 | ||||||
14 | 16 | 05-07-2021 | Receipt | 104 | Monday | 2025.00 | ||||||
15 | 17 | 05-07-2021 | Receipt | 104 | Tuesday | 1000.50 | ||||||
16 | 18 | 05-07-2021 | Receipt | 104 | Round Off | 1 | ||||||
17 | 21 | 09-07-2021 | Payment | 107 | (as per details) | 1001.00 | ||||||
18 | 22 | 09-07-2021 | Payment | 107 | January | 100 | ||||||
19 | 23 | 09-07-2021 | Payment | 107 | February | 200 | ||||||
20 | 24 | 09-07-2021 | Payment | 107 | March | 300 | ||||||
21 | 25 | 09-07-2021 | Payment | 107 | April | 400 | ||||||
22 | 26 | 09-07-2021 | Payment | 107 | Round Off | 1 | ||||||
A |