How to Extend the formual of multiple column

[manjula_rajendran]

Hi,

I’ve an excel sheet which is having data till column ET. From the Column AY2 to ET2 has formula for each column. I need to expand this formulas to all the column and rows respectively.

Currently i’ve done like, taking the array variable
FormualRanges= {“AY”, “AZ”, “BA”, “BB”, “BC”, “BD”, “BE”, “BF”, “BG”, “BH”, “BI”, “BJ”, “BK”, “BL”, “BM”, “BN”, “BO”, “BP”, “BQ”, “BR”, “BS”, “BT”, “BU”, “BV”, “BW”, “BX”, “BY”}

Then inside the for each loop i’ve auto filled the row 2 (ex: AY2 ) till AY+(dtData.Rows.Count+1)

Now i’ve to extend the formula till column ET. I can follow the same method as above by including the column inside array but I want to know is there any other way i can do it without adding it in array or any other select activity.

Thanks in advance.

[ppr (Peter Preuss)]

find some techniques maybe helping you for setting up a new strategy:

 Enumerable.Range(3,15).ToArray
 int[15] { 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 }
 Enumerable.Range(3,15).Select(Function (x) x.toString).ToArray
 string[15] { "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17" }
 UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnLetterToColumnIndex("AY")
 51
 UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnLetterToColumnIndex("ET")
 150
 UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(150)
 "ET"
 Enumerable.Range(51, 150-51).Select(Function (x) UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(x)).toarray
 string[99] { "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES" }

[manjula_rajendran]

Thank you @ppr. It was really a good approach instead of hard coding such a long values.

My another query was, to copy and apply the formula on all the column by doing in side the for each loop or is there any activity so that i can select the formulas in AY2:ET2 and apply on all the rows by taking the dynamic count of last rows.