Need help with Display Room if Across cell does not contain date

[yangc10]

Can be macro or formula; which ever works. I would prefer macro if possible.

So I have all my rooms at column A and All Dates on Column B.

On D4, I want to display the first cell reading down if column B of it does not contain a date.

Example:

A1= 10    B1= 6/1/2015
A2= 11    B2= 
A3= 12    B3= 
A4= 13    B4= 6/1/2015

So D4 or textbox (if used macro) would populate 11 because B2 = Nothing. If B2 = a date then D4 or textbox = 12.

Hope this make sense.

thanks,

 

[JoeMo]

Try this:

Sub FirstEmptyB()
On Error Resume Next
Range("D4").Value = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)(1).Offset(0, -1).Value
If Err.Number <> 0 Then MsgBox "No empty cells found in used range of column B"
On Error GoTo 0
End Sub

 

[yangc10]

Tested and nothing show-up on D4. Only Error saying B column isn't empty... B aren't all empty in my case.

Secondly, I forgot to mention that D4 is on sheet 1 and the list of rooms with date is on sheet2.

 

[JoeMo]

Tested and nothing show-up on D4. Only Error saying B column isn't empty... B aren't all empty in my case.

Secondly, I forgot to mention that D4 is on sheet 1 and the list of rooms with date is on sheet2.

The code I posted is intended to return a value to D4 on the same sheet the B column is on - and it does that for me. It's also intended to be run with the sheet that holds the data as the active sheet - my guess is you are running it from some other sheet.

 

[yangc10]

The code I posted is intended to return a value to D4 on the same sheet the B column is on - and it does that for me. It's also intended to be run with the sheet that holds the data as the active sheet - my guess is you are running it from some other sheet.

so how do you make it work with different sheets?

by adding activeworkbook.sheets("Sheet1") and activeworkbook.sheets("Sheet2"); it works if I start excel as new, but adding it to existen... it doesn't work.

 

[JoeMo]

so how do you make it work with different sheets?

by adding activeworkbook.sheets("Sheet1") and activeworkbook.sheets("Sheet2"); it works if I start excel as new, but adding it to existen... it doesn't work.

Sub FirstEmptyB()
On Error Resume Next
Sheets("Sheet1").Range("D4").Value = Sheets("Sheet2").Range("B1:B" & _
    Sheets("Sheet2").Cells(Rows.Count, _
    "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)(1).Offset(0, -1).Value
If Err.Number <> 0 Then MsgBox "No empty cells found in used range of column B"
On Error GoTo 0
End Sub

 

[yangc10]

thanks, it works.