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

Find first blank cell in single column named range
 

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" wrote in message oups.com...
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

Find first blank cell in single column named range
 

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

--
Don Guillett
SalesAid Software

"tig" wrote in message
oups.com...
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

Find first blank cell in single column named range
 

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

Find first blank cell in single column named range
 

Hi Tig

Reboot first and
StartRun
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" wrote in message ups.com...
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

Find first blank cell in single column named range
 

Ron,

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

Find first blank cell in single column named range
 

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.

Find first blank cell in single column named range
 

Hi tig

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

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


"tig" wrote in message ups.com...
Ron,

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

Find first blank cell in single column named range
 

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

Find first blank cell in single column named range
 

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" wrote in message ...
Hi tig

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

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


"tig" wrote in message ups.com...
Ron,

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