Using code to mimic a find and replace
 

Hi,
I have two worksheets, one holds data horizontally for course codes,
vertivcally for peoples names, the cells have a y or N to indicate if the
course is requred, the second sheet holds the same data drawn from a database
with the addition of a date (i.e if the peson has attended the course) This
first sheet is regularly refreshed.

I would like to be able to take the date from the first sheet and find the
same person and code on the second sheet and replace if a y is present with
the date, if the y is not present then replace with the date and change the
cell colour.

Using code to mimic a find and replace
 

Off the top, untested

With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
iLastCol = .Cells(1,.Columns.Count).End(xlToLeft).Column
For Each cell In .Range("B2",.Cells(iLastRow, iLastCol)
If IsDate(cell.Value) Then
Worksheets("Sheet1").Range(cell.Address).Vaue = _
cell.Value
End If
Next cell

--

HTH

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


"ST" wrote in message
...
Hi,
I have two worksheets, one holds data horizontally for course codes,
vertivcally for peoples names, the cells have a y or N to indicate if the
course is requred, the second sheet holds the same data drawn from a
database
with the addition of a date (i.e if the peson has attended the course)
This
first sheet is regularly refreshed.

I would like to be able to take the date from the first sheet and find the
same person and code on the second sheet and replace if a y is present
with
the date, if the y is not present then replace with the date and change
the
cell colour.

Using code to mimic a find and replace
 

Bob, Thanks I am getting a syntax error on the following line, any clues

For Each cell In .Range("B2",.Cells(iLastRow, iLastCol)

"Bob Phillips" wrote:

Off the top, untested

With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
iLastCol = .Cells(1,.Columns.Count).End(xlToLeft).Column
For Each cell In .Range("B2",.Cells(iLastRow, iLastCol)
If IsDate(cell.Value) Then
Worksheets("Sheet1").Range(cell.Address).Vaue = _
cell.Value
End If
Next cell

--

HTH

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


"ST" wrote in message
...
Hi,
I have two worksheets, one holds data horizontally for course codes,
vertivcally for peoples names, the cells have a y or N to indicate if the
course is requred, the second sheet holds the same data drawn from a
database
with the addition of a date (i.e if the peson has attended the course)
This
first sheet is regularly refreshed.

I would like to be able to take the date from the first sheet and find the
same person and code on the second sheet and replace if a y is present
with
the date, if the y is not present then replace with the date and change
the
cell colour.

Using code to mimic a find and replace
 

Missing parentheses at end...
For Each cell In .Range("B2",.Cells(iLastRow, iLastCol))

Mike F

"ST" wrote in message
...
Bob, Thanks I am getting a syntax error on the following line, any clues

For Each cell In .Range("B2",.Cells(iLastRow, iLastCol)

"Bob Phillips" wrote:

Off the top, untested

With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
iLastCol = .Cells(1,.Columns.Count).End(xlToLeft).Column
For Each cell In .Range("B2",.Cells(iLastRow, iLastCol)
If IsDate(cell.Value) Then
Worksheets("Sheet1").Range(cell.Address).Vaue = _
cell.Value
End If
Next cell

--

HTH

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


"ST" wrote in message
...
Hi,
I have two worksheets, one holds data horizontally for course codes,
vertivcally for peoples names, the cells have a y or N to indicate if
the
course is requred, the second sheet holds the same data drawn from a
database
with the addition of a date (i.e if the peson has attended the course)
This
first sheet is regularly refreshed.

I would like to be able to take the date from the first sheet and find
the
same person and code on the second sheet and replace if a y is present
with
the date, if the y is not present then replace with the date and
change
the
cell colour.