I am currently trying to get a marco to recongise when 86 Rows have bee
written in an Excel workbook. I have currently created a Macro t
insert a new row and copy the validation to the cells in the new row
But I am having problems getting the If statement to work properly a
it keeps displaying a messagebox no matter how many cells have bee
entered, even if they are under 86.

Please Help.


Sub AddNewHouse()
'
' AddNewHouse Macro
' Adds New House to the table
'

'
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A3:K3").Select
Selection.AutoFill Destination:=Range("A2:K3")
Type:=xlFillDefault
Range("A2:K3").Select
Range("A2:K2").Select
Selection.ClearContents
Range("J3").Select
Selection.AutoFill Destination:=Range("J2:J3")
Type:=xlFillDefault
Range("J2:J3").Select
Range("A2").Select

'Makes sure no more then 84 houses can be entred
If Rows("86:86").Text = "" Then
MsgBox "No more Records can be inserted here!"
End If

End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

If Rows("86:86").Text = "" Then
MsgBox "No more Records can be inserted here!"
End If

could be

if Application.CountA(Rows("86:86")) 0 then
MsgBox "No more Records can be inserted here!"
End If


--
Regards,
Tom Ogilvy

"NeilB" wrote in message
...

I am currently trying to get a marco to recongise when 86 Rows have been
written in an Excel workbook. I have currently created a Macro to
insert a new row and copy the validation to the cells in the new row,
But I am having problems getting the If statement to work properly as
it keeps displaying a messagebox no matter how many cells have been
entered, even if they are under 86.

Please Help.


Sub AddNewHouse()
'
' AddNewHouse Macro
' Adds New House to the table
'

'
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A3:K3").Select
Selection.AutoFill Destination:=Range("A2:K3"),
Type:=xlFillDefault
Range("A2:K3").Select
Range("A2:K2").Select
Selection.ClearContents
Range("J3").Select
Selection.AutoFill Destination:=Range("J2:J3"),
Type:=xlFillDefault
Range("J2:J3").Select
Range("A2").Select

'Makes sure no more then 84 houses can be entred
If Rows("86:86").Text = "" Then
MsgBox "No more Records can be inserted here!"
End If

End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements