Excel Basics #7: Range Functions

I want to use vb to create a formula, but first background on the file:

1. Data is stored in AK28 to AK_xx (differs by each tab)
2. In the column next to it (AL) we have the data's matching z-score
3. In AL:22 There is a spot to type in "acceptable z-scores", which is defaulted to 3, but can be changed
4. In AL:20 I want to have an average of all the data in AK28 to AK_xx that is within +/- acceptable z-scores
5. The calculation though in AL20 must look simple like (=AVERAGE(AK$28:AK$145)) so that anyone who opens it can double click it and see what the range selected is - this I think rules out arrays

So, first time with user form user (redundant much?). I got some help yesterday over in this thread: User Form Basics - Populating Text Boxes. A few tweaks later and its reading the data just fine. Some of the specific form objects and range names have changed, but it's the same logic.

Anyway, this isn't just for display. I need to export the data back to the spreadsheet. So, I plugged this line into the event handler for the "Save & Exit" button on the sample form in the other thread:

Code:

Range("intTier1Size").Value = frmRaceLimits.txtTier1Size

Excel responds with...

Quote:

Run-time error '1004':
Method 'Range' of object '_Global' failed

If I change the code to read:

Code:

Range("H5").Value = frmRaceLimits.txtTier1Size

It dumps the value in the cell H5 on the current worksheet.

As before, the named ranges are not cell references. Some are static values, some are formulas. They don't exist anywhere except the Name Manager. To make it more confusing, I've used Range with similar named ranges in quite a few other procedures, and it works just fine. Only here, where it involves a user form, do I have a problem.

Any thoughts?

I have a spreadsheet that shows a date, because of shipping I need to enter a
date and then calculate three months back and enter that date, For example
Cell A1 March, 2004 and I have to enter into cell B1 January 2004, how do I
get Excel to do this automatically.

Please can anyone help me answer any of these questions:

How would you add the contents of the cells from A1 to A10. Give 2 ways
to do this. (hint: Look up "Examples of common formulas" in Excel
help



How do you format a cell to have dollar signs next to the numbers?



How do you add the contents of cells A1 through A10 on sheet 1 and make
the answer show up on sheet 2?



How do you rename a sheet?



Can I delete the sheets that I don't need? If so how?



How do I freeze rows or columns of the sheet so that they don't'
move when you scroll up or down? Example: My name is in Column A, when
I scroll right my mane stays on the screen and column B moves behind A



How do you add up all the contents of column D and make the answer show
up in column C?


Thanks in advance!
abi

I went to the website cpearson.com and got the information for the syntax and the formula to sum numbers in different colors on a worksheet
now my problem is where do i put the visual basic part of the works so the formula can call on it
can you tell i have no idea but great hopes!!!
thanks everyone

Alright i'm using Excel 2007, i created a table with 2 columns and 5 rows
cell A1 - A5 has a list of numbers, and Cells B1 - B5 has a list of food items,

I've created a blank User-Interface form, using VisualBasics which came inside excel 2007, how do i get the table in my Excel Sheet, to show as it is with it's color coding and grid lines in the VisualBasics Form,


My second question is, I also created a Seperate Visual Basics form in Visual Basics 2008, could i get the table in the Excel form, to show in the form in Visual Basics 2008 programme, I have no clue how to link Visual basics, to Excel so a non-overly technical step-by-step guide would be extremely appreciated,


Thank you,

I'm trying to populate a Word 2003 document with data from an Excel 2003 document -- I realize some use mail merge but this, for me, has been an excruciating pain in the neck, so I am trying to avoid it by going the Word bookmark route, which looks like it will fit the bill... if only I could figure it out!

I have done some searches on here and this is the code that seems to have set the precedent:

http://www.mrexcel.com/forum/showthread.php?t=478182

However, as a beginner, it's a bit overwhelming for me. While I'm learning a lot by looking at it and looking up the things I don't understand, it's taking a long time and I actually need to use something similar for my own project.

Specifically, I wanted to open a template letter; populate it from the active Excel row I'm working on; and then "Save As" so I can keep the template.

Is there some more generalized information out there that outlines this procedure? Otherwise, would someone be kind enough to separate the code and/or elaborate more on what the invididual lines/sections do?

Hope you can help!

Basicly the same as the title.. I want to learn more about excel... I consider myself to be just a bit better then a noob, i can use the sum function, and i think i understand how most easy things work

Brain teaser of note! well for me at any rate, pity the poor sap with an arts background trying to put this together. My grasp of excel formula is remarkably poor to say the least.
I am trying to do the following: I need to check a cell [say A2] for the existence of a range of data and then depending on the range that exists, populate another cell [say B2] with another value.

ie:
if A2 has a value ending in the range 00-09 then B2=9
if A2 has a value ending in the range 10-19 then B2=8
if A2 has a value ending in the range 20-29 then B2=7
if A2 has a value ending in the range 30-39 then B2=6

And so on.

Any help with this on will be hugely appreciated!

We have an excellent in house developed add-in(xlam) that automate many processes.
Unfortunately we have some excel templates with the same UDF functions locally, i.e VB:

Public Function rng(ByVal dx As Double, ByVal dy As Double) As Double 
    rng = Sqr(dx ^ 2 + dy ^ 2) 
End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The above function exists in both add-in and local workbook.
In Excel 2003 this was not a problem.
But in Excel 2007 the formula is referenced to the add-in (xlam) and not the local workbook VBA i.e
VB:

= 'C:\Documents and Settings\xx\Application Data\Microsoft\AddIns\isXtools.xlam'!rng(.....

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



.Is there a method for setting the priority of which the formula is referenced?

I have tried disabling the add-in in the workbook_open() procedure and enable on the workbook_Beforeclose() without luck.