Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to set the active cell from within a VBA procedure
I have a procedure where I scan a range 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 LookUpVal As Double Private c Private firstAddress 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 (tested this also). The address of the specified cell is stored in c.Address as it suppose to be (confirmed by the debug.print output). 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? Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to set the active cell from within a VBA procedure
Hi
use c.select -- Regards Frank Kabel Frankfurt, Germany "HMS" schrieb im Newsbeitrag ... I have a procedure where I scan a range 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 LookUpVal As Double Private c Private firstAddress 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 (tested this also). The address of the specified cell is stored in c.Address as it suppose to be (confirmed by the debug.print output). 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? Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
Calling procedure when leaving cell | Excel Worksheet Functions | |||
Unable to set the active cell from VBA | New Users to Excel |