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

- 140.6K All Categories
- 103.5K Programming Languages
- 6.4K Assembler Developer
- 1.9K Basic
- 39.9K C and C++
- 2.9K C#
- 7.9K Delphi and Kylix
- 4 Haskell
- 9.6K Java
- 4.1K Pascal
- 1.3K Perl
- 2K PHP
- 531 Python
- 37 Ruby
- 4.4K VB.NET
- 1.6K VBA
- 20.8K Visual Basic
- 2.6K Game programming
- 314 Console programming
- 90 DirectX Game dev
- 1 Minecraft
- 110 Newbie Game Programmers
- 2 Oculus Rift
- 9K Applications
- 1.8K Computer Graphics
- 733 Computer Hardware
- 3.4K Database & SQL
- 535 Electronics development
- 1.6K Matlab
- 628 Sound & Music
- 257 XML Development
- 3.3K Classifieds
- 198 Co-operative Projects
- 193 For sale
- 190 FreeLance Software City
- 1.9K Jobs Available
- 602 Jobs Wanted
- 203 Wanted
- 2.9K Microsoft .NET
- 1.7K ASP.NET
- 1.1K .NET General
- 3.4K Miscellaneous
- 7 Join the Team
- 0 User Profiles
- 354 Comments on this site
- 69 Computer Emulators
- 2.1K General programming
- 187 New programming languages
- 619 Off topic board
- 182 Mobile & Wireless
- 56 Android
- 124 Palm Pilot
- 335 Multimedia
- 151 Demo programming
- 184 MP3 programming
- 0 Bash scripts
- 22 Cloud Computing
- 53 FreeBSD
- 1.7K LINUX programming
- 370 MS-DOS
- 0 Shell scripting
- 320 Windows CE & Pocket PC
- 4.1K Windows programming
- 923 Software Development
- 411 Algorithms
- 68 Object Orientation
- 89 Project Management
- 93 Quality & Testing
- 261 Security
- 7.6K WEB-Development
- 1.8K Active Server Pages
- 61 AJAX
- 2 Bootstrap Themes
- 55 CGI Development
- 28 ColdFusion
- 224 Flash development
- 1.4K HTML & WEB-Design
- 1.4K Internet Development
- 2.2K JavaScript
- 35 JQuery
- 296 WEB Servers
- 135 WEB-Services / SOAP

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

Terms of use / Privacy statement / Publisher: Lars Hagelin

Programmers Heaven articles / Programmers Heaven files / Programmers Heaven uploaded content / Programmers Heaven C Sharp ebook / Operated by CommunityHeaven

© 1997-2015 Programmersheaven.com - All rights reserved.

## Comments

8[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

55: [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

:

:

55: : [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

:

:

:

:

55: : : [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 & ")"

: :

:

:

:

:

8I 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

55:

: 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

8Let'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