Validating number of entries

  • Thread starter Thread starter NeilB
  • Start date Start date
N

NeilB

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
 
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 said:
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


------------------------------------------------



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