I am trying to create a macro that will find the word "DATE" in column A,
then copy the date in the cell adjacent to it in the same row to cell D2.
Can someone help me with this, I've looked for roughly 2 hours on how to
accomplish and haven't gained any ground....Thanks for your help!!!

Tasha
This macro will find the first instance of "Date" in Column A and will place
the contents of Column B of the same row, into cell D2. Watch out for word
wrap. This macro has only one line besides the Sub and End Sub lines. HTH
Otto
Sub GetDate()
Range("D2").Value = Columns("A:A").Find(What:="Date",
LookAt:=xlWhole).Offset(, 1).Value
End Sub
"Tasha" wrote in message
...
I am trying to create a macro that will find the word "DATE" in column A,
then copy the date in the cell adjacent to it in the same row to cell D2.
Can someone help me with this, I've looked for roughly 2 hours on how to
accomplish and haven't gained any ground....Thanks for your help!!!

Hi Tasha

You can try this one for a sheet named "Sheet1"

Sub Find_First()
Dim FindString As String
Dim rng As Range
FindString = "DATE"
If Trim(FindString) < "" Then
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy rng.Parent.Range("D2")
Else
MsgBox "Nothing found"

End If
End With
End If
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tasha" wrote in message ...
I am trying to create a macro that will find the word "DATE" in column A,
then copy the date in the cell adjacent to it in the same row to cell D2.
Can someone help me with this, I've looked for roughly 2 hours on how to
accomplish and haven't gained any ground....Thanks for your help!!!

You can try this:

Range("A2").Select
Do While ActiveCell.Value < ""
If ActiveCell.Value = "DATE" Then
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 1).Value
End If
ActiveCell.Offset(1, 0).Select
Loop

I'm assuming it's column B which contains the date you want to copy.

The syntax of Activecell.Offset is "row number, column number" so you can
adjust those if need be. 0 = the row or column of the active cell. Positive
values are to the right for columns and down for rows, negative values to the
left for columns and up for rows. The other thing this code assumes is that
you have no blank values in column A. If you do you could change the first
two lines of code to:

Range("A1000").select (or some greater number if you have more rows)
Do While Activecell.Row 1
and the second to last line of code to
ActiveCell.Offset(-1, 0).Select
which would move the cursor up instead of down after each test.

There are prettier ways of coding this, but the way I've shown may be easier
to understand than the more elegant methods.

Hope this helps,

Keith

"Tasha" wrote:

I am trying to create a macro that will find the word "DATE" in column A,
then copy the date in the cell adjacent to it in the same row to cell D2.
Can someone help me with this, I've looked for roughly 2 hours on how to
accomplish and haven't gained any ground....Thanks for your help!!!

worked like a charm!! Thanks so much Otto!!!!

"Otto Moehrbach" wrote:

Tasha
This macro will find the first instance of "Date" in Column A and will place
the contents of Column B of the same row, into cell D2. Watch out for word
wrap. This macro has only one line besides the Sub and End Sub lines. HTH
Otto
Sub GetDate()
Range("D2").Value = Columns("A:A").Find(What:="Date",
LookAt:=xlWhole).Offset(, 1).Value
End Sub
"Tasha" wrote in message
...
I am trying to create a macro that will find the word "DATE" in column A,
then copy the date in the cell adjacent to it in the same row to cell D2.
Can someone help me with this, I've looked for roughly 2 hours on how to
accomplish and haven't gained any ground....Thanks for your help!!!