updating a workbook

[Robert Couchman]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)