jotting down the blank row number

[daveyc18]

sor right now i have a loop that shows a message box saying if there is a number in column K, but column Q is blank, it'll display the blank row number for the analyst to fill in the blank in column Q with the necessary info.

however, rather than having the analyst jot down the row number, i want the macro to put the row number in column AA and of course build on the list if there is more than one row where the condition is true (i.e., number is column K, but column Q is blank)

 

[Joe4]

Note that I had a typo and went back and edited my code, as I had "finalrow" where I should have had "c".

Note that by definition:

Range("q" & c).Row

is exactly the same as

c

(and HAS to be, by definition).

 

[daveyc18]

Here is one way:

    For c = 9 To finalrow
        If Range("O" & c).DisplayFormat.Interior.Color = 65535 And Range("Q" & c) = "" And Application.WorksheetFunction.IsNumber(Range("O" & c)) Then
            MsgBox "There is a blank contact column on row " & c
            r = r + 1
            Range("AA" & r).Value = c
        End If
    Next c

Note that "Range("q" & c).Row" can simply be replaced with "c".
No need to take the scenic round-about route back to where you started from!

thanks, it works, but is there is a way so that it starts from cell AA2? right now it's starting from AA1

 

[Joe4]

Sorry, I thought you wanted it to start in cell AA1.

Try this:

    For c = 9 To finalrow
        If Range("O" & c).DisplayFormat.Interior.Color = 65535 And Range("Q" & c) = "" And Application.WorksheetFunction.IsNumber(Range("o" & c)) Then
            MsgBox "There is a blank contact column on row " & c
            Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).Value = c
        End If
    Next c

 

[Micron]

Why not just start r as 1+1 as I suggested wrt i?

 

[daveyc18]

Sorry, I thought you wanted it to start in cell AA1.

Try this:

    For c = 9 To finalrow
        If Range("O" & c).DisplayFormat.Interior.Color = 65535 And Range("Q" & c) = "" And Application.WorksheetFunction.IsNumber(Range("o" & c)) Then
            MsgBox "There is a blank contact column on row " & c
            Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).Value = c
        End If
    Next c

perfect, ty!

 

[Joe4]

You are welcome.
Glad we were able to help!