VBA: Find and adress cell
 

Hello!

I am trying to do the following: The user chooses a cell
via an InputBox (the cell contains a date); then the macro
needs to switch to another sheet an find this value/the
date in Column "A" and activate it. I just don't know how
to adress it ... Can anybody help?

Thanks,

Maria

Find and adress cell
 

Hi Maria,

Try this

Dim ans
Dim c As Range

ans = InputBox("Input date")
If Not IsDate(ans) Then
MsgBox "Invalid date"
Exit Sub
Else
Worksheets("Sheet2").Activate
Set c = Columns(1).Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.Activate
End If
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maria" wrote in message
...
Hello!

I am trying to do the following: The user chooses a cell
via an InputBox (the cell contains a date); then the macro
needs to switch to another sheet an find this value/the
date in Column "A" and activate it. I just don't know how
to adress it ... Can anybody help?

Thanks,

Maria

VBA: Find and adress cell
 

Maria,
You could pass the date received from the message box to the following sub.
This assumes that the Programmatic name of the sheet you have the list on is
Sheet2 and that the range is from A1 to A100. Modify the code appropriately.

Sub Switch(ByVal d As Double)

Dim lRow&

On Error Resume Next
lRow = WorksheetFunction.Match(d, Sheet2.Range("A1:A100"), 0)
On Error GoTo 0

If lRow < 0 Then
Sheet2.Activate
Sheet2.Cells(lRow, 1).Select
End If

End Sub


"Maria" wrote:

Hello!

I am trying to do the following: The user chooses a cell
via an InputBox (the cell contains a date); then the macro
needs to switch to another sheet an find this value/the
date in Column "A" and activate it. I just don't know how
to adress it ... Can anybody help?

Thanks,

Maria

Find and adress cell
 

Many thanks, it worked out!

Maria


-----Original Message-----
Hi Maria,

Try this

Dim ans
Dim c As Range

ans = InputBox("Input date")
If Not IsDate(ans) Then
MsgBox "Invalid date"
Exit Sub
Else
Worksheets("Sheet2").Activate
Set c = Columns(1).Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
c.Activate
End If
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Maria" wrote in
message
...
Hello!

I am trying to do the following: The user chooses a cell
via an InputBox (the cell contains a date); then the
macro
needs to switch to another sheet an find this value/the
date in Column "A" and activate it. I just don't know
how
to adress it ... Can anybody help?

Thanks,

Maria


.

VBA: Find and adress cell
 

Many thanks for your help!

-----Original Message-----
Maria,
You could pass the date received from the message box to
the following sub.
This assumes that the Programmatic name of the sheet you
have the list on is
Sheet2 and that the range is from A1 to A100. Modify the
code appropriately.

Sub Switch(ByVal d As Double)

Dim lRow&

On Error Resume Next
lRow = WorksheetFunction.Match(d, Sheet2.Range
("A1:A100"), 0)
On Error GoTo 0

If lRow < 0 Then
Sheet2.Activate
Sheet2.Cells(lRow, 1).Select
End If

End Sub


"Maria" wrote:

Hello!

I am trying to do the following: The user chooses a
cell
via an InputBox (the cell contains a date); then the
macro
needs to switch to another sheet an find this value/the
date in Column "A" and activate it. I just don't know
how
to adress it ... Can anybody help?

Thanks,

Maria

.