How to create a message box depending on values of the cell? Toughie =/

[truzilla]

Hi everyone! hope you all are doing well...

So i've been working at this problem for awhile and can't seems to get anywhere, I know some of you will get it quickly, but either way I appreciate you all for your help!

Problem:
So I need a macro that will..
1) Search column D and find the rows that have the word "Check" in them
2) Then, search those rows (specifically to the RIGHT of Column D) and if they contain a number value that does NOT equal 0 (less than or greater than ZERO) have a message box that says: "Row __ Column __ does not equal Zero"
3) If there are more than one then multiple boxes could come up?

It seems like an impossible task to me, but I was definitely hoping someone could at least get me started. I appreciate all your help again and please let me know if i need to clarify! THANK YOU!

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Value = "Check" Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = .Cells(i, TEST_COLUMN).Column + 1 To LastCol

If .Cells(i, j).Value <> 0 Then

msg = msg & vbTab & .Cells(i, j).Address(False, False) & vbNewLine
End If
Next j
End If
Next i

If msg <> "" Then

MsgBox "The following cells have been identified: " & vbNewLine & msg
End If

End With

End Sub
[/vba]

[truzilla]

thanks xld...but for some reason nothing shows up. I tested it with my file which has -1 and 1 in those rows with "Check" but there was no message box. What could be the problem?

[Cosmo]

Your original post mentioned that the cells will have 'Check'. If they have either '-1' or '1' then you will have to change the line:
[vba]If .Cells(i, TEST_COLUMN).Value = "Check" Then[/vba]
accordingly. Something like this should work if you have a zero in the cells that are 'unchecked'
[vba]If .Cells(i, TEST_COLUMN).Value <>0 Then[/vba]

[truzilla]

thanks for the help, but I think I need to clarify a little.
So here is what the excel looks like:
A B C D E F G H I
1 ___xxx
2 ___xxx
3 ___Check 0 0 0 1 -1
4 ___xxx
5 ___xxx

So i need the message box to locate the cells that are in the ROWS with "Check" in column D that do not equal 0.

Thanks!

[Bob Phillips]

When in doubt ... post your workbook.

[RonMcK]

Truzilla,

Looking at your data I have a question. Is 'check' in col B or col D? Your spec calls for "D" but your sample data shows it in "B".

When I change 'TEST_COLUMN' = "B" and run it against your data, xld's code tells me that cells F4 and G4 are identified. It's working as expected.

Added thought: If those underlines are supposed to represent 2 blank fields, it might be better to use ' bbb ccc ' on each line instead of the underline so the data will import (text to columns) and put 'check' in column 'D".


Thanks,

[truzilla]

Its in D, because the spacing got shifted after posting.

I've posted my workbook, with the names changed for security reasons.

So as you can see, the "Check" is in column D, and there are values to the right of it, some zero, some not. I need the box to locate where they are not equal to zero. Thanks!

[Bob Phillips]

Small tweak

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If LCase(.Cells(i, TEST_COLUMN).Value) = "check" Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = .Cells(i, TEST_COLUMN).Column + 1 To LastCol

If .Cells(i, j).Value <> 0 Then

msg = msg & vbTab & .Cells(i, j).Address(False, False) & vbNewLine
End If
Next j
End If
Next i

If msg <> "" Then

MsgBox "The following cells have been identified: " & vbNewLine & msg
End If

End With

End Sub
[/vba]

[truzilla]

that worked well, is there a way to omit row E, so that it deoesn't show up in the message box? THANKS!!!

[Bob Phillips]

Yeah, change

[vba]
For j = .Cells(i, TEST_COLUMN).Column + 1 To LastCol
[/vba]

to

[vba]
For j = .Cells(i, TEST_COLUMN).Column + 2 To LastCol
[/vba]