Hi
Thanks for your input.
I obviously didn't include the reams of code that define the values of x & y that would have just confused things.
I now know what I missed in that piece of Formula code, namely the use of R1C1 and " & x & ".
I prefer to use the column as a letter "D" instead of the column number 4. It makes understanding the code later on a tad easier.
...
tryinghard
Last Activity: Nov 27 '13, 04:07 PM
Joined: Nov 21 '13
Location:
-
#NAME? error
Hi I'm getting the worksheet error #NAME? when my formula in VBA gets evaluated.
I know that the error is refering to the word cells in the formula, but I don't know how to make Excel accept a formula that is not in the usual ColRow : ColRow format.Code:Cells(x + y + 4, "D").Formula = "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))"
-
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.
The problem is I don't know how else to refer to the cells as their...Code:Cells(x + y + 4, "D").Formula = "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))"
Leave a comment:
-
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.
...Leave a comment:
-
Problem with .Formula = in Excel VBA
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...
...Code:cells(x , y).Formula = "=sum(cells(a , b)- cells(s , t))"
-
Ahh, Thanks Killer42, I thought there must be a simple answer.
I've just stumbled across another little problem that the Excel VBA Help is less than useful with. It's to do with Formula = in VBA code.
I think I better enter it as a new Problem, hope you can help me with that also.Leave a comment:
-
Using Excel VBA - problems in passing a global variable
I have many subroutines that get called from various events on multiple excel worksheets,
All I want to do is identify which subs have been used.
I wish to set a variable in each sub and take action in another sub depending on its value:
Purch does not get passed. Helllllp
sub calc()
dim Purch as integer
if Purch = 1 then
'perform some action
elseif Purch = 2 then
' perform...
No activity results to display
Leave a comment: