updating a workbook
 

Hi i am trying to use some visual basic code to update
everybodys results depending on there score e.g. if the
new value is greater than there current value then it will
put "Fail" in there record.

here is the code i have tried so far!

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
End With

For Each cell In rng
If cell.Offset(0, 26).Value Sheets
("sheet1").Range("AU1").Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value Sheets
("sheet1").Range("AV1").Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value Sheets
("sheet1").Range("AW1").Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next

Thank you,

Robert Couchman

updating a workbook
 

The code would appears like it would work. It compiles with no complaints.
What is the issue. do you want to check against a value in the same row in
columns AU, AV, AW?

Sub Tester1()
Dim rng as Range, cell as Range, i as long
With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), _
.Cells(2, 1).End(xlDown))
End With
i = 0
For Each cell In rng
If cell.Offset(0, 26).Value _
Sheets("sheet1").Range("AU2").Offset(i,0).Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value _
Sheets("sheet1").Range("AV2").Offset(i,0).Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value _
Sheets("sheet1").Range("AW2").Offset(i,0).Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
i = i + 1
Next
End Sub


--
Regards,
Tom Ogilvy


"Robert Couchman" wrote in message
...
Hi i am trying to use some visual basic code to update
everybodys results depending on there score e.g. if the
new value is greater than there current value then it will
put "Fail" in there record.

here is the code i have tried so far!

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
End With

For Each cell In rng
If cell.Offset(0, 26).Value Sheets
("sheet1").Range("AU1").Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value Sheets
("sheet1").Range("AV1").Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value Sheets
("sheet1").Range("AW1").Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next

Thank you,

Robert Couchman

updating a workbook
 

Robert,
It would help if you explained wht you expected to happen and what was
happening, rather than leave it to us to figure out. I was just about to
respond that I didn';t know what you wanted, wehen I spotted that the test
cell wasn't moving.

Is this what you want?

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
rng.Select

For Each cell In rng
If cell.Offset(0, 26).Value .Cells(cell.Row, "AU").Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value .Cells(cell.Row, "AV").Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value .Cells(cell.Row, "AW").Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Hi i am trying to use some visual basic code to update
everybodys results depending on there score e.g. if the
new value is greater than there current value then it will
put "Fail" in there record.

here is the code i have tried so far!

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
End With

For Each cell In rng
If cell.Offset(0, 26).Value Sheets
("sheet1").Range("AU1").Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value Sheets
("sheet1").Range("AV1").Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value Sheets
("sheet1").Range("AW1").Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next

Thank you,

Robert Couchman

updating a workbook
 

Can also be written more succinctly as

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
rng.Select

For Each cell In rng
cell.Offset(0, 27) = IIf(cell.Offset(0, 26).Value
..Cells(cell.Row, "AU").Value, _
"Fail", "Pass")
cell.Offset(0, 37) = IIf(cell.Offset(0, 36).Value
..Cells(cell.Row, "AV").Value, _
"Fail", "Pass")
cell.Offset(0, 45) = IIf(cell.Offset(0, 44).Value
..Cells(cell.Row, "AW").Value, _
"Fail", "Pass")
Next
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Robert,
It would help if you explained wht you expected to happen and what was
happening, rather than leave it to us to figure out. I was just about to
respond that I didn';t know what you wanted, wehen I spotted that the test
cell wasn't moving.

Is this what you want?

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
rng.Select

For Each cell In rng
If cell.Offset(0, 26).Value .Cells(cell.Row, "AU").Value
Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value .Cells(cell.Row, "AV").Value
Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value .Cells(cell.Row, "AW").Value
Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Hi i am trying to use some visual basic code to update
everybodys results depending on there score e.g. if the
new value is greater than there current value then it will
put "Fail" in there record.

here is the code i have tried so far!

With Worksheets("sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End
(xlDown))
End With

For Each cell In rng
If cell.Offset(0, 26).Value Sheets
("sheet1").Range("AU1").Value Then
cell.Offset(0, 27) = "Fail"
Else
cell.Offset(0, 27) = "Pass"
End If
If cell.Offset(0, 36).Value Sheets
("sheet1").Range("AV1").Value Then
cell.Offset(0, 37) = "Fail"
Else
cell.Offset(0, 37) = "Pass"
End If
If cell.Offset(0, 44).Value Sheets
("sheet1").Range("AW1").Value Then
cell.Offset(0, 45) = "Fail"
Else
cell.Offset(0, 45) = "Pass"
End If
Next

Thank you,

Robert Couchman