Select range from ActiveCell do to Lastcell
From Leith Ross 2/7/2006 Dim EndCell As Range Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) ActiveSheet.Range(ActiveCell, EndCell).ClearContents If I change the A to an N it selects a range from ActiveCell down to last entry in Column N. My ActiveCell is in Column A but Column N may not have data down to the last cell as in Column A How do I select a range of cells from an ActiveCell in column A across to Column N and down to last data cell in column A -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware.
|
-1 |
|
Utf
3/11/2010 10:56:06 AM
excel.programming
6508 articles. 
2 followers.
6 Replies
1017 Views
Dim EndCell As Range
Dim FirstRow as Integer
Dim LastRow as Integer
FirstRow = ActiveSheet.ActiveCell.Row
LastRow ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set DataRange = ActiveSheet.Range("N" & FirstRow & ":N" & LastRow)
DataRange.ClearContents
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186589
[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
|
|
-1 |
|
joel
3/11/2010 11:08:38 AM
Hi,
Try this with the usual caveat that it is very unlikely you need to select
the range to do what you want. The last line could simply be
MyRangeA.ClearContents
Dim MyRangeA As Range
Dim LastRowA As Long
LastRowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRangeA = Range("A" & ActiveCell.Row & ":N" & LastRowA)
MyRangeA.Select
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Aussie Bob C" wrote:
> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
> --
> Thank you
>
> Aussie Bob C
> Little cost to carry knowledge with you.
> Win XP P3 Office 2007 on Mini Mac using VMware.
|
|
1 |
|
Utf
3/11/2010 11:09:01 AM
Hello Aussie Bob,
Whether you use the If/Else/EndIf is up to you but I should think that you
would not want code selecting the range if you have not previously selected a
cell in the correct column.
Having said that, normally with code it is not necessary to actually select
ranges but not knowing what you are doing with the code it is hard to advise
the best way for your particular case.
Sub SelectSpecific()
Dim lastRow As Long
With ActiveSheet
If ActiveCell.Column = Columns("A").Column Then
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(ActiveCell, .Cells(lastRow, "N")).Select
Else
MsgBox "Activecell not in column A"
End If
End With
End Sub
--
Regards,
OssieMac
"Aussie Bob C" wrote:
> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
> --
> Thank you
>
> Aussie Bob C
> Little cost to carry knowledge with you.
> Win XP P3 Office 2007 on Mini Mac using VMware.
|
|
0 |
|
Utf
3/11/2010 11:16:01 AM
Hi Bob
Dim lr as long, fr as long
with activesheet
fr=activecell.row
lr = cells(Rows.count("A").End(Xlup).Row
range("A" & fr & ":N" & lr).clearcontents
end with
--
Regards
Roger Govier
Aussie Bob C wrote:
> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
|
|
0 |
|
Roger
3/11/2010 11:25:55 AM
This should do it: Sub test() Dim EndCell As Range Set EndCell = Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp), "N") ActiveSheet.Range(ActiveCell, EndCell).ClearContents End Sub Mike F "Aussie Bob C" <AussieBobC@discussions.microsoft.com> wrote in message news:D1B81B79-6A4E-45B6-8F0C-38E584549725@microsoft.com... > From Leith Ross 2/7/2006 > > Dim EndCell As Range > Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) > ActiveSheet.Range(ActiveCell, EndCell).ClearContents > > If I change the A to an N it selects a range from ActiveCell down to last > entry in Column N. > My ActiveCell is in Column A but Column N may not have data down to the > last cell as in Column A > > How do I select a range of cells from an ActiveCell in column A across to > Column N and down to last data cell in column A > > -- > Thank you > > Aussie Bob C > Little cost to carry knowledge with you. > Win XP P3 Office 2007 on Mini Mac using VMware.
|
|
0 |
|
Mike
3/11/2010 11:31:32 AM
Hi OssieMac
Thanks for the code with the If/Else/Endif safety added.
Just what I needed.
--
Thank you
Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.
"OssieMac" wrote:
> Hello Aussie Bob,
>
> Whether you use the If/Else/EndIf is up to you but I should think that you
> would not want code selecting the range if you have not previously selected a
> cell in the correct column.
>
> Having said that, normally with code it is not necessary to actually select
> ranges but not knowing what you are doing with the code it is hard to advise
> the best way for your particular case.
>
> Sub SelectSpecific()
>
> Dim lastRow As Long
>
> With ActiveSheet
> If ActiveCell.Column = Columns("A").Column Then
> lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> .Range(ActiveCell, .Cells(lastRow, "N")).Select
> Else
> MsgBox "Activecell not in column A"
> End If
> End With
>
> End Sub
>
>
> --
> Regards,
>
> OssieMac
>
>
> "Aussie Bob C" wrote:
>
> > From Leith Ross 2/7/2006
> >
> > Dim EndCell As Range
> > Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> > ActiveSheet.Range(ActiveCell, EndCell).ClearContents
> >
> > If I change the A to an N it selects a range from ActiveCell down to last
> > entry in Column N.
> > My ActiveCell is in Column A but Column N may not have data down to the
> > last cell as in Column A
> >
> > How do I select a range of cells from an ActiveCell in column A across to
> > Column N and down to last data cell in column A
> >
> > --
> > Thank you
> >
> > Aussie Bob C
> > Little cost to carry knowledge with you.
> > Win XP P3 Office 2007 on Mini Mac using VMware.
|
|
0 |
|
Utf
3/15/2010 7:40:02 AM
Similar Artilces:
Fill Color a Range
I want to fill a range with a color. I know how to do that but I don't know how to keep the grid lines visible in that range. It seems I would need a transparent color or something like that. How can I fill a range with a color that will allow the grid lines to show through? Thank you. The only way is to give the gridlines an alternative colour of their own. -- HTH RP (remove nothere from the email address if mailing direct) "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message news:112bdn5bm4udrff@corp.supernews.com... > I want to fill a range with a ...
How to select multi-user ???
For example, peter & john's email account are installed in the same post office or identity(I don't know how to say in outlook), when I compose new email, I can select the sender is peter or john in outlook express, but in outlook, no selection can be provided. HOW TO DO IT ??? Appreciate for any advice !!! jj <jj@discussions.microsoft.com> wrote: > For example, peter & john's email account are installed in the same > post office or identity(I don't know how to say in outlook), when I > compose new email, I can select the sender is peter or john in...
Creating Chart from Userform ListBox selections
This is a multi-part message in MIME format. ------=_NextPart_000_0016_01C911A4.100F32B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have created a Userform with 2 Listboxes on it Listbox1, is populated with ALL values in Sheet3 Column B Listbox2, is populated with ALL values in Sheet3.Column A Column B values are Names Column A are Dates Both Listboxes have Multi-Select enabled. I am trying to set up a chart in Sheet6 with the data from Sheet3, but = am up to the stage of how to create it. Sheet3 data is stored in rows, and eac...
Formula to count number of days that match, when comparing one date range to another
I am trying to count any matching days when comparing one date range to another. Any formula suggestions would be appreciated. For example: Range 1 Start Date1 Thru Date1 Start Date 2 Thru Date 2 Count Match 3/4/08 4/9/09 3/5/08 3/7/09 formula to show "3" 5/1/08 5/15/08 4/8/08 5/5/08 formula to show "5" Etc Not sure how you arrive at a result of 3 for the first group. There are 368 days that overlap. This formula returns 5 for the second group: =MAX(0,MIN(B3,D3)-MAX(A3,C3)+1) -- Biff...
Printer Selection
I have 2 printers tied to the RMS computer. One is the Epson T88iii and the other is an HP Officejet. When I back order, create a workorder, lay-a-way, etc, I want the print out full page on the HP. How do I set this up? This is a multi-part message in MIME format. ------=_NextPart_000_0077_01C6D670.BFB550D0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Bill, SO Manager | Database | Registers | Receipt Format | select the format = you're using for your Epson | delete the formats for workorder, layaway, = etc with the red X SO Ma...
Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...
Change 3rd cell colour based on cell 1 >= cell2 in range
Hi all - I have looked through the group for a solution to this particular problem but i think my inexperience is possibly stopping me from seeing the solution .. so apologies if it has already been answered many times previously. My problem is this : I have two columns Col1 = Cumulative Sales and Col2 = Cumulative Target and a range of 1 - 31(signifies the days of the month). I have applied conditional formatting to all the cells in Col1 so that as a value is entered into a cell in Col1 the cell turns green if the value is >= to the corresponding cell in Col2, and red if < than the c...
selecting email account when sending a new email
I have 3 email accounts in Outlook 2003 that I send and receive emails from. I used to be able to change the email account in which to Send a new email from; but that feature disappeared at some point. I checked the support page and tried to find instructions on how to set up Outlook 2003 to allow you to be able to select a different email address to send an email from rather than always sending emails from the main, default email address. The instructions said to click on "Accounts" in a new email. I do not have that option anywhere in a new email message and I do not ...
Checking ALL values in a range
I'm trying to create a formula which checks that a range of cells e.g. A2:F2 contain all the values 1,2,3,4,5,6 irrespective of which cell each number is in. Unfortunately, everything I've tried has failed so any help would be appreciated. Thanks One way, though not scaleable: =IF(COUNTIF(A2:F2,1)>0,1,0)+IF(COUNTIF(A2:F2,2)>0,1,0)+IF(COUNTIF(A2:F2,3)>0,1,0)+IF(COUNTIF(A2:F2,4)>0,1,0)+IF(COUNTIF(A2:F2,5)>0,1,0)+IF(COUNTIF(A2:F2,6)>0,1,0) Regards Trevor "nospaminlich" <nospaminlich@discussions.microsoft.com> wrote in message news:F971FD92-38F...
Return cell content in a matched range
I need help with the following formula: =IF(ISERROR(SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)))=FALSE,SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)),"") This formula works well for Sum total for all matches however I need to use it to return a word instead of a SUM . Example: Column D contains the folowing payment methods. ATM, Check, Draft I want to use the formula to find the match of A1 in the range F8-F100 and return what payment method was used located in Column D on the matched row. As you can tell it i...
Returning a named range source reference
Using XL03 I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model und...
Named-range source-data for pie charts on copied worksheets
I have to finally concede that I'm stuck and have to ask for advice. I am using Excel 2000 and have been able to use Andy Pope's help on "Automatic removal of zero values in pie chart". That works fine. But if I copy the worksheet, the pie chart on the copied (new) worksheet references the named range as defined on the original worksheet. My named ranges, which the pie chart references, are: PieLabels=OFFSET(blank!$O$26,0,0,COUNT(blank!$N$26:$N$34),1) PieValues=OFFSET(blank!$N$26,0,0,COUNT(blank!$N$26:$N$34),1) So, one solution might be to define the same named range on dif...
How do you implement selectable alternative DLLs?
I've got a need to allow my application to use a differrent version of a data access DLL based on a user's selection, or automatically discovered difference. In particular, I have an application that I want the user to be able to select the type of data source (Oraqcle, MSSQL, XML, Web Service, etc) and the application will use the applicable DLL from among the multiple DLLs provided with minimal coding. Ideally, I would have a DLL for each of the data sources supported and would just select one at startup and the code would remain the same for all calls regardless of...
Update Status field by record selection
Table 1 Name: Bank Table 2 Name: Ledger 1 Main form with 4 subforms (only having problem with 2 subforms) 1st Subform: UnMatch1 (query based on Ledger) 2nd Subform: UnMatch2 (query based on Bank) Once my users verfied information from both subforms, they will decide if those items match (by record) on the Status field. I need to have a CmdMatch which can allow users to select particular record and make update the Status to Match or UnMatch. I did some research all day but only one that's close enough to what I wanted is the following code: Private Sub CmdMatch_Click() Dim rs As...
Require value
Using a tabbed subform - the code below works perfect to validate one field and force a required value for a second field IF the users tabs through the fields. However, I'm not sure how to compile the code that gives the user the option of tabbing or manually selecting each field. Private Sub ALARM_Q2_REM_Exit(Cancel As Integer) 'if the alarm rate <3 and the rem doesn't have a value then pop a message 'and force the user to stay in the control until it has a value If (Me.ALARM_Q2_RATE.Value) > 0 And (Me.ALARM_Q2_RATE.Value) < 3 And _ Nz(Me.ALARM_Q2_REM.Va...
access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and then select the higher value, insert it into another field and then use in a formula. E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field and then be multiplied. On 11 apr, 21:26, Captain Turtle <Captain Tur...@discussions.microsoft.com> wrote: > In Access database I want to compare the values in two fields in a form a= nd > then select the higher value, insert it into another field and then use i= n a > formula. > > E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...
changing CRichEditView color without changing the selection?
I'm trying to change the color of a portion of text inside a CRichEditView control. The only way that i know of to do this is to highlight the text and then set the character formatting to change the color. Just wondering if there is a way to avoid highlighting the text and yet be able to colorize it? Refer http://www.codeproject.com/richedit/htmlricheditctrlssl.asp "MrPolite" <kderakhshanAtMSN@msn.com> wrote in message news:OUMPf.6536$e1.235@tornado.socal.rr.com... > I'm trying to change the color of a portion of text inside a CRichEditView > control. T...
Code to look at range and extract data.
If the column of data has the text "Back On" I want to copy the adjacent columns to another sheet for each instance. This will be a loop since this text occurs more than once. If I understand you correct ? Start here http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JDJacobs" <JDJacobs@discussions.microsoft.com> wrote in message news:5B3B0D6A-CE19-426B-BBB6-F68B9AD6C2DB@microsoft.com... > If the column of data has the text "Back On" I want to copy the adjacent > columns to a...
Counting time ranges
I have a spreadsheet that looks like this... 07:51:4 07:56:2 07:59:5 08:36:0 08:36:2 09:04:2 09:08:1 09:12:2 09:13:3 09:15:4 09:17:3 09:17:3 09:20:1 09:23:1 09:24:1 I need to be able to count how many times each hour shows up, I tried using count if but the range isnt working? Please help!!! Cathy Hi maybe something like =SUMPRODUCT(--(A1:a100>=9/24),--(A1:A100<10/24)) to count the numer of 9 hour occurences >-----Original Message----- >I have a spreadsheet that looks like this.... >07:51:40 >07:56:27 >07:59:59 >08:36:01 >08:36:24 >09:04:25 >09:08:18 >09:...
How To Select Multiple Transactions At One Time
I would like to delete a block of transactions from an account in Money 2007 Plus Deluxe. Is there away to select multiple transactions and then delete all of them or must I do each one, "one-at-a-time" ? In microsoft.public.money, Snoopy328 wrote: >I would like to delete a block of transactions from an account in Money 2007 >Plus Deluxe. Is there away to select multiple transactions and then delete >all of them or must I do each one, "one-at-a-time" ? The later. You could get some kind of keyboard macro if you are doing really a lot of these. Otherwise, c...
Refer to a named range
Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name <> A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("...
Totalling records in a date range
I have a report that shows sums of various fields, grouped by date. If i run my report based on specific dates for example between 1/1/2008 and 1/5/2008, each date is displayed as a page in the report. i am looking to run my report so all my fields are totalled based on the dates selected, without displaying each individual day. I was able to add a field that totals all my fields in the date range, but it doesn't give me the specific totals of each field I am looking for. As always any help is greatly appreciated. -- Jon M. Do not include the dates in the query but the date r...
Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...
Auto populate form field based on selection
Hi, I have a form which is linked to a table(tblAntibody) for data entry. I have another table(tblCatalogPartNumbers) with no relationship to the first which contains all of our codes and has four coulmns CatalogNumber, UnqualifiedNumber,NIPartNumber, and PIPartNumber. All of these columns store thier data in tblAntibody. On the form CatalogNumber is a combo box that has it's selection criterial set to tblListCatalogPartNumbers. What I need to happen is when a user selects a catalog number from the combo box, I need the other 3 fields to auto populate from tblCatalogPartNumbers. Catalo...
paste over a filtered range
I have a spreadsheet with 40000 records, I have filtered down to 1000 records that have one coloumn of incorrect information that needs replacing, how do I paste the new data over the old?? What keeps happening is the new data pastes over the first 1000 records, not only the filtered recorsds freddie When you have your filter in place and the 1000 records are showing, hit F5>Special>Visible cell only and OK. Now paste your replacement data. Gord Dibben Excel MVP On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711" <freddie2711@discussions.microsoft.com> wrote: >...
I want to fill a range with a color. I know how to do that but I don't know how to keep the grid lines visible in that range. It seems I would need a transparent color or something like that. How can I fill a range with a color that will allow the grid lines to show through? Thank you. The only way is to give the gridlines an alternative colour of their own. -- HTH RP (remove nothere from the email address if mailing direct) "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message news:112bdn5bm4udrff@corp.supernews.com... > I want to fill a range with a ...
How to select multi-user ???
For example, peter & john's email account are installed in the same post office or identity(I don't know how to say in outlook), when I compose new email, I can select the sender is peter or john in outlook express, but in outlook, no selection can be provided. HOW TO DO IT ??? Appreciate for any advice !!! jj <jj@discussions.microsoft.com> wrote: > For example, peter & john's email account are installed in the same > post office or identity(I don't know how to say in outlook), when I > compose new email, I can select the sender is peter or john in...
Creating Chart from Userform ListBox selections
This is a multi-part message in MIME format. ------=_NextPart_000_0016_01C911A4.100F32B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have created a Userform with 2 Listboxes on it Listbox1, is populated with ALL values in Sheet3 Column B Listbox2, is populated with ALL values in Sheet3.Column A Column B values are Names Column A are Dates Both Listboxes have Multi-Select enabled. I am trying to set up a chart in Sheet6 with the data from Sheet3, but = am up to the stage of how to create it. Sheet3 data is stored in rows, and eac...
Formula to count number of days that match, when comparing one date range to another
I am trying to count any matching days when comparing one date range to another. Any formula suggestions would be appreciated. For example: Range 1 Start Date1 Thru Date1 Start Date 2 Thru Date 2 Count Match 3/4/08 4/9/09 3/5/08 3/7/09 formula to show "3" 5/1/08 5/15/08 4/8/08 5/5/08 formula to show "5" Etc Not sure how you arrive at a result of 3 for the first group. There are 368 days that overlap. This formula returns 5 for the second group: =MAX(0,MIN(B3,D3)-MAX(A3,C3)+1) -- Biff...
Printer Selection
I have 2 printers tied to the RMS computer. One is the Epson T88iii and the other is an HP Officejet. When I back order, create a workorder, lay-a-way, etc, I want the print out full page on the HP. How do I set this up? This is a multi-part message in MIME format. ------=_NextPart_000_0077_01C6D670.BFB550D0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Bill, SO Manager | Database | Registers | Receipt Format | select the format = you're using for your Epson | delete the formats for workorder, layaway, = etc with the red X SO Ma...
Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...
Change 3rd cell colour based on cell 1 >= cell2 in range
Hi all - I have looked through the group for a solution to this particular problem but i think my inexperience is possibly stopping me from seeing the solution .. so apologies if it has already been answered many times previously. My problem is this : I have two columns Col1 = Cumulative Sales and Col2 = Cumulative Target and a range of 1 - 31(signifies the days of the month). I have applied conditional formatting to all the cells in Col1 so that as a value is entered into a cell in Col1 the cell turns green if the value is >= to the corresponding cell in Col2, and red if < than the c...
selecting email account when sending a new email
I have 3 email accounts in Outlook 2003 that I send and receive emails from. I used to be able to change the email account in which to Send a new email from; but that feature disappeared at some point. I checked the support page and tried to find instructions on how to set up Outlook 2003 to allow you to be able to select a different email address to send an email from rather than always sending emails from the main, default email address. The instructions said to click on "Accounts" in a new email. I do not have that option anywhere in a new email message and I do not ...
Checking ALL values in a range
I'm trying to create a formula which checks that a range of cells e.g. A2:F2 contain all the values 1,2,3,4,5,6 irrespective of which cell each number is in. Unfortunately, everything I've tried has failed so any help would be appreciated. Thanks One way, though not scaleable: =IF(COUNTIF(A2:F2,1)>0,1,0)+IF(COUNTIF(A2:F2,2)>0,1,0)+IF(COUNTIF(A2:F2,3)>0,1,0)+IF(COUNTIF(A2:F2,4)>0,1,0)+IF(COUNTIF(A2:F2,5)>0,1,0)+IF(COUNTIF(A2:F2,6)>0,1,0) Regards Trevor "nospaminlich" <nospaminlich@discussions.microsoft.com> wrote in message news:F971FD92-38F...
Return cell content in a matched range
I need help with the following formula: =IF(ISERROR(SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)))=FALSE,SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)),"") This formula works well for Sum total for all matches however I need to use it to return a word instead of a SUM . Example: Column D contains the folowing payment methods. ATM, Check, Draft I want to use the formula to find the match of A1 in the range F8-F100 and return what payment method was used located in Column D on the matched row. As you can tell it i...
Returning a named range source reference
Using XL03 I am using the code below, trying to create a list of named ranges and their source references. I'm having two problems. (1) There are some other names (named objects) in the workbook, and I don't want to return those; I just want to return named ranges, and (2) The code below returns the resulting range referred to, but not the source reference. For example, if the named range is "=offset(A1,1,1)" then I need to see that offset statement, not the resulting range of "B2". I tried everything that made sense from the help object model und...
Named-range source-data for pie charts on copied worksheets
I have to finally concede that I'm stuck and have to ask for advice. I am using Excel 2000 and have been able to use Andy Pope's help on "Automatic removal of zero values in pie chart". That works fine. But if I copy the worksheet, the pie chart on the copied (new) worksheet references the named range as defined on the original worksheet. My named ranges, which the pie chart references, are: PieLabels=OFFSET(blank!$O$26,0,0,COUNT(blank!$N$26:$N$34),1) PieValues=OFFSET(blank!$N$26,0,0,COUNT(blank!$N$26:$N$34),1) So, one solution might be to define the same named range on dif...
How do you implement selectable alternative DLLs?
I've got a need to allow my application to use a differrent version of a data access DLL based on a user's selection, or automatically discovered difference. In particular, I have an application that I want the user to be able to select the type of data source (Oraqcle, MSSQL, XML, Web Service, etc) and the application will use the applicable DLL from among the multiple DLLs provided with minimal coding. Ideally, I would have a DLL for each of the data sources supported and would just select one at startup and the code would remain the same for all calls regardless of...
Update Status field by record selection
Table 1 Name: Bank Table 2 Name: Ledger 1 Main form with 4 subforms (only having problem with 2 subforms) 1st Subform: UnMatch1 (query based on Ledger) 2nd Subform: UnMatch2 (query based on Bank) Once my users verfied information from both subforms, they will decide if those items match (by record) on the Status field. I need to have a CmdMatch which can allow users to select particular record and make update the Status to Match or UnMatch. I did some research all day but only one that's close enough to what I wanted is the following code: Private Sub CmdMatch_Click() Dim rs As...
Require value
Using a tabbed subform - the code below works perfect to validate one field and force a required value for a second field IF the users tabs through the fields. However, I'm not sure how to compile the code that gives the user the option of tabbing or manually selecting each field. Private Sub ALARM_Q2_REM_Exit(Cancel As Integer) 'if the alarm rate <3 and the rem doesn't have a value then pop a message 'and force the user to stay in the control until it has a value If (Me.ALARM_Q2_RATE.Value) > 0 And (Me.ALARM_Q2_RATE.Value) < 3 And _ Nz(Me.ALARM_Q2_REM.Va...
access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and then select the higher value, insert it into another field and then use in a formula. E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field and then be multiplied. On 11 apr, 21:26, Captain Turtle <Captain Tur...@discussions.microsoft.com> wrote: > In Access database I want to compare the values in two fields in a form a= nd > then select the higher value, insert it into another field and then use i= n a > formula. > > E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...
changing CRichEditView color without changing the selection?
I'm trying to change the color of a portion of text inside a CRichEditView control. The only way that i know of to do this is to highlight the text and then set the character formatting to change the color. Just wondering if there is a way to avoid highlighting the text and yet be able to colorize it? Refer http://www.codeproject.com/richedit/htmlricheditctrlssl.asp "MrPolite" <kderakhshanAtMSN@msn.com> wrote in message news:OUMPf.6536$e1.235@tornado.socal.rr.com... > I'm trying to change the color of a portion of text inside a CRichEditView > control. T...
Code to look at range and extract data.
If the column of data has the text "Back On" I want to copy the adjacent columns to another sheet for each instance. This will be a loop since this text occurs more than once. If I understand you correct ? Start here http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JDJacobs" <JDJacobs@discussions.microsoft.com> wrote in message news:5B3B0D6A-CE19-426B-BBB6-F68B9AD6C2DB@microsoft.com... > If the column of data has the text "Back On" I want to copy the adjacent > columns to a...
Counting time ranges
I have a spreadsheet that looks like this... 07:51:4 07:56:2 07:59:5 08:36:0 08:36:2 09:04:2 09:08:1 09:12:2 09:13:3 09:15:4 09:17:3 09:17:3 09:20:1 09:23:1 09:24:1 I need to be able to count how many times each hour shows up, I tried using count if but the range isnt working? Please help!!! Cathy Hi maybe something like =SUMPRODUCT(--(A1:a100>=9/24),--(A1:A100<10/24)) to count the numer of 9 hour occurences >-----Original Message----- >I have a spreadsheet that looks like this.... >07:51:40 >07:56:27 >07:59:59 >08:36:01 >08:36:24 >09:04:25 >09:08:18 >09:...
How To Select Multiple Transactions At One Time
I would like to delete a block of transactions from an account in Money 2007 Plus Deluxe. Is there away to select multiple transactions and then delete all of them or must I do each one, "one-at-a-time" ? In microsoft.public.money, Snoopy328 wrote: >I would like to delete a block of transactions from an account in Money 2007 >Plus Deluxe. Is there away to select multiple transactions and then delete >all of them or must I do each one, "one-at-a-time" ? The later. You could get some kind of keyboard macro if you are doing really a lot of these. Otherwise, c...
Refer to a named range
Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name <> A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("...
Totalling records in a date range
I have a report that shows sums of various fields, grouped by date. If i run my report based on specific dates for example between 1/1/2008 and 1/5/2008, each date is displayed as a page in the report. i am looking to run my report so all my fields are totalled based on the dates selected, without displaying each individual day. I was able to add a field that totals all my fields in the date range, but it doesn't give me the specific totals of each field I am looking for. As always any help is greatly appreciated. -- Jon M. Do not include the dates in the query but the date r...
Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...
Auto populate form field based on selection
Hi, I have a form which is linked to a table(tblAntibody) for data entry. I have another table(tblCatalogPartNumbers) with no relationship to the first which contains all of our codes and has four coulmns CatalogNumber, UnqualifiedNumber,NIPartNumber, and PIPartNumber. All of these columns store thier data in tblAntibody. On the form CatalogNumber is a combo box that has it's selection criterial set to tblListCatalogPartNumbers. What I need to happen is when a user selects a catalog number from the combo box, I need the other 3 fields to auto populate from tblCatalogPartNumbers. Catalo...
paste over a filtered range
I have a spreadsheet with 40000 records, I have filtered down to 1000 records that have one coloumn of incorrect information that needs replacing, how do I paste the new data over the old?? What keeps happening is the new data pastes over the first 1000 records, not only the filtered recorsds freddie When you have your filter in place and the 1000 records are showing, hit F5>Special>Visible cell only and OK. Now paste your replacement data. Gord Dibben Excel MVP On Tue, 26 Apr 2005 15:36:02 -0700, "freddie2711" <freddie2711@discussions.microsoft.com> wrote: >...