code to check condition for each row
 

I am trying to write a macro that will check the last cell in each row of a
range against a condition. If the condition is met, then the value of the
first cell in the row is added to a string. The string will be used for a
message box.

The range("A3:P42") has names in the first column, data in the middle
columns, and totals in the last column. I'm trying to use a for-next loop,
but I'm stuck on how to refer to the correct cell. This is what I have tried:

Private Sub cmdErrorCheck_Click()
Dim e As String

For Each .Row In Me.Range("A3:P42")
If [the last cell].Value 4 Then
e = e & [the first cell].Value
End If
Next .Row

MsgBox "There is an error on these people: " & e

End Sub

code to check condition for each row
 

try this idea

for i=3 to 42
if cells(i,"p")4 then msgbox "error on "&cells(i,"a")
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Horatio J. Bilge, Jr." wrote in
message ...
I am trying to write a macro that will check the last cell in each row of a
range against a condition. If the condition is met, then the value of the
first cell in the row is added to a string. The string will be used for a
message box.

The range("A3:P42") has names in the first column, data in the middle
columns, and totals in the last column. I'm trying to use a for-next loop,
but I'm stuck on how to refer to the correct cell. This is what I have
tried:

Private Sub cmdErrorCheck_Click()
Dim e As String

For Each .Row In Me.Range("A3:P42")
If [the last cell].Value 4 Then
e = e & [the first cell].Value
End If
Next .Row

MsgBox "There is an error on these people: " & e

End Sub

code to check condition for each row
 

Thank you for the idea - it works great. This is the final code I used:

Private Sub cmdErrorCheck_Click()
Dim m As String
Dim i As Integer

For i = 3 To 42
If Cells(i, "P").Value 4 Then
m = m & vbLf & Cells(i, "A").Value
ElseIf Cells(i, "O").Value 3 Then
m = m & vbLf & Cells(i, "A").Value
End If
Next i

MsgBox "There is an error these people: " & m
End Sub


"Don Guillett" wrote:

try this idea

for i=3 to 42
if cells(i,"p")4 then msgbox "error on "&cells(i,"a")
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Horatio J. Bilge, Jr." wrote in
message ...
I am trying to write a macro that will check the last cell in each row of a
range against a condition. If the condition is met, then the value of the
first cell in the row is added to a string. The string will be used for a
message box.

The range("A3:P42") has names in the first column, data in the middle
columns, and totals in the last column. I'm trying to use a for-next loop,
but I'm stuck on how to refer to the correct cell. This is what I have
tried:

Private Sub cmdErrorCheck_Click()
Dim e As String

For Each .Row In Me.Range("A3:P42")
If [the last cell].Value 4 Then
e = e & [the first cell].Value
End If
Next .Row

MsgBox "There is an error on these people: " & e

End Sub

code to check condition for each row
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Horatio J. Bilge, Jr." wrote in
message ...
Thank you for the idea - it works great. This is the final code I used:

Private Sub cmdErrorCheck_Click()
Dim m As String
Dim i As Integer

For i = 3 To 42
If Cells(i, "P").Value 4 Then
m = m & vbLf & Cells(i, "A").Value
ElseIf Cells(i, "O").Value 3 Then
m = m & vbLf & Cells(i, "A").Value
End If
Next i

MsgBox "There is an error these people: " & m
End Sub


"Don Guillett" wrote:

try this idea

for i=3 to 42
if cells(i,"p")4 then msgbox "error on "&cells(i,"a")
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Horatio J. Bilge, Jr." wrote
in
message ...
I am trying to write a macro that will check the last cell in each row
of a
range against a condition. If the condition is met, then the value of
the
first cell in the row is added to a string. The string will be used for
a
message box.

The range("A3:P42") has names in the first column, data in the middle
columns, and totals in the last column. I'm trying to use a for-next
loop,
but I'm stuck on how to refer to the correct cell. This is what I have
tried:

Private Sub cmdErrorCheck_Click()
Dim e As String

For Each .Row In Me.Range("A3:P42")
If [the last cell].Value 4 Then
e = e & [the first cell].Value
End If
Next .Row

MsgBox "There is an error on these people: " & e

End Sub