Finding 1st empty cell in range

Sam261 · Aug 13th 2021

I am running the following code and for some reason it is finding the 1st empty cell in a completely different range. For example, it goes to the 1st empty cell in the "AA19:AA24" range. I just ran it and it went to cell "AA20" because AA19 already had a value in it and every cell in the "AA11:AA17" range was empty. I'm sure it is something simple that I am missing but I just can't figure it out. Any ideas?

Code

Sub LookatU6()
'
' Macro1 Macro
'
Dim wksht1 As Worksheet
Dim u6 As Range
Set wksht1 = Worksheets("Nucomat_Dashboard")
Set u6 = wksht1.Range("AA11:AA19").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Offset(1, 0)
On Error Resume Next
With Sheets("Nucomat_Dashboard")
If Range("N14").Value = 0 Then
u6.Value = Now()
End If
End With


End Sub

Display More

jolivanes · Aug 14th 2021

If you change

Code

SearchDirection:=xlPrevious

to

Code

SearchDirection:=xlNext

Does that make a difference?

NoSparks · Aug 14th 2021

Adding to what jolivanes has indicated:

"*" will find first non blank cell

"" will find first blank cell

But range.Find is a circular function that begins looking after the first cell in the stated range and circles back around to first cell, it being the last cell checked.

So if the first cell in the range may be the first blank, but not the only blank, you'll need to specify the after cell.

try

Code

Set u6 = wksht1.Range("AA11:AA19").Find(What:="", After:=wksht1.Range("AA19"), SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues)

Finding 1st empty cell in range

Participate now!

Share