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)

 

[Micron]

Post the code you have, then no one will have to guess what your sheet name(s) etc. are and where the code is located. The row number might just be the value of your loop counter - but can't see it so don't know.

and of course build on the list

What list? If row number is going into AA then there is no more list? Whatever you're doing when you have the info, would it not be easier if you just filtered the list to the rows where cell(s) are blank?
If posting code, please enclose code in code tags (use vba button on posting toolbar) to maintain readability.

 

[daveyc18]

Post the code you have, then no one will have to guess what your sheet name(s) etc. are and where the code is located. The row number might just be the value of your loop counter - but can't see it so don't know.

What list? If row number is going into AA then there is no more list? Whatever you're doing when you have the info, would it not be easier if you just filtered the list to the rows where cell(s) are blank?
If posting code, please enclose code in code tags (use vba button on posting toolbar) to maintain readability.

like row 43 meets that condition, then cell aa1 has "43"

if row 50 meets that condition, then cell aa2 has "50"

so now column AA has 2 rows of numbers ..that's what I meant by build on that list

 

[Micron]

Gotcha.
I take it that each time you run the code it is OK if the list changes in any fashion; i.e. values in AA1 and AA2 might get replaced, or if no rows satisfy the requirement, those values would be removed.

 

[daveyc18]

Gotcha.
I take it that each time you run the code it is OK if the list changes in any fashion; i.e. values in AA1 and AA2 might get replaced, or if no rows satisfy the requirement, those values would be removed.

correct ...it's a monthly report with new data so the number of rows meeting those conditions (if at all) and the actual row numbers will vary

 

[daveyc18]

just following up on this

 

[Joe4]

You still haven't posted your VBA code (like Micron asked).
If you post it, we can probably build on to it to do what you want.

 

[daveyc18]

You still haven't posted your VBA code (like Micron asked).
If you post it, we can probably build on to it to do what you want.

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 " & Range("q" & c).Row
End If

so, essentially, whatever row number appears, put that row number in cell AA@. if another row meets that condition, put that in cell AA3, etc.

 

[Joe4]

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!

 

[Micron]

Maybe

Dim i As Integer

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
      i = i + 1
      Range("AA" & i) = Range("q" & c).Row
    End If
Next

Adjust i if need to change start row.