Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first blank cell in single column named range
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Query Named Range Return Single Column Value | Excel Worksheet Functions | |||
Named Range reference via single Cell | Excel Discussion (Misc queries) | |||
named Range Referenced vai single cell | Excel Discussion (Misc queries) | |||
Identifying single column within named range | Excel Discussion (Misc queries) |