Hi All
My code works if I have more than 3 rows in current region.
When I have 2 rows the source range and end range are the same IE a cell at activecell offset 1,5
Therefore the fillrange doesn't make sense.
How do I get this code to "copy and paste" in the same cell?
Spreadsheet example :
<tbody>
</tbody>
My code works if I have more than 3 rows in current region.
When I have 2 rows the source range and end range are the same IE a cell at activecell offset 1,5
Therefore the fillrange doesn't make sense.
How do I get this code to "copy and paste" in the same cell?
Spreadsheet example :
| Material / SITE | Size | Supplier | Cost | CEF | Total Cost |
| aaa | 5lt | jjj | R 10.20 | 2 | 20.40 |
| sss | 5lt | uu | R 12.50 | 2 | 25.00 |
| ddd | 5lt | yg | R 51.30 | 2 | 102.60 |
| fff | 5lt | t | R 88.88 | 2 | 177.76 |
| ccc | 5lt | hio | R 65.52 | 1 | 65.52 |
| hhh | tin | edf | R 54.69 | 1 | 54.69 |
| jjj | 5lt | sas | R 69.67 | 2 | 139.34 |
| uuu | Pair | xvb | R 12.36 | 4 | 49.44 |
| lll | 20Pk | ejh | R 36.25 | 10 | 362.50 |
| ppp | 20Pk | khg | R 36.10 | 10 | 361.00 |
| rvt | 10Pk | jh | R 14.57 | 10 | 145.68 |
| 1503.93 | |||||
| Material / SITE | Size | Supplier | Cost | ABC | Total Cost |
| aaa | 5lt | bbb | R 10.20 | 6 | =E1276*D1276 |
| =SUM(F1276) |
<tbody>
</tbody>
Code:
Dim SourceRange As Range
Dim EndRange As Range
Dim FillRange As Range
ActiveCell.Offset(0, 5).Value = "Total Cost"
ActiveCell.Offset(1, 5).NumberFormat = "0.00"
ActiveCell.Offset(1, 5).Formula = "=IFERROR(" & ActiveCell.Offset(1, 3).Address(False, False) & "*" & ActiveCell.Offset(1, 4) _
.Address(False, False) & ","""")"
Set SourceRange = ActiveCell.Offset(1, 5)
Set EndRange = ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count - 1, 5)
Set FillRange = Range(SourceRange, EndRange)
ActiveCell.Offset(1, 5).Formula = "=IFERROR(" & ActiveCell.Offset(1, 3).Address(False, False) & "*" & ActiveCell.Offset(1, 4) _
.Address(False, False) & ","""")"
SourceRange.AutoFill Destination:=FillRange
Set SourceRange = Nothing
Set EndRange = Nothing
Set FillRange = Nothing
ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count, 5).Formula = "=SUM(" & ActiveCell.Offset(1, 5).Address & ":" & ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count - 1, 5).Address & ")"
