Find first blank cell in single column named range

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

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"tig" <[email protected]> wrote in message news:[email protected]...
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"tig" <[email protected]> wrote in message
news:[email protected]...
> 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,

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

Hi Tig

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

Try again



--
Regards Ron de Bruin
http://www.rondebruin.nl


"tig" <[email protected]> wrote in message news:[email protected]...
> 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,

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

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.

Hi tig

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"tig" <[email protected]> wrote in message news:[email protected]...
> Ron,
>
> Still got the same 1004 error. Let me know if you have any other
> ideas. Thanks for your help thus far.
>

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.

tig wrote:
>
> 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

--

Dave Peterson

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> Hi tig
>
> Send me your test workbook private then i take a look at it
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "tig" <[email protected]> wrote in message news:[email protected]...
>> Ron,
>>
>> Still got the same 1004 error. Let me know if you have any other
>> ideas. Thanks for your help thus far.
>>
>
>