# MS Excel - Changing the Active Cell Macro



## Pynt (Jul 2, 2004)

I am trying to use part of a macro to change the active cell to a cell address determined from data entered by the user. For example, a user enters a date, the date is compared to a list of dates and when a match is found the cell address is returned. I want to then make the returned address the active cell. Can this be done and if so how?


----------



## matikin9 (Jun 29, 2004)

You would need to use VB code. I don't think you can record a macro to do that...do you already have a way to find the matching date or do you need help on that part too?


----------



## Kelly_in_LA (Jun 16, 2004)

Whoa, baby!

Dates can be tricky.

I made an excel file with a macro inside which will perform a very crude demonstration of a very simple date search you could do.

The macro works within the file I made, but it may encounter problems in other xls files, depending on how they are formatted.

For instance, I'm not so sure my macro will work if there is a date in a cell that LOOKS LIKE "05/13/1988" but in reality is FORMATTED AS TEXT rather than a date.

Obviously, that problem could be handled in a macro, but it just makes things more complex.

So I will reemphasize that my macro is a very simplistic demonstration.

Here is the macro. But as I said before, the only way I can "guarantee" results is if you use Sheet1 of the workbook I am posting as an attachment.


```
Sub FindDateCell()

Dim UserInput As String 'the input from the input box
Dim UserDate As Date 'we will convert the input to date format and store it here
Dim myCell As Range 'an object variable to look in each cell of the sheet

UserInput = InputBox("Please enter a date using the following format:" _
                     & vbCr & vbCr & "MM/DD/YYYY", "Enter a date", "MM/DD/YYYY")

If UserInput = "" Then End
'if the user cancels or doesn't enter a thing, then end

On Error Resume Next 'turn automatic error interruptions off for the next line

    UserDate = CDate(UserInput)
    'the above line will create an error if user didn't use correct date format
    If Err.Number = 13 Then MsgBox "Sorry, you did not enter the date correctly": End
    
On Error GoTo 0 'turn automatic error interruptions back on

For Each myCell In ActiveSheet.UsedRange  'cycle through all cells that have data

    If myCell.Cells.Value = UserDate Then 'does the cell match the date?
        myCell.Select 'if it matches, select it!
        MsgBox "The first occurrence of " & UserDate & " was found in " & myCell.Address
        End 'we found the cell, so now end
    End If

Next

MsgBox UserDate & " was not found in any cell."

End Sub
```
I want everyone to know that I would welcome criticisms or "helpful pointers" about this.

Honestly, the date type makes me a little squeamish, so if anyone has anything to teach me, please please do so.

Sincerely,
Kelly
kellyjones.netfirms.com


----------



## Pynt (Jul 2, 2004)

Thank you all. I've incorporated bits of Kelly's macro and everything is running sweet. I wish I had found this site months ago! Cheers Kelly.


----------

