H
HMS
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?
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?