Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
HMS
 
Posts: n/a
Default 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?
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
just use
c.select

--
Regards
Frank Kabel
Frankfurt, Germany

"HMS" schrieb im Newsbeitrag
...
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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
In excel, I seem to be "locked" on a cell and unable to click off. Bubba Junie Excel Discussion (Misc queries) 7 May 1st 06 07:46 AM
Why am I unable to link a cell in one worksheet to a cell in anot. Dave Excel Discussion (Misc queries) 1 January 8th 05 02:51 AM
Sum to active cell GregR Excel Discussion (Misc queries) 4 December 10th 04 11:01 PM
Highlight Active Cell Db1712 Excel Discussion (Misc queries) 1 November 26th 04 02:14 PM


All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"