Input excel formula fast

Help Studio
1

Hi friends

I have to insert 3 formulas and few validation into excel.

For validations i m using the below invoke code.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet

Dim rng1 As Microsoft.Office.Interop.Excel.Range
Dim rng2 As Microsoft.Office.Interop.Excel.Range
Dim rng3 As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False

ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
rng1=CType(ws.Range(“AG2:AG2000”),Microsoft.Office.Interop.Excel.Range)
rng2=CType(ws.Range(“K2:K2000”),Microsoft.Office.Interop.Excel.Range)
rng3=CType(ws.Range(“L2:L2000”),Microsoft.Office.Interop.Excel.Range)

With rng1
.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
“A,B”)
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True

for formula , i am using write cell in for each row , its taking a lot time.
do you have any idea to write it fast
image

2

Hello,

What do you mean exactly by “its taking a lot time” ?

If you have many rows in your Excel File, and writing row by row is what your mean “takes a lot of time”, you could try to write the formula in the first row, and then apply the formula to the full column thanks to Excel shortcut (Ctrl+enter to apply formula of the first row to the selected range).

1 Like