Insert formula into a vairable range of cells in Excel from VB
-
Hi there, I have the following code which takes data from SQL and then adds it to a Excel Spreadsheet. This all works fine, but I need to subtract the column "K" from Column "L" and store it in "M" It inserts the formula into the required number of rows (dependant of the value "R2") but the formula shows as =(K & D1)-(L & D1) in each cell and not for example =(K 28)-(L 28) etc.... Can you help?? Thanks <code> Dim SQlQuery2 As String = "select * from growth where Name <> '[Files]'order by FullPath" Dim SQLCommand2 As New SqlCommand(SQlQuery2, myConn) Dim SQlReader2 As SqlDataReader = SQLCommand2.ExecuteReader Dim R2 As Integer = 27 While SQlReader2.Read R2 = R2 + 1 osheet.Range("I" & R2).Value = SQlReader2.GetValue(0).ToString osheet.Range("I" & R2).BorderAround(8) osheet.Range("J" & R2).Value = SQlReader2.GetValue(1).ToString osheet.Range("J" & R2).BorderAround(8) osheet.Range("K" & R2).Value = SQlReader2.GetValue(2).ToString osheet.Range("K" & R2).BorderAround(8) osheet.Range("L" & R2).Value = SQlReader2.GetValue(3).ToString osheet.Range("L" & R2).BorderAround(8) End While SQlReader2.Close() SQlReader2 = Nothing Dim D1 As Integer = 28 Do Until D1 = R2
-
Hi there, I have the following code which takes data from SQL and then adds it to a Excel Spreadsheet. This all works fine, but I need to subtract the column "K" from Column "L" and store it in "M" It inserts the formula into the required number of rows (dependant of the value "R2") but the formula shows as =(K & D1)-(L & D1) in each cell and not for example =(K 28)-(L 28) etc.... Can you help?? Thanks <code> Dim SQlQuery2 As String = "select * from growth where Name <> '[Files]'order by FullPath" Dim SQLCommand2 As New SqlCommand(SQlQuery2, myConn) Dim SQlReader2 As SqlDataReader = SQLCommand2.ExecuteReader Dim R2 As Integer = 27 While SQlReader2.Read R2 = R2 + 1 osheet.Range("I" & R2).Value = SQlReader2.GetValue(0).ToString osheet.Range("I" & R2).BorderAround(8) osheet.Range("J" & R2).Value = SQlReader2.GetValue(1).ToString osheet.Range("J" & R2).BorderAround(8) osheet.Range("K" & R2).Value = SQlReader2.GetValue(2).ToString osheet.Range("K" & R2).BorderAround(8) osheet.Range("L" & R2).Value = SQlReader2.GetValue(3).ToString osheet.Range("L" & R2).BorderAround(8) End While SQlReader2.Close() SQlReader2 = Nothing Dim D1 As Integer = 28 Do Until D1 = R2
The below works, you just have to build the string, yours is a common issue. Everything in the formula should be in parenthesis, then go back and replace all numbers with " & D1 & "
osheet.Range("M" & D1).Formula = "=(K" & D1 & ")-(L" & D1 & ")"
-
The below works, you just have to build the string, yours is a common issue. Everything in the formula should be in parenthesis, then go back and replace all numbers with " & D1 & "
osheet.Range("M" & D1).Formula = "=(K" & D1 & ")-(L" & D1 & ")"