Unable to set the active cell from VBA

I have a procedure where I scan a list of dates for a specific date and, once 
found, set the found date as the active cell.  The only problem is that the 
specified cell is not being made active.  Below is the code.  

Private Sub CommandButton1_Click()
' Locate CurrentDate within StorageDates and make it the active cell
    LookUpVal = Range("CurrentDate")
    Worksheets("Storage").Activate
    On Error Resume Next
    For Each c In Worksheets("Storage").Range("StorageDates")
        If c.Value = LookUpVal Then
            c.Address.Select
            Exit For
        End If
    Next
    Debug.Print "c.Address = " & c.Address
    Debug.Print "-------------------------"
    
' Copy temperatures variables & forecast MW to adjacent columns
      ActiveCell.Offset(0, 1).Value = Range("MainAvgTemp")
      ActiveCell.Offset(0, 2).Value = Range("MainMaxTemp")
      ActiveCell.Offset(0, 3).Value = Range("MainMaxDewPt")
      ActiveCell.Offset(0, 4).Value = Range("MainForecast")
    
End Sub

The sub works from a command button on the first worksheet named Main.  I 
get the same results whether executing it from the button or from directly 
inside of VBA.  The range names were defined in the spreadsheet.  Everything 
seems to work---almost.  CurrentDate is retrieved from the Main worksheet and 
passed to the do loop (tested this).  The correct date is found in the range 
StorageDates (reported out by the debug.print lines.  The address of the 
specified cell is stored in c.Address as it suppose to be.  However, the 
c.Address.Select doesn't activate the specified cell.  Whenever I execute 
this procedure, I'm left in the Storage worksheet (as I should be) but the 
active cell is the same as when I left the sheet.  The four lines outputting 
various cells from the Main worksheet to the Storage worksheet work just 
fine, putting the data in the cells to the right of that cell.

I've tried moving the c.Address.Select outside of the loop.  Same result.

What am I doing wrong and how can I set the active cell?
0
HMS1 (2)
12/7/2004 8:43:02 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
633 Views

Similar Articles

[PageSpeed] 58

Hi
just use
c.select

--
Regards
Frank Kabel
Frankfurt, Germany

"HMS" <HMS@discussions.microsoft.com> schrieb im Newsbeitrag
news:D7ADDB4C-6773-4B8F-84F6-D1556280F4B3@microsoft.com...
> I have a procedure where I scan a list of dates for a specific date
and, once
> found, set the found date as the active cell.  The only problem is
that the
> specified cell is not being made active.  Below is the code.
>
> Private Sub CommandButton1_Click()
> ' Locate CurrentDate within StorageDates and make it the active cell
>     LookUpVal = Range("CurrentDate")
>     Worksheets("Storage").Activate
>     On Error Resume Next
>     For Each c In Worksheets("Storage").Range("StorageDates")
>         If c.Value = LookUpVal Then
>             c.Address.Select
>             Exit For
>         End If
>     Next
>     Debug.Print "c.Address = " & c.Address
>     Debug.Print "-------------------------"
>
> ' Copy temperatures variables & forecast MW to adjacent columns
>       ActiveCell.Offset(0, 1).Value = Range("MainAvgTemp")
>       ActiveCell.Offset(0, 2).Value = Range("MainMaxTemp")
>       ActiveCell.Offset(0, 3).Value = Range("MainMaxDewPt")
>       ActiveCell.Offset(0, 4).Value = Range("MainForecast")
>
> End Sub
>
> The sub works from a command button on the first worksheet named
Main.  I
> get the same results whether executing it from the button or from
directly
> inside of VBA.  The range names were defined in the spreadsheet.
Everything
> seems to work---almost.  CurrentDate is retrieved from the Main
worksheet and
> passed to the do loop (tested this).  The correct date is found in
the range
> StorageDates (reported out by the debug.print lines.  The address of
the
> specified cell is stored in c.Address as it suppose to be.  However,
the
> c.Address.Select doesn't activate the specified cell.  Whenever I
execute
> this procedure, I'm left in the Storage worksheet (as I should be)
but the
> active cell is the same as when I left the sheet.  The four lines
outputting
> various cells from the Main worksheet to the Storage worksheet work
just
> fine, putting the data in the cells to the right of that cell.
>
> I've tried moving the c.Address.Select outside of the loop.  Same
result.
>
> What am I doing wrong and how can I set the active cell?

0
frank.kabel (11126)
12/7/2004 9:56:26 PM
Reply:

Similar Artilces:

Unable to open Outlook2003. Error: MAPI unable loading msncon.dll
I am trying to set up Outlook 2003 on home computer but keep getting the following message when trying to open application. MAPI was unable to load the information service msncon.dll. Since I am not open to open, I cannot get to the help feature. Please advise Have you downloaded the latest MSN Connector from their site? Or, if not using it, have you uninstalled it? Try opening Outlook in Safe mode (start->run->outlook.exe /safe) and go into the Advanced options and uncheck it from add-in manager or COM add-ins. --� Milly Staples [MVP - Outlook] Post all replies to the group to ...

Utility That Does Cell-By-Cell Comparison Of Two Spreadsheets?
.... and creates a report of differences... Anybody know of such a thing? Don't need anything sophisticated - just a simple-minded cell-by-cell comparison of data values. -- PeteCresswell Per (PeteCresswell): >Anybody know of such a thing? Oops! Ill-considered post. Just Googled it (duhhhh...) and found a number of relevant hits. -- PeteCresswell Pete, Since you asked... xlCompanion here... http://www.realezsites.com/bus/primitivesoftware Jim Cone San Francisco, USA "(PeteCresswell)" <x@y.Invalid> wrote in message news:pnm1u19vu11tn6rt5mmite6gcglm3r3e8r@...

Error when tracking an e-mail if recipient is in CRM or Active Dir
We are experiencing a problem when a user attempts to send an e-mail with the Outlook client. If a recipient - To, BCC or CC - is a user in one of the CRM picklists (Contacts, Users, etc) an error is generated indicating that the user does not have sufficient privileges for the operation. If the recipient is NOT in one of the CRM lists, no error is generated and the email is attached to the account referenced in RE: . This is happening for the salesperson role, but not for the sales manager role, so obviously there is some privilege that I need to grant the salesperson role, but i ca...

adding a comma at the end of a number to every cell
I have 22,000 cells with zip codes. I need to add a comma at the end of every number. Does anyone know how I can do this? Someone may have a better way but this worked for me. But please try it on a copy of the worksheet in case I am not understanding you correctly. Say my first zip code is in cell A1. In cell B1, enter the formula =A1&"," and copy it down. This causes column B to contain the zip code with the comma after it. Then I copied Column B and used Edit, Paste Special, Values to paste it over Column A. Judy Freed "Jeff" <jeff@nbotv.com> wrot...

Unable to Edit/Add records on Form in a New ADP
I just started playing around with rebuilding a MDB file as an ADP. I built a form and subform from scratch, but I'm unable to edit or add records. SELECT Id, LoadDate, TrailerDOTNumber, SealNumber, SealDate, SealId, LoadLocation, LoadStatus, DispatchStatus, DispatchLocationId, DispatchUserId FROM TrailerActivityHeaders ORDER BY LoadDate Recordset Type: Updatable Snapshot I'm getting the 'This recordset is not updatable' Ideas? Never mind... (Does anyone have a spare bandage btw? I cut my head banging it on the wall due to my stupidity...) "D...

Unable to open calendar
Folks, i cannot open or view my calendar. When I start Oultook a Pop Up appears saying "Could not read the Calendar" On clicking Ok, the pop up vanishes and nothing happens. If i then go over to my calendar, it says "Cannot display the folder" I have run Oultook Diagnostics and nothing shows up as irregular. I ran the repair function (off my setuo CD) and nothing happened. I also re-installed Outlook and rebooted my comp and nada. When staring in safe mode i also get he same errors. I am using Off 2007, Win 7 TIA, P "phamiltonsmith" <...

unable to download emails and unable to stop synchronization
I am going to try one more time to get this fixed. My Outlook 2003 has been unable to download emails. While it is trying, a small box in the tray section shows up and it states that it is trying to synchronize folders. After a time out time Outlook gives me errors. This is the error message. It has these numbers 0X800CCC15 , or 0X8004210A, or 0X8004210B, which these error messages state that either I did not have enough timeout or look and see if server name is wrong. All of you have given me these suggestions. no joy. Thank you very much however. I went ahead and finally found a sect...

Use color or marked cells to make a chart
We have an excel 2002 shared workbook that we use to keep track of our capacity for incoming patients. The cells are color coded for each different insurance. How can i build a chart to show how many of each insurance we have taken in each week. The cell range looks like this. ='2268 SEATTLE'!$B$5:$H$13,'2268 SEATTLE'!$B$17:$H$25,'2268 SEATTLE'!$B$35:$H$43,'2268 SEATTLE'!$B$47:$H$55,'2268 SEATTLE'!$B$65:$H$73,'2268 SEATTLE'!$B$77:$H$85,'2268 SEATTLE'!$B$95:$H$103,'2268 SEATTLE'!$B$107:$H$115 We would like to be able to kee...

set New currency EURO
I would like to know how to handle a new currency in GP, basically all of the transactions are in US dollars, however we need to create a vendor where all of his PO has to be in EURO ? How I can setup a new currency EURO ? Any help I will appreciate it. -- Thanks Miguel Dear, Go to Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Currency Define your currency then open the Multicurrency Access Setup window. (Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Multicurrency Access) Regards, -- Mohammad R. Daoud MCP, ...

How to set a default value in an Edit control
I am new to VC. I need to set a default value to an Edit control which is read-only. How can I do that in a dialog? I tried my_Edit_Control.SetWindowText("mytest") in it compiles successfully, but it crashed at run-time. Thanks. Tony Tony Hu wrote: >I am new to VC. I need to set a default value to an Edit control which is >read-only. How can I do that in a dialog? I tried >my_Edit_Control.SetWindowText("mytest") in it compiles successfully, but it >crashed at run-time. The read-only property applies only to the user, not the programmer using S...

Can I set the numerical type of Cell to Hex format
Dear all, Sorry to post again. Can I set the numerical type of the cell in Hex format, because I want to do the following task. Thanks. Now, if I type in 01/0001 in cell, and then I drag the mouse down to many cells , the number in the cell will be added by 1 automatically, just like the following 01/0001 01/0002 01/0003 . . . Now, if I type in 01/0001 and I want to drag the mouse down to many cells and I want the cell to add 1 automatically in Hex format. Can I do that? Simply, I want the cell t...

unable to scroll the page
on microsoft office frontpage 2003 - when i up loaded my web pages to my web site www.millertimeauction.com , there is no sroll bar for up & down of page - any help what browser? it scrolls for me in IE8 scroll bar will only appear if it needs to -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage Error" <FrontPageError@discussions.microsoft.com> wrote in message news:EC19C7CE-1C5A-4872-A47B-2C04BD7E96A7@microsoft.com... > on microsoft office frontpage 2003 - when i up loaded my web pages to my > web > s...

How do i set up outlook?
I just purchased a dell computer and it came with microsft office? I am trying to setup my outlook email account but i am not sure where to start. do you have to have an existing email account like hotmail or yahoo to get this setup? Please help!!! Sheariep wrote: > I just purchased a dell computer and it came with microsft office? I > am trying to setup my outlook email account but i am not sure where > to start. do you have to have an existing email account like hotmail > or yahoo to get this setup? Please help!!! You have to have a mail account somewhere if you want mail. Y...

how to use cursor to navigate active cells
With our Excel 2003, I could navigate to different cells by using the cursor keys. The cursor up key moved the active cell up. Left, right and down moved the active cell in the appropriate direction. I could select multiple cells by holding the shift key down and using the cursor keys to start selecting large groups of cells. Now, with Excel 2007, the cursor keys move the sheet around in the window, instead of moving the active cell in the indicated direction. I have found no way to select multiple cells. How can I get my Excel 2007 cursor keys to move the active cell, instead of moving t...

Condition is true in one cell, give me same condition in another
Have a problem. See below. If a condition or value is true in one cell, I need the same conditio or value to appear in another cell. If cell A1 = "N", then give me a "N" in cell C1. Problem is, I need to write the formula in cell D1 due t specifications in workbook. Any Ideas? T -- Message posted from http://www.ExcelForum.com Hi not possible with formulas. Formulas can only return a value but can't change the values of other cells. -- Regards Frank Kabel Frankfurt, Germany > Have a problem. See below. > > If a condition or value is true in one cel...

Unable to conect
Hi, I have set up my email account to work with aol using the guidlines for outlook 2002 but it is still unable to connect. Any suggestions? Thanks. John <John@discussions.microsoft.com> wrote: > I have set up my email account to work with aol using the guidlines > for outlook 2002 but it is still unable to connect. Any suggestions? Since you pay AOL for their service, my suggestion is to ask AOL. -- Brian Tillman ...

Typed Data Set and relations
I've a fairly complex schema which I'm trying to create a typed data set for. I've had a number of issues to date but it is sort of working (e.g. elements are not considered nullable unless minOccurs="0" is present. If not the codegen:nullValue attribute is ignored). What I'm seeing at the moment is that if I do NOT add in key and key ref relations, the cs generated puts in sensibly named relations with appropriate accessors. But they don't work. If I put in explicit key refs I get pairs of relations defined for each 'real' relation. The ones added c...

Cells with Validation lists attached
How can I indictate that a cell has a valaidation list attached to it without having to select the cell...so that if someone views the xls they can tell which cells have a validation list associated without having to actually select the individual cells? edit/goto special, validation, then give it a color...? Bob Umlas excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:49AFC808-DE74-4E39-9978-893352BE288E@microsoft.com... > How can I indictate that a cell has a valaidation list attached to it without > having to select the cell...so that if som...

Unable to start POP3, NNTP, IMAP4...
Dear all, I'm evaluating to install Exchange 2003. After the installation I find the said protocol haven't been started. I tried to start them in "Servers --> Server Name --> Protocols" but no help. Please comment.... Thanks!! Background: Windows 2003, Exchange 2003, Active Directory DC with all roles of FSMO as it's the first DC in the forest. Thank you very much!! -- Are you boring? Let's come to talk together... ^^ news://news.hk4u.com/hk4u.2hit6 Those services are disabled by default in Exchange 2003. Go to Administrative Tools, Services and enable...

Sum cells in different colors
Can you sum all the numbers in a column that are green only? I do not want to sum numbers in other colors. You need a VBA macro to do this. See http://www.cpearson.com/excel/colors.htm , -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "heater" <heater@discussions.microsoft.com> wrote in message news:51EADCED-9548-4260-B16C-174CD8751A31@microsoft.com... > Can you sum all the numbers in a column that are green only? I > do not want > to sum numbers in other colors. ...

Unable to click on charts & unable to create any new charts Excel
Hi Just started using Microsoft Excel 2007 and I seem to have done something that means the chart option is unavailable. The chart section is now grey and all charts I have created are 'locked' I cannot click on them anywhere. I'm working on a project and need it finished by Monday. Do you mean that if you click on a chart it does not get a border to show it is activated and there are no range-finder borders around the data that makes the chart? This is a bit odd. Have you tried a new workbook to see if the problem is with just this one? My advice with all odd things is...

Using secondary email when distributing a Campaign Activity
Hello everyone, I'm looking for a way to use the marketing list's member primary AND secondary emails when send a email mass mailout from a campaign activity. I believe we could do it using the sdk, but am not sure how (plug-in, workflow?) Has anyone achieved this somehow? Thanks Hi Fred, Yes, our latest VEM (Vizola eMarketing Addin) module has this feature, please checkout http://www.vizola.com/vem - you can download and try the evaluation version - it also includes all the template usage + tracking and bounce features you need. On the COMPOSE EMAIL screen you are presented w...

Setting Gmail IMAP
I previously used Gmail as POP but I have just changed it to IMAP so that I can keep my PC (WLM), my iPhone and my webmail all in sync. Now I need some advice. 1. In addition to my inbox,sent etc folders I also have a folder called All Mail that indeed contains all messages I have ever sent or received. It is therefore very big. How can I stop that folder showing up in WLM? 2. When I delete items in WLM the are not going to the Bin (deleted folder) They just vanish. The deleted folder is a useful facility as at times I may accidently click to delete the wrong message. How c...

MSXML6 C++ unable to locate node
Hi, I am new to using the MSXML functions in C++. I found this on MSDN as an example of what I wanted to do, http://msdn2.microsoft.com/en-us/library/ms765465.aspx. I have tried a number of XPATH examples to try and retrieve the "><Code>12455</Code>", but have been unable to do so. The MSDN example works fine. So, I am wondering if it's a namespace issue? Any help would be appreciated. Thanks. Jeff I have changed the code as follows: int _tmain(int argc, _TCHAR* argv[]) { CString sResponse = "<?xml version=\"1.0\" encoding=\"utf...

Win32 or COM API to set vlan id and mtu for a network interface
Hi, I want to set vlan id and mtu for a network interface. Is there any win32 or COM API to do this ? I want to do this on windows server 2003 r2, 2008, 2008 R2. I am using visual studio 2008. Thank you, Vivek S ...