Reply:

VB Code for Naming Ranges
Can somehere tell me how to name a range in excel using VB code. Here' how I get the range: Range("L1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False As u can see the range changes, but I would like to create the sam name for whatever the range is? Thanks. -- Message posted from http://www.ExcelForum.com the code you can use is ActiveWorkbook.Names.Add Name:="YO...

Locking on Update Query
I have a database that keeps track of credit applications. I am trying to setup a 2 forms to pull up existing apps and edit them. The application data table is linked to an SQL Server 2000 table. I have 2 forms, one tied to a macro which allows me to find the app I want to edit using several criteria and sends the details of the app I select to a temp table, then opens the edit form, which is showing only the app I want to edit. This works fine. However, when I make the edits on the edit form and press the save button, the query runs, tells me I am updating 1 row, then gives me the l...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

In excel change color of a range of cells based on a cell value
Based on a cell value from a drop down box, how can you make a range of cells to change background color? Use the cell the box is linked to in your conditional formatting formula. If your box is tied to, say, A1, make sure the conditional formula is like: =$A$1="value" value is whatever will be selected in the box. Then set the background color. Squeaky "kwitt" wrote: > Based on a cell value from a drop down box, how can you make a range of cells > to change background color? ...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Stock Position Updates
I have 3 accounts with Smith Barney. When I update these accounts in Money 2003 I get a box that is titled Position Matching. This box shows my positions that need to be updated and asks me if I want to do it automatically, or manually. If I check automatically and click finish the box disappears and I get the account update screen and when I click next the position matching box comes up again. This circle goes around and around and won't update my positions. I have to do it manually or hit postpone to move on. The problem seems to be in dealing with selling calls or short sales. Th...

nth cell in the range
Within a VBA Module, I would like to set the ith component of my array A(1 to N) to be equal to the 100*(nth cell in a column in excel). How can I do this within a loop? ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Hi try something like the following sub foo() dim c_values(1 to 100) dim i for i = 1 to 100 c_values(i)=activesheet.cells(i*100,"A").value next end sub >-----Original Message----- >Within a VBA Module, I would like to set the ith component of my array >...

Date-range specific report with counted results of combo box selections
Hi there, I'm a novice with Access, but am currently setting up a patient database for an NGO in India that I'm volunteering with. The primary reason for the database is to collect statistics quarterly. It is a simple database with one table that collects patient data from simple forms with some fields having combo boxes. Ultimately I want to generate reports that will show a summary of 'counts' of various fields for a given period. For example: In the table I have a combo box for where the person came from, with several options including - "Pick up Y. Bazar", &quo...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

how do i update a chart area automatically
Hello, I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb,...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

Automatic filling of cells within a certain date range
Can someone please help, I have been stuck on this for the past two days... Column 'AB' has 'Start Date' and column 'AD' has 'Finish date' Row 19, cells 'AI' to 'DW' have the months written as 'J' 'F' 'M' 'A' etc.....from 2008 to 2015. I want to be able to enter a 'start date' in column 'AB' i.e. 1 January 2008 and a 'Finish date' in column 'AD' i.e. 1 April 2015. Then i would like the cells 'J - A' on row 19 to be filled with '1' in each month.... I...

FWD: Look at the security update
--mkfmqbped Content-Type: multipart/related; boundary="pztlqqku"; type="multipart/alternative" --pztlqqku Content-Type: multipart/alternative; boundary="uxlylohaloelt" --uxlylohaloelt Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "November 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vulnerabil...

using intersection of ranges
Need to specify the intersection of two named ranges, but require either one or both of them to be referenced to other cells. In other words, someone would enter the range which is one or both of the two, and I need the formula to look at that cell and use its name in the intersection argument. Any ideas? I have tried TEXT, but that doesn't seem to do it. Suppose the rangenames are rangeA and rangeB, and the names are in D2 and D3 =INDIRECT(D2) INDIRECT(D3) Note the spaces between the two functions; space is the intersection operator. Now you'll have to test for one or two...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Outlook 2007 Distribution list update!
For years now, I have used Ctrl+W to update a Distribution List after new member(s) have been added to the list. This keyboard shortcut seems to be inoperative in Outlook 2007. A setting missing or a function missing? ...

Date ranges
Hi I have a list of predefined dates in column B in colum C formula to show todays date. (i.e. if today = 24-6-06) What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06) A B C D 1 1 12-6-06 =today() next date = 2 2 19-7-06 previous date = 3 3 24-8-06 4 4 30-9-06 Any help appreciated. Regards Paul T Paul T wrote: > I have a list of predefined dates in column B in colum C formula to show > todays date. (i.e. if today = 24-6-06) > What I want is D1 to to show B2 (19-7-06) an...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Change Text Case to Upper in a cell range A2:A92
I have a workbook of Contacts. In column A there are Names. I wish to convert those to UPPER case. I've seen a few VB codes that change the case to UPPER if I change something in the cell, GREAT if I want to go through the entire Column and change something. But is there another way? "Barry" wrote: > I have a workbook of Contacts. In column A there are Names. I wish to > convert those to UPPER case. I've seen a few VB codes that change the case > to UPPER if I change something in the cell, GREAT if I want to go through > the entire Column and change s...

Outlook meeting tracking tab not updating acceptances
We have and Executive and Admin using Outlook XP SR-2 with Exchange 5.5 SP4 The Admin has delegate permissions to the Executives mailbox. All meeting requests get sent to the delegates not to the Executive. The Admin creates a meeting request for the Executive and sends it to several people. All the people accept the meeting and she receives all the acceptances in her inbox. However the Tracking tab in the Executives calendar for the particular meeting does not get updated that the people accepted the meeting. Any one have any ideas what the problem is? Thanks in advance, Jack Hackney, Jr ...

Windows 7 update improves graphics performance
There is an optional update: http://support.microsoft.com/kb/2028560 that claims to improve graphics performance. Here is the download link. http://www.microsoft.com/downloads/details.aspx?familyid=E04DD3BD-C34B-42EF-978B-10B73E7D5BBA&displaylang=en I have had no chance to benchmark it so far. Carlos "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:BBB09DA5-F311-4332-AD99-A7803E85FDDD@microsoft.com... > There is an optional update: > http://support.microsoft.com/kb/2028560 > that claims to improve graphics performance. > Here is th...

Why Office 2007 updates for office 2003?
We are running Office 2003 on one computer. The March update downloads from MS want me to take and install the following Office 2007 patches: Update for the 2007 Microsoft Office System (KB977724) Date last published: 3/9/2010 Security Update for the 2007 Microsoft Office System (KB978380) Date last published: 3/9/2010 These are obviously for Office 2007. The information provided with the update makes no mention of working with or needed by Office 2003. But it keeps suggesting I install these 2 patches. Why? Should I install them? I do have MS Live Meeting 2007 inst...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...