I am trying to add a formula that will subtract Column C from B through code, but I don't know how to format the formula using a variable in my code to move to another cell. I more then likley going about this wrong, so any help would be appreciated. The following works with the exception of the formula.
Please Help.
Dim
xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim lb As IntegerxlApp = CreateObject(
"Excel.Application")xlBook = xlApp.Workbooks.Add()
xlSheet = xlBook.Worksheets(1)
For lb = 0 To ListBox1.Items.Count - 1 Step 1 Dim send As String Dim sendT As String Dim sendTE As Stringsend = ListBox1.Items.Item(lb)
sendT = ListBox2.Items.Item(lb)
sendTE = ListBox3.Items.Item(lb)
If lb >= 0 ThenxlSheet.Cells.Item((lb + 1), 1) = send
xlSheet.Cells.Item((lb + 1), 2) = sendT
xlSheet.Cells.Item((lb + 1), 3) = sendTE
xlSheet.Cells.Item((lb + 1), 4).Formula =
"= C(lb)-B(lb)" End If NextxlSheet.Columns(
"A:C").ColumnWidth = 25xlBook.SaveAs(
"c:\NewWorkBook.xls")xlBook.Close()
xlApp =
Nothing
Trying to write an Excel Formula using a VB.Net variable
Princess77
I tried this but it did not work. I guess I don't understand how I need to implement this. Any help would be great.
Thanks
gzitek
xlSheet.Cells((lb + 1), 1).Select
Selection.Value= send
bllshw
You want to use concatenation to put the actual integer value of lb into your formula, rather than the text "lb."
Change this line to use the ampersand concatenation operator:
xlSheet.Cells.Item((lb + 1), 4).Formula = "= C" & lb+1 & "-B" & lb+1
Richardk
Select the cell first:
xlSheet.Range("A1").Select
Set the value of the selection:
xlSheet.Selection.Value = send
Same with formula:
xlSheet.Selection.Formula="= C(lb)-B(lb)"
Move around the selection with offsets:
XlSheet.selection.offset(0,1).Select
HTH