Input data in particular cells


#1

Hello, I am trying to add data from one data table to another datatable while keeping the formulas in some cell intact. I have attached a sample excel file for clarity.
In the sample excel file i want to add values into the “landing file” sheet from the “Input file” sheet.
The “final file” sheet is what I desireretain formula.xlsx (11.3 KB)


#2

@ClaytonM @arivu96 @vvaidya @balupad14 @MAHESH1 @andrzej.kniola @aksh1yadav @ddpadil @richarddenton @Florent_Salendres


#3

@jamnanin

I haven’t tried this before, just iam suggesting you to try this.
If you are trying to use Read range and store it in a datatable it will not take it as a formula.

So Better you have to Assign formula directly into Excel sheet.
Use the Invoke Code
And Read the Excel sheet and Assign the Property of the cell to a formula like below

Range(“C1”).Formula = "=A1+B1"

Here you are setting the property of cell C3 with a formula of adding A1+B1 cells.

Regards,
Mahesh


#4

@MAHESH1 Are you suggesting to do it for all the cells. whereever there is a formula involved? Because I have ~ 1000 such cells.


#5

@jamnanin
Do you want to Include the same formaula for all the Cells.
First set the Formula to a cell then copy the formula to a specific range like below.
Range(“C1:C10”).Pastespecial(XlPasteall)
Now it will copy the formula which you have set in C1 to the Range From C1 to C10

Regards
Mahesh


#6

@MAHESH1 As provided in the excel. I have different formulas in different cells


#7

@jamnanin
Different Formulas in the sense can you give some example

Regards,
Mahesh


#8

like the profit has (sales - cost) and percentage change has (current year - prev year)/prev year


#9

@jamnanin

Any how you are doing based on corresponding cells only right

Regards,
Mahesh


#10

yes… based on the corresponding current year and prev year


#11

@jamnanin
Can you please post the excel with your requirement , for better understanding.

Regards,
Mahesh


#12

I have posted the excel file. in the question @MAHESH1