IN EXCEL APPLICATION SCOPE Adding all values of one EXCEL ROW into all values of another EXCEL ROW(same file same sheet) and store the result IN EXCEL

Hi TEam,

Suppose I have the random excel as shown below And have the fixed cell rows and columns

Now, I simply want to add the values of A6 to F6 to the values of A2 to F2 and also to A3 to F3 and also to A4 to F4.

Please provide me with some efficient way how I can perform this task.

NOTE 1:- Everything needs to be done in the excel file itself so that datatable is not needed. **
NO
TE 2:- I need to perform all this in the background so excel file opening is not needed. **
****NOTE 3:- Direct formula is not needed as this file is created on the fly.

Thanks and Regards,
@hacky

1 Like

@HareeshMR,
@Palaniyappan,
@arivu96,
@aksh1yadav,
@Lahiru.Fernando
@indra
Please help.

1 Like

Better to go with datatable @hacky

Do these steps :

  1. read entire data and store in data table
  2. Then pick the values of A6 by looping through the data table and add it to A2. store it in a variable
  3. Then simply use write cell activity or just assigning it back to the data table will change the value :slight_smile:
1 Like

@hacky

You can use VB.NET code to perform this task

1 Like

@HareeshMR,

But then as I said this excel is created on the fly and I was trying to avoid using datatable.

I am having a gut feeling that there has to be some way to achieve this.

I know its very easy when it somes to datatables.

1 Like

Yeah, the best option to do it in VB as @lukas_krishnan mentioned :slight_smile:

2 Likes

@lukas_krishnan,

I understand, can you give some approach that I can try on this dummy data?
where following is satisfied:-
NOTE 1:- Everything needs to be done in the excel file itself so that datatable is not needed. **
NOTE 2:- I need to perform all this in the background so excel file opening is not needed. **
****NOTE 3:- Direct formula is not needed as this file is created on the fly. preformatted text

1 Like

@hacky

Try this for adding only value in vba :
Range(“A2”).Value = Range(“A2”).Value + Range(“A6”).Value

1 Like

@hacky

For your reference code for adding 2 cells in VB.NET

Try
Dim xlsApp As Microsoft.Office.Interop.Excel.Application = Nothing
Dim xlsWorkBooks As Microsoft.Office.Interop.Excel.Workbooks = Nothing
Dim xlsWB1 As Microsoft.Office.Interop.Excel.Workbook = Nothing
xlsApp = New Microsoft.Office.Interop.Excel.Application

xlsApp.Visible = False
xlsApp.DisplayAlerts = False

xlsWorkBooks = xlsApp.Workbooks
xlsWB1 = xlsWorkbooks.Open(source_file_path)

Dim sheet1 As Microsoft.Office.Interop.Excel.Worksheet
sheet1=CType(xlsWB1.Sheets(source_sheet_name),Microsoft.Office.Interop.Excel.Worksheet)

sheet1.Range(“A1”).Value = Convert.ToInt32(sheet1.Range(“A1”).Value) + convert.ToInt32(sheet1.Range(“B1”).Value)

xlsWB1.Save
xlsWB1.Close
xlsApp.Quit
Catch exception
End Try

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.