Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable in Sum
Thanks again. What you have said has helped a lot. I did not realise that
this was just a string. I thought quite wrongly the quotes applied to the expression as a whole and I tried to ampersand variables into the centre! It does have the -ve out front because this is the balancing entry of a guided General Journal entry (ie) the overall total (what is there plus this) is 0, and now it is working. Yeah! "Jacob Skaria" wrote: Thanks Sue. I have tried to explain a bit about strings and ampersands. Hope it helps.. If not post back A. Quotes represents string values.. If 'i' is a variable the formula will be "=-Sum(R[" & i & "]C:R[-1]C)" When you have two variables used here say i and y "=-Sum(R[" & i & "]C:R[" & y & "]C)" If you break it down it is easy to understand. All strings are combined using ampersands. "=-Sum(R[" .......is a string and so surrounded by quotes i ............is a variable "]C:R[" ......is again a string and so surrounded by quotes y .................another variable "]C)" ..........yet another string B. Ampersands are used to combine variables OR strings... C. Use .Offset to refer relative values like ActiveCell.Offset Activecell.Offset(2,3) will refer to 2 rows down and 3 columns to the right D. ActiveCell.Offset(0,3).FormulaR1C1 = "=Sum(R[" & -3 & "]C:R[-1]C)" will put in a formula which adds up 3 rows to the top. You dont need a negative sign placed before the formula..If i is negative and suppose value is -3 the below formula will add up 3 rows above... i = -3 "=Sum(R[" & i & "]C:R[-1]C)" If this post helps click Yes --------------- Jacob Skaria "Sue" wrote: Congratulations on your Silver. I see you just got it today! You have helped me recently with another quote and ampersand problem - I so appreciate it. I realise I do not fully grasp when to use quotes, ampersands and brackets. I did try the quotes but I did not have the second ampersand. By the way my i was a negative i to clarify working on the formula - one less sign (i=-i)! I have tested it in my program and gone back to i being +ve with -i in the formula and it does work exactly as it is meant. The cursor remains on the left and the group of figures are in column d immediately above the cursor row. It is relative, the actual row is not row 1. "Jacob Skaria" wrote: It should be "=-Sum(R[" & i & "]C:R[-1]C)" Are you sure it is ActiveCell.Range("d1"). OR ActiveSheet.Range("D1"). But are you sure it is If this post helps click Yes --------------- Jacob Skaria "Sue" wrote: I have worked out with a count loop how many lines need to be added. This is called i and can be from 1 to 10 usually. This is the formula I have tried to use. Without the variable it works. I conclude that brackets, quotes and ampersands are my problem and not for the first time either! Can someone help me to get the variable accepted in the formula? Help! eg i=3 ActiveCell.Range("d1").FormulaR1C1 = "=-Sum(R[& i]C:R[-1]C)" This should sum the 3 cells above d1 and put the answer in d1. It needs debugging. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a Variable to an Expression that Includes a Variable andVBA Property | Excel Programming | |||
Nothing Keyword Destories Objects rather than just resetting the variable to an empty variable | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |