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!

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!

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!

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!

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!