Find date in row and move to cell

I will try and explain this as best as I can but please forgive me if I fail.

I have a date range in row 2 starting in column e and runing through to cell 
IR2. I then have a list of tasks in column a staring in row 4.

There is a command button linked to a macro that will insert a new task in 
the middle of the existing list of tasks and creates some subtotals etc but 
what I would like it also to do is remember the row the active cell is in, 
look up todays date in the range and move to that location ready for input.

Any ideas please?

Kind regards,
Mav
0
Utf
4/28/2010 10:39:01 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
753 Views

Similar Articles

[PageSpeed] 2

Try the below

Sub Macro()
Dim lngCol As Long
'your other code
lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole).Column
Application.Goto Cells(2, lngCol), True
End Sub

-- 
Jacob (MVP - Excel)


"Maver1ck666" wrote:

> I will try and explain this as best as I can but please forgive me if I fail.
> 
> I have a date range in row 2 starting in column e and runing through to cell 
> IR2. I then have a list of tasks in column a staring in row 4.
> 
> There is a command button linked to a macro that will insert a new task in 
> the middle of the existing list of tasks and creates some subtotals etc but 
> what I would like it also to do is remember the row the active cell is in, 
> look up todays date in the range and move to that location ready for input.
> 
> Any ideas please?
> 
> Kind regards,
> Mav
0
Utf
4/28/2010 11:12:01 AM
Oops...I missed the activecell Row....Try the modified one..

Sub Macro()
Dim lngRow As Long, lngCol As Long
lngRow = ActiveCell.Row

'your other code here

lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole).Column
Cells(lngRow, lngCol).Activate
End Sub

-- 
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

> Try the below
> 
> Sub Macro()
> Dim lngCol As Long
> 'your other code
> lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
> LookIn:=xlFormulas, LookAt:=xlWhole).Column
> Application.Goto Cells(2, lngCol), True
> End Sub
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Maver1ck666" wrote:
> 
> > I will try and explain this as best as I can but please forgive me if I fail.
> > 
> > I have a date range in row 2 starting in column e and runing through to cell 
> > IR2. I then have a list of tasks in column a staring in row 4.
> > 
> > There is a command button linked to a macro that will insert a new task in 
> > the middle of the existing list of tasks and creates some subtotals etc but 
> > what I would like it also to do is remember the row the active cell is in, 
> > look up todays date in the range and move to that location ready for input.
> > 
> > Any ideas please?
> > 
> > Kind regards,
> > Mav
0
Utf
4/28/2010 11:16:02 AM
Excellent, cheers for that Jacob!

"Jacob Skaria" wrote:

> Oops...I missed the activecell Row....Try the modified one..
> 
> Sub Macro()
> Dim lngRow As Long, lngCol As Long
> lngRow = ActiveCell.Row
> 
> 'your other code here
> 
> lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
> LookIn:=xlFormulas, LookAt:=xlWhole).Column
> Cells(lngRow, lngCol).Activate
> End Sub
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Jacob Skaria" wrote:
> 
> > Try the below
> > 
> > Sub Macro()
> > Dim lngCol As Long
> > 'your other code
> > lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
> > LookIn:=xlFormulas, LookAt:=xlWhole).Column
> > Application.Goto Cells(2, lngCol), True
> > End Sub
> > 
> > -- 
> > Jacob (MVP - Excel)
> > 
> > 
> > "Maver1ck666" wrote:
> > 
> > > I will try and explain this as best as I can but please forgive me if I fail.
> > > 
> > > I have a date range in row 2 starting in column e and runing through to cell 
> > > IR2. I then have a list of tasks in column a staring in row 4.
> > > 
> > > There is a command button linked to a macro that will insert a new task in 
> > > the middle of the existing list of tasks and creates some subtotals etc but 
> > > what I would like it also to do is remember the row the active cell is in, 
> > > look up todays date in the range and move to that location ready for input.
> > > 
> > > Any ideas please?
> > > 
> > > Kind regards,
> > > Mav
0
Utf
4/28/2010 12:09:01 PM
I just noticed; I have hardcoded the date...You can try the below..

Sub Macro()
Dim lngRow As Long, lngCol As Long
lngRow = ActiveCell.Row

'your other code here

lngCol = Cells.Find(What:=Date, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole).Column
Cells(lngRow, lngCol).Activate
End Sub

-- 
Jacob (MVP - Excel)


"Maver1ck666" wrote:

> Excellent, cheers for that Jacob!
> 
> "Jacob Skaria" wrote:
> 
> > Oops...I missed the activecell Row....Try the modified one..
> > 
> > Sub Macro()
> > Dim lngRow As Long, lngCol As Long
> > lngRow = ActiveCell.Row
> > 
> > 'your other code here
> > 
> > lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
> > LookIn:=xlFormulas, LookAt:=xlWhole).Column
> > Cells(lngRow, lngCol).Activate
> > End Sub
> > 
> > -- 
> > Jacob (MVP - Excel)
> > 
> > 
> > "Jacob Skaria" wrote:
> > 
> > > Try the below
> > > 
> > > Sub Macro()
> > > Dim lngCol As Long
> > > 'your other code
> > > lngCol = Cells.Find(What:="4/28/2010", After:=ActiveCell, _
> > > LookIn:=xlFormulas, LookAt:=xlWhole).Column
> > > Application.Goto Cells(2, lngCol), True
> > > End Sub
> > > 
> > > -- 
> > > Jacob (MVP - Excel)
> > > 
> > > 
> > > "Maver1ck666" wrote:
> > > 
> > > > I will try and explain this as best as I can but please forgive me if I fail.
> > > > 
> > > > I have a date range in row 2 starting in column e and runing through to cell 
> > > > IR2. I then have a list of tasks in column a staring in row 4.
> > > > 
> > > > There is a command button linked to a macro that will insert a new task in 
> > > > the middle of the existing list of tasks and creates some subtotals etc but 
> > > > what I would like it also to do is remember the row the active cell is in, 
> > > > look up todays date in the range and move to that location ready for input.
> > > > 
> > > > Any ideas please?
> > > > 
> > > > Kind regards,
> > > > Mav
0
Utf
4/28/2010 12:14:01 PM
Reply:

Similar Artilces: