Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

variables in access formulas

rascalrascal Posts: 55Member
I am working on a program which sums a collumn; however, the column is created by a loop and therefore the last cell is a variable. How do I enter a variable in the formula. I tried several variations of this:

a = Range("A1").Formula = "=sum(Range(("b" & 3), ("b" & currow)))

However the formula is not recoginized as a formula and will not sum the range.

Thanks

Comments

  • wojawoja Posts: 8Member
    What you want is:
    [code]
    Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
    [/code]
    [b]Tip[/b]: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.

    Hope this helps

    [hr]
    [size=2][red][b]Roger J Coult[/b][/red][/size]
    [b]Grimsby, UK[b]
    :-o

  • rascalrascal Posts: 55Member
    : What you want is:
    : [code]
    : Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
    : [/code]
    : [b]Tip[/b]: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
    :
    : Hope this helps
    :
    : [hr]
    : [size=2][red][b]Roger J Coult[/b][/red][/size]
    : [b]Grimsby, UK[b]
    : :-o
    :
    :



  • rascalrascal Posts: 55Member
    : : What you want is:
    : : [code]
    : : Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
    : : [/code]
    : : [b]Tip[/b]: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
    : :
    : : Hope this helps
    : :
    : : [hr]
    : : [size=2][red][b]Roger J Coult[/b][/red][/size]
    : : [b]Grimsby, UK[b]
    : : :-o
    : :
    : : That's great I already tried it out
    :
    :
    :
    :



  • rascalrascal Posts: 55Member
    : : : What you want is:
    : : : [code]
    : : : Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
    : : : [/code]
    : : : [b]Tip[/b]: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
    : : :
    : : : Hope this helps
    : : :
    : : : [hr]
    : : : [size=2][red][b]Roger J Coult[/b][/red][/size]
    : : : [b]Grimsby, UK[b]
    : : : :-o
    : : :
    : : : That's great I already tried it out
    : :
    : :
    : : I did try this out; however, I could not get the program to recognize the variable "currow" I either receive an error message or, if I put the quotation marks around the b3:b as you suggested, I only got the value of the cell with in the quotation marks
    Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
    : :
    :
    :
    :
    :



  • wojawoja Posts: 8Member
    What I understood you were trying to do was to produce in [green]cell A1[/green] the sum of the cells from [green]row 3[/green] in [green]column B[/green] to [green]some calcuated row[/green] in [green]column B[/green]. That is, the variable [red]currrow[/red] is calculated in your VBA script.

    I suspect that this is [b]not[/b] what you are trying to do. Can you explain what you want to achieve in a little more detail?

    [hr]
    [size=2][red][b]Roger J Coult[/b][/red][/size]
    [b]Grimsby, UK[/b]
    :-o

  • rascalrascal Posts: 55Member
    : What I understood you were trying to do was to produce in [green]cell A1[/green] the sum of the cells from [green]row 3[/green] in [green]column B[/green] to [green]some calcuated row[/green] in [green]column B[/green]. That is, the variable [red]currrow[/red] is calculated in your VBA script.
    :
    : I suspect that this is [b]not[/b] what you are trying to do. Can you explain what you want to achieve in a little more detail?
    :
    : [hr]
    : [size=2][red][b]Roger J Coult[/b][/red][/size]
    : [b]Grimsby, UK[/b]
    : :-o
    :
    :
    THIS PROGRAM IS KIND OF AN AMATURE WAY OF FORECASTING SALES. AS EACH DAY PROGRESSES THE GROSS SALES ARE SUBMITTED AND ENTERED INTO A CELL (VIA A TEXT BOX) THAT BOX WILL BE MULTIPLIED BY THE FRACTION OF THE TOTAL NUMBER OF DAYS IN THE PERIOD. FOR INSTANCE ON THE FIRST DAY THE PERIOD IS 1/20 (GIVEN 20 SALES DAYS) THE NEXT DAY IS 2/20, AND THE NEXT 3/20 AND SO FORTH. THEREFORE; ON THE DAY BEFORE THE FINAL DAY YOU WILL HAVE 19/20. WHAT I NEED TO DO IS TO BE ABLE TO TAKE THE AVERAGE OF THE COLUMN OF SALES LETS SAY THE B COLUMN (WHICH IS INCREASED BY ONE EACH DAY) MULTIPLY THAT BY THE FRACTION TO SOMEHOW BE ABLE TO PREDICT GIVEN LINEAR SALES THE END OF THE MONTH TOTALS.

    SO WHAT I NEED IS THE ABILITY TO TAKE A AVERAGE OF A COLUMN GIVEN THAT THE COLUMN ROW IS A VARIABLE.

    I KNOW THAT THIS IS CONVOLUTED REASONING AND IT MAY BE FAULTY BUT ONCE THE PROGRAMMING IS WORKED OUT WE CAN WORK ON THE PREDICTION FORMULA.

    I KNOW THAT THE BOSS WILL USE THIS PROGRAM TO SHOW ME THAT MY SALES ARE POOR AND THEREFORE I DON'T DESERVE A RAISE.

    THANKS FOR YOUR HELP



  • wojawoja Posts: 8Member
    Ahhh...

    Let's say you have your sales figures in column B.

    There is going to be some maximum number of sales days (shall we say 1000, just for the sake of example).

    What you are saying is that you want the average of the cells B1:B1000 only counting the cells with some data in.

    This is exactly what the AVERAGE() worksheet function does!

    If you enter =AVERAGE(B1:B1000) in A1 (and there is no data in B1:B1000), Excel will display #DIV/0! becuase there is no data. Start entering values in B1:B1000 and you'll see the average displayed.

    Hope this helps.


    [hr]
    [size=2][red][b]Roger J Coult[/b][/red][/size]
    [b]Grimsby, UK[/b]
    :-o

Sign In or Register to comment.