Find first blank cell in single column named range

[tig]

I've found some posts that cover something similar to what I'm trying
to do, but I've not been able to apply it to my situation. It maybe
just syntax.

I have a named range that covers A7-A16. I need to find the first
blank cell in that range. I tried to use:

Set rng = Range("AACash")
rng.Find("").Select

but I get a runtime 91 error: "Object variable or With block variable
not set".

Maybe I'm just missing something. Any ideas or alternatives to apply?

TIA

Here's my code so far:

Sub Asset_Allocation()

Dim lastrow&
Dim assettype$
Dim currentrow&
Dim acct_type1$
Dim acct_type2$
Dim assetname$
Dim amt#

Dim rng As Range
Dim aa_row1&
Dim aa_lastrow&

Sheets("Asset Detail").Select
'start in S10
'find last row of print area
lastrow = Range("Print_Area").Rows.Count

With ActiveSheet
Range("S10").Select
For x = 10 To lastrow
currentrow = ActiveCell.Row
assettype = ActiveCell.Value
If assettype = "" Then GoTo next_rec
acct_type1 = Range("AB" & currentrow).Value
acct_type2 = Range("AD" & currentrow).Value
assetname = Range("H" & currentrow).Value
amt = Range("J" & currentrow).Value

Sheets("Asset Allocation").Select
With ActiveSheet
Select Case assettype
Case "Cash"
Set rng = Range("AACash")
------> rng.Find("").Select


Case "Fixed Income"
Case "Large Cap"
Case "Mid Cap"
Case "Small Cap"
Case "Foreign"
Case "Company Stock"
Case "Real Estate"
Case "Alternative Investment"
Case Else
GoTo next_rec
End Select
End With
next_rec:
Next x
End With

End Sub

 

[Ron de Bruin]

Try this

Sub test()
Dim rng As Range
On Error GoTo BodemUp
Set rng = Range("AACash")
rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
Exit Sub
BodemUp: MsgBox "No empty cell"
End Sub

 

[Don Guillett]

You don't have blanks.
try " " instead of ""

 

[tig]

Ron,

Thank you for the idea. It seems like it should work, but I get
another error:

Runtime 1004 - No cells were found

Could this occur because I have formulas in those cells, even though
the result = ""?

Any ideas??

TIA

 

[Ron de Bruin]

Hi Tig

Reboot first and
Start>Run
Type: %temp%
OK
Clear all files in this Temp folder and also the sub folders

Try again

 

[tig]

Ron,

Still got the same 1004 error. Let me know if you have any other
ideas. Thanks for your help thus far.

 

[tig]

Don,

Thanks for the response. I tried your idea. Still got the runtime 91
error. It seems like it's something else in my logic. Can't put my
finger on it.

 

[Ron de Bruin]

Hi tig

Send me your test workbook private then i take a look at it

 

[Dave Peterson]

Dim FoundCell as range
dim rng as range

set rng = range("aacash")
set foundcell = rng.find("")
if foundcell is nothing then
'what should happen
msgbox "not found!
else
foundcell.select
end if

Remember that excel's and VBA's Find both remember the last parms you used in
the .find (manually or in code).

You may want to specify all the parms in your .find statement.

 

[Ron de Bruin]

Hi

I see you remove the on error code.

That's why you get the excel error and not the msgbox because there is no empty cell (only formulas)

You must loop through the range to find the first formula that return ""

Dim rng As Range
Dim cell As Range

Set rng = Range("AACash")
For Each cell In rng
If cell.Value = "" Then
cell.Select
Exit Sub
End If
Next cell