Thread: Build String and then Insert it as Formula to Cell

I am having a difficult time finding a solution to this simple task. I need to insert a formula in a cell, it's a long formula which simply adds up the values from some other cells. The pattern of the cells to be added is offset by 8 rows each time so I thought why not write a VB to do this.

The formula will look like this:

=C7+C15+C23+C31.....+C415

Here is the code I'm trying to make work

Sub Main()

Dim x
Dim y As String

y = "="

For x = 7 To 415 Step 8
y = y & "C" & Str(x) & "+"
Next

Range("C1").Formula = y

End Sub

I got this to work although slightly differently in VB2010 Express to write a text file. The string 'y' gets concantenated each time through the loop to get the next cell added to it, then I want to insert the string that 'y' has become as a formula into cell "C1".

try

Sub Main()

Dim x
Dim y As String

For x = 7 To 415 Step 8
    y = y & "+" & "C" & Trim(Str(x))
Next

Mid(y, 1) = "="
Range("C1").Formula = y

End Sub

Thanks, I had actually resolved this with these corrections before reading your post. This mornings use of VB 2010 Express with it's slightly different syntax threw me off to the spaces being left and the end of the formula needed termination properly. Your code is a little cleaner, so I will use it instead of mine.

Sub Main()

Dim x
Dim y

y = "="

For x = 7 To 407 Step 8
y = y & "C" & Mid(Str(x), 2) & "+"
Next

y = y & "C" & Mid(Str(x), 2)


Range("C1").Formula = y


End Sub