Results 1 to 4 of 4

Thread: How to set the active cell using VBA in Excel

  1. #1
    Join Date
    Dec 2011
    Posts
    59

    How to set the active cell using VBA in Excel

    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.

    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
    Whenever I execute this procedure, I'm left in the Storage worksheet (as I should be) but the active cell is the same like it left the sheet. The four lines outputting various cells from the Main worksheet to the Storage worksheet works just fine, putting the data in the cells to the right of that cell.

    So if you guys have any ideas, please help me out.

  2. #2
    Join Date
    Nov 2010
    Posts
    503

    Re: How to set the active cell using VBA in Excel

    I have seen your code, I just want you to use the following
    c.Address.Select
    Outside of the loop
    I hope it works.

  3. #3
    Join Date
    Dec 2011
    Posts
    59

    Re: How to set the active cell using VBA in Excel

    I have placed the c.Address.Select outside the loop, in the same manner that you have told. But however it shows the same result. What am I doing wrong and how can I set the active cell? Please help. And thanks for the reply.

  4. #4
    Join Date
    Mar 2011
    Posts
    542

    Re: How to set the active cell using VBA in Excel

    According to my information you have to use the below
    c.select inspite using c.Address.Select.
    And let me know if it works.

Similar Threads

  1. How to disable excel for taking empty cell in Excel chart
    By Hache hi in forum MS Office Support
    Replies: 2
    Last Post: 25-02-2012, 12:30 PM
  2. Applying absolute cell referencing to many cell in Excel
    By Nyota in forum MS Office Support
    Replies: 2
    Last Post: 24-02-2012, 07:23 PM
  3. Cell format errors appears in Excel 2003 but not in Excel 2000
    By @nki$achi in forum MS Office Support
    Replies: 1
    Last Post: 24-01-2012, 05:00 PM
  4. Replies: 2
    Last Post: 04-01-2012, 06:54 PM
  5. Set Backcolor of a Excel cell
    By Quattro in forum Customize Desktop
    Replies: 4
    Last Post: 21-01-2009, 01:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,655,690.66533 seconds with 16 queries