Import the formula in the cell into the following rows

[ozgur.yukcan]

Hi All,

I want to copy the formula in Excel M2 cell up to the number of rows. How do I do it?

[naveen.s]

@ozgur.yukcan

Hi All,

I want to copy the formula in Excel M2 cell up to the number of rows. How do I do it?

@ozgur.yukcan ,

This may helps you:

To copy a formula in Excel from cell M2 down to the end of the data (to the number of rows you have), you can do this in a few ways. Here’s a step-by-step guide on how to do it manually as well as how to automate it in UiPath.

Manually Copying the Formula in Excel

1. Select Cell M2: Click on cell M2, which contains the formula you want to copy.
2. Grab the Fill Handle: Move your mouse to the bottom-right corner of the cell until you see a small square (this is called the Fill Handle).
3. Drag Down: Click and drag the Fill Handle down to the last row where you want to copy the formula.
   • Alternatively, you can double-click the Fill Handle to automatically fill the formula down to the last row of contiguous data in the adjacent column.

Using UiPath to Automate the Process

If you want to use UiPath to replicate this, you can follow these steps:

1. Use Excel Application Scope: Begin by using the Excel Application Scope activity to open your Excel file.

2. Insert Column (if needed): If you haven’t inserted the “Denk” column yet, you can use the Insert Column activity.

3. Write Formula to M2: Use the Write Cell activity to write your formula into cell M2.

4. Find Last Row: Use the Read Range activity to read the data from the column next to M (or any relevant data) to determine the last row of data. You can then use the output data table to find the row count.

   • For example:

     lastRow = YourDataTable.Rows.Count
     

5. Copy Formula Down: To copy the formula from M2 down to the last row, you can use the Write Range activity to write the formula down. Here’s how you can do it:

   • You can create a DataTable containing the formula like this:

     formulaList = New List(Of String)()
     For i = 1 To lastRow - 1
         formulaList.Add("=M2") ' or whatever your formula is
     Next
     

   • After that, convert the list to a DataTable if needed and use the Write Range activity to write it to the M column.

Example Pseudo-UiPath Code

' Assuming you're working within Excel Application Scope
' Let's say you want to find the last row from column A.
excelAppScope

    ' Read the data from column A
    readRange "A:A" output yourDataTable

    lastRow = yourDataTable.Rows.Count

    ' Write the formula to M2
    writeCell "Sheet1" "M2" "=your_formula"

    ' Create a list to hold formulas
    formulaList = New List(Of String)()
    For i = 2 To lastRow
        formulaList.Add("=M2") ' This is where your original formula goes
    Next

    ' Write the formula list to M3 downwards
    writeRange "Sheet1" "M3" formulaList

Important Note

The writing of ranges with formulas can vary depending on the requirements. You can customize the logic for the formulas you wish to apply.

This will help you replicate and automate the process of copying the formula in cell M2 down through the necessary rows.

[Gowtham_K115 (Gowtham Krishnan)]

Hi @ozgur.yukcan

Welcome to the community !!!

You only need to enter the formula in the first row, then use Excel’s Autofill range activity to automatically extend the formula down to the last row.

Regards,
Gowtham K