Prev: NetDDE
Next: VBA source or reference
From: Harn88 on 28 Apr 2010 02:00
Hello All VB expert

Need help with a Macro.

I would like to be able to select a cell according to a variable in a cell.
For example: Cell A1 “31/04/2010”.

Cell A3 = 31/05/2010
Cell A4 = 30/06/2010
Cell A5 = 31/07/2010
Cell A6 = 31/04/2010

In this case when I run the Marco, it will select Cell A6 because it is the
same value to Cell A1.

And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the
same value to Cell A1

If you need more information or my explanation is not clear please let me
know.

Thank you very much for your help.

Harn


From: Jacob Skaria on 28 Apr 2010 02:12
Try the below

Cells.Find(What:=Range("A1").Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Activate

PS: Try recording a macro and edit to suit...

--
Jacob (MVP - Excel)


"Harn88" wrote:

> Hello All VB expert
>
> Need help with a Macro.
>
> I would like to be able to select a cell according to a variable in a cell.
> For example: Cell A1 “31/04/2010”.
>
> Cell A3 = 31/05/2010
> Cell A4 = 30/06/2010
> Cell A5 = 31/07/2010
> Cell A6 = 31/04/2010
>
> In this case when I run the Marco, it will select Cell A6 because it is the
> same value to Cell A1.
>
> And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the
> same value to Cell A1
>
> If you need more information or my explanation is not clear please let me
> know.
>
> Thank you very much for your help.
>
> Harn
>
>
From: ozgrid.com on 28 Apr 2010 02:11
See this page
http://www.ozgrid.com/VBA/find-dates.htm

Sub FindDate()
Dim strdate As String

strdate = Format(Range("A1"), "Short Date")

On Error Resume Next
Set rcell = Range("A2:A10000").Find _
(What:=CDate(strdate), After:=Range("A2"), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False)
On Error GoTo 0
If rcell Is Nothing Then
MsgBox "Date cannot be found"
Else
Application.Goto rcell

End If

End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Harn88" <Harn88(a)discussions.microsoft.com> wrote in message
news:70E1689C-6C34-4A6E-A9B1-0B0AED98AF61(a)microsoft.com...
> Hello All VB expert
>
> Need help with a Macro.
>
> I would like to be able to select a cell according to a variable in a
> cell.
> For example: Cell A1 “31/04/2010”.
>
> Cell A3 = 31/05/2010
> Cell A4 = 30/06/2010
> Cell A5 = 31/07/2010
> Cell A6 = 31/04/2010
>
> In this case when I run the Marco, it will select Cell A6 because it is
> the
> same value to Cell A1.
>
> And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the
> same value to Cell A1
>
> If you need more information or my explanation is not clear please let me
> know.
>
> Thank you very much for your help.
>
> Harn
>
>

 | 
Pages: 1
Prev: NetDDE
Next: VBA source or reference