vba help - read data into array print output ... loosing formula

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
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.


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
ABCDEFGHIJK
1DateQuantityPriceSalesAmountDateQuantityPriceSalesAmount
216/05/20173109766.0030
324/04/20173208427.0060
419/12/20169307853.00270
516/05/20173409766.00120
601/05/20182508731.00100
729/09/20171607492.0060
Data
Cell Formulas
RangeFormula
E2:E7E2=B2*C2


Thanks
mg
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Since you want to preserve the formulas in Column E, I think you'll need to fill your array row by row, instead of all in one shot. Then you can get the value for Column E using the FormulaR1C1 property, instead of the Value property...

VBA Code:
Option Explicit

Sub test()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    ReDim arr(1 To lastRow - 1, 1 To 5) As Variant

    Dim i As Long
    For i = 2 To lastRow
        arr(i - 1, 1) = Cells(i, "a").Value
        arr(i - 1, 2) = Cells(i, "b").Value
        arr(i - 1, 3) = Cells(i, "c").Value
        arr(i - 1, 4) = Cells(i, "d").Value * 2
        arr(i - 1, 5) = Cells(i, "e").FormulaR1C1
    Next i
       
    Range("G2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
   
End Sub
 
Upvote 0
What happens if you make this one small change to your original code?

Rich (BB code):
arr = Range("A2:e7").Value2
arr = Range("A2:e7").Formula
 
Upvote 0
Also, a different approach option to consider?

VBA Code:
Sub Alternative()
  With Range("A2:E7")
    .Copy Destination:=Range("G2")
    .Columns(4).Copy
  End With
  Range("J2").PasteSpecial Operation:=xlAdd
  Application.CutCopyMode = False
End Sub
 
Upvote 0
What happens if you make this one small change to your original code?

arr = Range("A2:e7").Value2
arr = Range("A2:e7").Formula

Hi Peter,

Yeah, while it copies the formula, the reference won't be relative to Column K. For example, the formula in K2 would be =B2*C2 instead of =H2*I2. I assume that OP wanted the latter. But I could be wrong. :)

Cheers!
 
Upvote 0
Hi Peter,

Yeah, while it copies the formula, the reference won't be relative to Column K. For example, the formula in K2 would be =B2*C2 instead of =H2*I2. I assume that OP wanted the latter.
Yes, I didn't look closely enough at that. :oops:

What about
Rich (BB code):
arr = Range("A2:e7").FormulaR1C1
 
Upvote 0
What about
Rich (BB code):
arr = Range("A2:e7").FormulaR1C1

Yep, that works.

That was the first, actually the second thing I tried, but I think what happened was that when the value in Column K remained the same and didn't change, I mistaking thought it didn't work, thinking that that the value should have double when in fact it shouldn't have. It seems that I got confused. Oh well. I'm glad you caught it. Thanks Peter.

Cheers!
 
Last edited:
Upvote 0
Hi Peter and Domenic

Millions of thanks both of you for your help , Perfect ! it worked ! ? (y)



Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top