Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tidy up multiple find and replace code | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Find and Replace code in Sheet modules | Excel Programming | |||
Code to mimic vlookup | Excel Programming | |||
[URGENT]How to mimic the toolbar zoom button from code | Excel Programming |