Find and output empty cells-CodePudding

I have a table with 3 columns. Column A contains over 6,000 entries for 49 different cities, column B contains the assignments of the cities to letters. In other words, any "City" can be linked to any "Department". Column C contains a date. However, there are cells in column C where the date is missing that I would like to find and output to a spreadsheet. The line number outputting the combination of city (A) & department (B) is required.

Unfortunately I don't have much experience and so far I've only fabricated this one. I would be happy if someone can help me here.

Option Explicit

Sub CreatedDate()
Dim i As Integer
For i = 2 To 22
  If Cells(i, 1).Value = "Panama" And Cells(i, 2).Value = "F" Then
     Cells(i, 3).Value = "No Date"
  End If
Next
End Sub

My attempt to search for all combinations failed:

Option Explicit

Sub CreatedDate()
Dim i As Integer
For i = 2 To 22
  If Cells(i, 1).Value = "Peking" Or "Tokio" Or "London" Or "Rom" Or "Lissabon" Or "Panama" Or "Budapest" Or "Prag" Or "Dublin" Or "Luxemburg" And Cells(i, 2).Value = "A" Or "B" Or "C" Or "D" Or "E" Or "F" Or "G" Or "H" Or "I" Or "J" Then
  Cells(i, 3).Value = "No Date"
  End If
Next
End Sub

CodePudding user response：

while i was writing this others have answered and honestly I like there solution but can also be done like this:

Sub missing()
    Dim ws, wsOut As Worksheet
    Set ws = ActiveWorkbook.Sheets("table")
    Set wsOut = ActiveWorkbook.Sheets("output")
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    lastRowOut = wsOut.Range("A" & Rows.Count).End(xlUp).Row   1
    
    For i = 2 To lastRow
        If ws.Cells(i, 3).Value = "" Then
            wsOut.Range("A" & lastRowOut & ":B" & lastRowOut).Value = ws.Range("A" & i & ":B" & i).Value
            wsOut.Range("C" & lastRowOut).Value = i
            lastRowOut = lastRowOut   1
        End If
    Next i
End Sub

assuming table is in worksheet "table" and output is wanted in a worksheet called "output" [note output has to have a value somewhere in column A before the code is run or an error will be thrown]

Also the code you show does not appear to be trying to answer the question you've asked, it may just be that you took a wrong turn but it is quite different, let us know if we've all missed the point!

CodePudding user response：

Click on cell "A1", press Ctrl G and choose "Special", "current region" (that should select the whole array). Again press Ctrl G and choose "Special", this type choose "Blanks".
In the address bar, type "No Date".
Press Ctrl ENTER (don't forget the control-button).

You can record this into a macro.

Have fun :-)

Oh, by the way, this is wrong:

If Cells(i, 1).Value = "Peking" Or "Tokio" Or "London" Or ...

It should be something like:

If Cells(i, 1).Value = "Peking" Or_
   Cells(i, 1).Value = "Tokio" Or_
   ...

(The underscore after "Or" is just to explain VBA that this should be treated as one single line.)

CodePudding user response：

Not sure i'm 100% with you, but

Dim r as range
dim c as range
dim a() as variant
dim i as long

set r=range("c2:c22").specialcells(xlcelltypeblanks)
redim a(1 to r.cells.count,1)
i=1

for each c in r.cells
    a(i,0) = cells(c.row,1)
    a(i,1)=cells(c.row,2)
    i=i 1
next c

' Output, to j1 on the same sheet.

cells(1,10).resize(ubound(a),2).value=a