Move through column until empty cell OR a date

[davedave24]

Hi

I have a calendar/diary page with the dates horizontally (Mon>Sun), then several rows underneath the following week, and so on (basic diary layout).

I need to find the specific date (got this part), then write data underneath it. The cell below can either be empty, or full. However, if all the cells in that current day are used, simply stepping down to find the next empty cell will put the data into the wrong date (the following week) which will be under it.

So I would need to step down until either an empty cell OR a date is found.
- If empty cell is hit before a date, write the data.
- If date cell his hit before empty cell, insert row above, write the data.

I have been trying out IsEmpty and IsDate, but several attempts have got stuck in a loop forever and crashed excel.

 

[LaurieHamlin]

If ActiveCell.Value >= 1 And <=#12/31/2199# Then
'stop
Else
ActiveCell.Offset(1, 0)
End If

The only way a loop will go forever is if you don't increment the criteria variable.
I hope that helps.

 

[davedave24]

Nevermind all I've actually solved it

Set rngFind = Cells.Find(What:=dDate, After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    If Not rngFind Is Nothing Then
                
        Application.Goto rngFind.Offset(1, 0), False
        Do Until IsEmpty(ActiveCell) Or IsDate(ActiveCell)
            ActiveCell.Offset(1, 0).Select
        Loop
                
        If IsEmpty(ActiveCell) Then
            ActiveCell = "ABC"
        ElseIf IsDate(ActiveCell) Then
            Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
            ActiveCell = "DEF"
        End If
    
    End If

 

[SkipVought]

So I'm making the following assumption: that doing a CurrentRegion in your table even will only select data for that week.

BTW I usually avoid Select like the plague...

With Selection
   For each r in Intersect(.CurrentRegion.Cells, .EntireColumn)
       If Len(r.Value) = 0 Then
         'Write the data here: r.Value = the data
       End If
   Next
End With