Hi Team,
I am Reading data into Array, Calculating and putting back into Range.
code is working but I am loosing formula which is in Column E Amount Column.
how to fix it. Because in My project I have lot of such formula I may loose it.
Below is my data..
Thanks
mg
I am Reading data into Array, Calculating and putting back into Range.
code is working but I am loosing formula which is in Column E Amount Column.
how to fix it. Because in My project I have lot of such formula I may loose it.
VBA Code:
Option Explicit
Sub Read_and_Calculate_InMemory()
Dim arr As Variant
arr = Range("A2:e7").Value2
Dim i As Long
For i = LBound(arr, 1) To UBound(arr, 1)
' Multiply sales amount
arr(i, 4) = arr(i, 4) * 2
Next i
' Get the output range - it must be the same size as the array (arr)
Dim lRows As Long, lColumns As Long
lRows = UBound(arr, 1)
lColumns = UBound(arr, 2)
' Resize the output range so it matches the size of the array
Dim rgOut As Range
Set rgOut = Range("g2").Resize(lRows, lColumns)
' Write out the data from the array
rgOut.Value = arr
End Sub
Below is my data..
02_ReadData_Array.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Date | Quantity | Price | Sales | Amount | Date | Quantity | Price | Sales | Amount | |||
2 | 16/05/2017 | 3 | 10 | 9766.00 | 30 | ||||||||
3 | 24/04/2017 | 3 | 20 | 8427.00 | 60 | ||||||||
4 | 19/12/2016 | 9 | 30 | 7853.00 | 270 | ||||||||
5 | 16/05/2017 | 3 | 40 | 9766.00 | 120 | ||||||||
6 | 01/05/2018 | 2 | 50 | 8731.00 | 100 | ||||||||
7 | 29/09/2017 | 1 | 60 | 7492.00 | 60 | ||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E7 | E2 | =B2*C2 |
Thanks
mg