| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code to compare/validate and delete as required
Hi
Is it possible to have some code (if so what) that will compare a name from cell C3 in sheet1 to a list of names held in sheet2 AB4:AB75 (note: it is important that the check is made from row 4 downwards for the name match and NOT row 75 upwards!!) If the name is found in range AB4:AB75 of sheet2 check the corresponding column AC (sheet2) and if it DOESN'T match that of 0445-1315 then paste the time value from the corresponding column AC into cell E3 of sheet1. Once pasted remove the name match from column AB of sheet2 Loop this until the range of C3:C6 in sheet1 has been completed Any ideas and thanks in advance! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code to compare/validate and delete as required
Dim iRow As Long Dim rng As Range Dim cell As Range With Worksheets("Sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Worksheets("Sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Worksheets("Sheet2").Cells(iRow + 3, "AC").Value = TimeSerial(4, 45, 0) And _ Worksheets("Sheet2").Cells(iRow + 3, "AC").Value <= TimeSerial(13, 15, 0) Then Worksheets("Sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Worksheets("Sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... Hi Is it possible to have some code (if so what) that will compare a name from cell C3 in sheet1 to a list of names held in sheet2 AB4:AB75 (note: it is important that the check is made from row 4 downwards for the name match and NOT row 75 upwards!!) If the name is found in range AB4:AB75 of sheet2 check the corresponding column AC (sheet2) and if it DOESN'T match that of 0445-1315 then paste the time value from the corresponding column AC into cell E3 of sheet1. Once pasted remove the name match from column AB of sheet2 Loop this until the range of C3:C6 in sheet1 has been completed Any ideas and thanks in advance! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code to compare/validate and delete as required
Bob,
I have slightly adjusted ur suggested code to this.. Dim iRow As Long Dim rng As Range Dim cell As Range With Sheets("sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Sheets("sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Sheets("sheet2").Cells(iRow + 3, "AC").Value < "4:45 - 13:15" Then Sheets("sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Sheets("sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With and it almost works... it copies each of the times found for the corresponding name matched, but I want it only to copy/paste the one(s) that DON'T MATCH the time "04:45 - 13:15" If there is a match DON'T copy/paste just leave the cell in sheet1 blank thanks and hope this makes sense "Bob Phillips" wrote: Dim iRow As Long Dim rng As Range Dim cell As Range With Worksheets("Sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Worksheets("Sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Worksheets("Sheet2").Cells(iRow + 3, "AC").Value = TimeSerial(4, 45, 0) And _ Worksheets("Sheet2").Cells(iRow + 3, "AC").Value <= TimeSerial(13, 15, 0) Then Worksheets("Sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Worksheets("Sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... Hi Is it possible to have some code (if so what) that will compare a name from cell C3 in sheet1 to a list of names held in sheet2 AB4:AB75 (note: it is important that the check is made from row 4 downwards for the name match and NOT row 75 upwards!!) If the name is found in range AB4:AB75 of sheet2 check the corresponding column AC (sheet2) and if it DOESN'T match that of 0445-1315 then paste the time value from the corresponding column AC into cell E3 of sheet1. Once pasted remove the name match from column AB of sheet2 Loop this until the range of C3:C6 in sheet1 has been completed Any ideas and thanks in advance! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code to compare/validate and delete as required
oops, I should have put 04:45 in the code and not 4:45
it now works !! sorry ! "Anthony" wrote: Bob, I have slightly adjusted ur suggested code to this.. Dim iRow As Long Dim rng As Range Dim cell As Range With Sheets("sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Sheets("sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Sheets("sheet2").Cells(iRow + 3, "AC").Value < "4:45 - 13:15" Then Sheets("sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Sheets("sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With and it almost works... it copies each of the times found for the corresponding name matched, but I want it only to copy/paste the one(s) that DON'T MATCH the time "04:45 - 13:15" If there is a match DON'T copy/paste just leave the cell in sheet1 blank thanks and hope this makes sense "Bob Phillips" wrote: Dim iRow As Long Dim rng As Range Dim cell As Range With Worksheets("Sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Worksheets("Sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Worksheets("Sheet2").Cells(iRow + 3, "AC").Value = TimeSerial(4, 45, 0) And _ Worksheets("Sheet2").Cells(iRow + 3, "AC").Value <= TimeSerial(13, 15, 0) Then Worksheets("Sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Worksheets("Sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... Hi Is it possible to have some code (if so what) that will compare a name from cell C3 in sheet1 to a list of names held in sheet2 AB4:AB75 (note: it is important that the check is made from row 4 downwards for the name match and NOT row 75 upwards!!) If the name is found in range AB4:AB75 of sheet2 check the corresponding column AC (sheet2) and if it DOESN'T match that of 0445-1315 then paste the time value from the corresponding column AC into cell E3 of sheet1. Once pasted remove the name match from column AB of sheet2 Loop this until the range of C3:C6 in sheet1 has been completed Any ideas and thanks in advance! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code to compare/validate and delete as required
Oh, I thought you meant that the time had to be between 4:45 and 13:15. Lol!
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... oops, I should have put 04:45 in the code and not 4:45 it now works !! sorry ! "Anthony" wrote: Bob, I have slightly adjusted ur suggested code to this.. Dim iRow As Long Dim rng As Range Dim cell As Range With Sheets("sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Sheets("sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Sheets("sheet2").Cells(iRow + 3, "AC").Value < "4:45 - 13:15" Then Sheets("sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Sheets("sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With and it almost works... it copies each of the times found for the corresponding name matched, but I want it only to copy/paste the one(s) that DON'T MATCH the time "04:45 - 13:15" If there is a match DON'T copy/paste just leave the cell in sheet1 blank thanks and hope this makes sense "Bob Phillips" wrote: Dim iRow As Long Dim rng As Range Dim cell As Range With Worksheets("Sheet1") For Each cell In .Range("C3:C6") iRow = 0 On Error Resume Next iRow = Application.Match(cell.Value, Worksheets("Sheet2").Range("AB4:AB75"), 0) On Error GoTo 0 If iRow < 0 Then If Worksheets("Sheet2").Cells(iRow + 3, "AC").Value = TimeSerial(4, 45, 0) And _ Worksheets("Sheet2").Cells(iRow + 3, "AC").Value <= TimeSerial(13, 15, 0) Then Worksheets("Sheet2").Cells(iRow + 3, "AC").Copy cell.Offset(0, 2) Worksheets("Sheet2").Cells(iRow + 3, "AB").Value = "" End If End If Next cell End With -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... Hi Is it possible to have some code (if so what) that will compare a name from cell C3 in sheet1 to a list of names held in sheet2 AB4:AB75 (note: it is important that the check is made from row 4 downwards for the name match and NOT row 75 upwards!!) If the name is found in range AB4:AB75 of sheet2 check the corresponding column AC (sheet2) and if it DOESN'T match that of 0445-1315 then paste the time value from the corresponding column AC into cell E3 of sheet1. Once pasted remove the name match from column AB of sheet2 Loop this until the range of C3:C6 in sheet1 has been completed Any ideas and thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB code required to compare name list | Excel Programming | |||
| VB code required to compare/delete data | Excel Programming | |||
| Using Vlookup to compare and validate data | Excel Worksheet Functions | |||
| validate data using code | Excel Programming | |||
| How to validate in code the contents of a cell? | Excel Programming | |||
Linear Mode
