Validation rules

I uses records macro from Excel to add a new validation.
I copied the code generated by macro to my function, but it fails.

The code is following:

For i = 19 To iRow
For J = 7 To iCol
With Sheets(wsDestination).Cells(i, J).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next J
Next i


I got run time erro 1004


"Unable to get large property of worksheetfunction class"

Your macro ran for me after I fleshed it out a bit:

Sub a()
Dim i As Integer, J As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim wsDestination As String
wsDestination = "Sheet1"
iRow = 25
iCol = 10
For i = 19 To iRow
For J = 7 To iCol
With Sheets(wsDestination).Cells(i, J).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop,
_
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next J
Next i
End Sub

The error message you reported cannot have anything to do with the code you
posted since it does not use the Large worksheet function. You're running
some other sub I think. Btw, there is no reason to iterate through the
cells in the range one at a time. You can apply the validation to all of
them at once:

Sub aa()
Dim iRow As Integer
Dim iCol As Integer
Dim wsDestination As String
wsDestination = "Sheet1"
iRow = 25
iCol = 10
With Sheets(wsDestination).Cells(19, 7).Resize(iRow - 19 + 1, iCol - 7 +
1).Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "I think you need some kind of message don't you?"
.ShowInput = True
.ShowError = True
End With
End Sub



--
Jim
"Souris" <Souris@discussions.microsoft.com> wrote in message
news:51979812-8BE3-4346-83E7-F07BA6A775C1@microsoft.com...
>I uses records macro from Excel to add a new validation.
> I copied the code generated by macro to my function, but it fails.
>
> The code is following:
>
> For i = 19 To iRow
> For J = 7 To iCol
> With Sheets(wsDestination).Cells(i, J).Validation
> .Delete
> .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, Formula1:="0", Formula2:="100"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> Next J
> Next i
>
>
> I got run time erro 1004
>
>
> "Unable to get large property of worksheetfunction class"
>
>

It works fine for me.

Did you initialise iRow, iCol and wsDestination?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Souris" <Souris@discussions.microsoft.com> wrote in message
news:51979812-8BE3-4346-83E7-F07BA6A775C1@microsoft.com...
> I uses records macro from Excel to add a new validation.
> I copied the code generated by macro to my function, but it fails.
>
> The code is following:
>
> For i = 19 To iRow
> For J = 7 To iCol
> With Sheets(wsDestination).Cells(i, J).Validation
> .Delete
> .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, Formula1:="0", Formula2:="100"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> Next J
> Next i
>
>
> I got run time erro 1004
>
>
> "Unable to get large property of worksheetfunction class"
>
>

Thanks millions for the information,

I found the problem:

1. The cell format must be number, can not be general
2. I need select the spreadsheet.
My button and code are on sheet1, but I wanted to fill validation on sheet2.

Thanks again,




"Jim Rech" wrote:

> Your macro ran for me after I fleshed it out a bit:
>
> Sub a()
> Dim i As Integer, J As Integer
> Dim iRow As Integer
> Dim iCol As Integer
> Dim wsDestination As String
> wsDestination = "Sheet1"
> iRow = 25
> iCol = 10
> For i = 19 To iRow
> For J = 7 To iCol
> With Sheets(wsDestination).Cells(i, J).Validation
> .Delete
> .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop,
> _
> Operator:=xlBetween, Formula1:="0", Formula2:="100"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> Next J
> Next i
> End Sub
>
> The error message you reported cannot have anything to do with the code you
> posted since it does not use the Large worksheet function. You're running
> some other sub I think. Btw, there is no reason to iterate through the
> cells in the range one at a time. You can apply the validation to all of
> them at once:
>
> Sub aa()
> Dim iRow As Integer
> Dim iCol As Integer
> Dim wsDestination As String
> wsDestination = "Sheet1"
> iRow = 25
> iCol = 10
> With Sheets(wsDestination).Cells(19, 7).Resize(iRow - 19 + 1, iCol - 7 +
> 1).Validation
> .Delete
> .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, Formula1:="0", Formula2:="100"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = "I think you need some kind of message don't you?"
> .ShowInput = True
> .ShowError = True
> End With
> End Sub
>
>
>
> --
> Jim
> "Souris" <Souris@discussions.microsoft.com> wrote in message
> news:51979812-8BE3-4346-83E7-F07BA6A775C1@microsoft.com...
> >I uses records macro from Excel to add a new validation.
> > I copied the code generated by macro to my function, but it fails.
> >
> > The code is following:
> >
> > For i = 19 To iRow
> > For J = 7 To iCol
> > With Sheets(wsDestination).Cells(i, J).Validation
> > .Delete
> > .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
> > Operator:=xlBetween, Formula1:="0", Formula2:="100"
> > .IgnoreBlank = True
> > .InCellDropdown = True
> > .InputTitle = ""
> > .ErrorTitle = ""
> > .InputMessage = ""
> > .ErrorMessage = ""
> > .ShowInput = True
> > .ShowError = True
> > End With
> > Next J
> > Next i
> >
> >
> > I got run time erro 1004
> >
> >
> > "Unable to get large property of worksheetfunction class"
> >
> >
>
>
>