Problem with .Formula = in Excel VBA

[tryinghard]

Excel VBA subroutine:
The Cell coordinates are determined by a couple of loops which give an x & y location for the cell in the form
Cells(x , y).
I want to calculate and enter into this cell the result of a Formula.
Basically Cell(x , y) = Cell(a , b) minus Cell(s , t)
Here's the code that's giving me the problem...

 
cells(x , y).Formula = "=sum(cells(a , b)- cells(s , t))"
 
'An alternative that I tried was directly accessing the worksheet, but it still didn't work
 
Cells(x , y).Formula = "=sum(Main!H20-Main!H22)
 
 

[Killer42]

You haven't actually told us what problem you're having with this code.

I'LL take a stab at it, though. Where you've said cells(a , b) it's likely you intended to use the values of a and b in there.

I'm thinking something like...

Cells(x , y).Formula = "=sum(cells(" & a & " , " & b & ")- cells(" & s & " , " & t & "))"
 

Um... something else here doesn't really make sense. The Sum function is used to add up a number of values. Not sure why you're using it, since you're only working with a single (calcaulted) value. As an alternative, you might try this...

Cells(x , y).Formula = "=cells(" & a & " , " & b & ") - cells(" & s & " , " & t & ")"
 

[Killer42]

Oh, and a general tip. You'll probably find more VBA expertise in the Microsoft Access / VBA forum.

[tryinghard]

Hi Killer42,
I tried to simplify the problem, but I guess I should have described the problem I having in more detail.

I am using the SUM as I am indeed trying to compute the arithmetic sum of a column of values (normally on a sheet I'd use =sum(a1:a8) for instance.

Also I'm trying to subtract just 2 values from locations on the sheet. I agree the =cells would have been more correct in that instance.

The Error:
I get "Expected End of Statement" in the VBA code and the first comma (after the =sum) is highlighted in both the .Formulas below.

 
 Cells(x + y, "D").Formula = "=sum(cells("&x+1&" , "D") - cells("&x+y+2&" , "D"))"
 
Also the same error msg in the following column sum
 
 Cells(x + z, "D").Formula = "=sum(cells("&x+1&" , "D") : cells("&x+y+2&" , "D"))"
 
The idea is to subtract the last entry from the first as well as summing all the entries in the column - hence the two .Formulas
 
 

[tryinghard]

Hi - I've fixed the VBA compilation error that I was getting in the above post ....... however the formula still doesn't work as it gives a worksheet error of #NAME? which means it doesn't like the word cells in the formula.

 
 Cells(x + y + 4, "D").Formula = "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))"
 
 

The problem is I don't know how else to refer to the cells as their location on the worksheet changes.

[Killer42]

I don't think Cells(x + y + 4, "D") is correct to address a cell in VB code. For the two coordinates you're providing a numeric expression and a string.

It seems as though you are still having a problem incorporating variables in your expressions. If I remove the quotes around the "D" in this latest line of code, leaving this...

Cells(x + y + 4, D).Formula = "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))"

...then this means you are placing this exact formula in a cell: "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))". Would that work if you typed it directly into the cell? I think it's more likely that you intended to take the values of x and y, and insert them into the formula. For example, if we assume the values of x and y were 8 and 12 respectively, would you be expecting to see this formula placed in the cell?

=sum(cells(9 , 4) : cells(21 , 4))

If so, then the code would be more like this:

Cells(x + y + 4, D).Formula = "=sum(cells(" & x+1 & ", 4) : cells(" & x + y + 1 & " , 4))"

Sorry if I'm misunderstanding the issue here.

[MikeTheBike]

Hi

I am unclear, are wanting to enter a formula to calculate the result or calculate the result and enter the value? So below I have posted code to do both. Also I have illustrated relative and absolute addresss formular. Both formula example Use R1C1 notation, with an alternative using the cell addresses.

Option Explicit
    Dim x As Integer
    Dim y As Integer
    Dim a As Integer
    Dim b As Integer
    Dim s As Integer
    Dim t As Integer
 
Sub InsertValue()
    x = 2
    y = 2
    a = 3
    b = 3
    s = 4
    t = 4
 
    Cells(x, y) = Cells(a, b) - Cells(s, t)
 
End Sub
 
Sub InsertFormula_Rel()
    x = 2
    y = 2
    a = 3
    b = 3
    s = 4
    t = 4
 
    Cells(x, y).FormulaR1C1 = "=R[" & a - x & "]C[" & b - y & "]-R[" & s - x & "]C[" & t - y & "]"
    'Cells(x, y).Formula = "=" & Replace(Cells(a, b).Address, "$", "") & "-" & Replace(Cells(s, t).Address, "$", "")
End Sub
 
Sub InsertFormula_Abs()
    x = 2
    y = 2
    a = 3
    b = 3
    s = 4
    t = 4
 
    Cells(x, y).FormulaR1C1 = "=R" & a & "C" & b & "-R" & s & "C" & t
    'Cells(x, y).Formula = "=" & Cells(a, b).Address & "-" & Cells(s, t).Address
End Sub

I don't know if this any use to you but maybe the info my be usefull at some point in the future!

MTB