get validation on a cell clicking a button

[kiwis]

Hi

I did not get any errors when i run the macro to get the product list.

when i try calling the macro by clicking on the button, i get this
error msg
run-time error 1004
Application-defiend or object-defined error

product list on sheet 2, validation cell on sheet 3, button on sheet
1,ws is sheet 2

Code for the button
Private Sub CommandButton1_Click()
'get stock code to cell C3 on result page
Call findpdtlist
End Sub

part of the code to get product list, result is sheet 3

Set rng = ws.Range("A2:A" & lastr)
rng.Name = "pdtlist"

'get the pdt list on result page
With Worksheets("Result").Range("C3").Validation
.Add Type:=xlValidateList, Formula1:="=pdtlist" <-----------
this line is flag out by the debugger
End With

there is no error when i just run the macro directly.

Why is the error appearing & how to solve it?

 

[Bob Phillips]

Is there already a DV in C3? If so, delete it first.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

kiwis

I tried emulating your code to follow the workings.

it would appear that this segment is the problem:

.... 'Set rng = ws.Range("A2:A" & lastr)'
Mainly the variable - lastr . As this generates the error numbe 1004 when I
tested it.

However I am a bit confused - as you said that it worked when you ran the
macro. Can you please elaborate?

Rgds

SysAccountant

 

[kiwis]

i have deleted the DV in that cell at the start of findpdtlist sub

still got the error
here is the full code

Sub findpdtlist()
'get the pdt list from the raw

Dim lastr As Long
Dim rng As Range
Dim ws As Worksheet

Set ws = Worksheets("list")

' Application.ScreenUpdating = False

'clear the drop down list
Worksheets("Result").Range("C3").Validation.Delete

'unhide sheet3
ws.Visible = xlSheetVisible

'clear data
ws.Range("A1").CurrentRegion.Delete

'name all the data on raw page
Worksheets("Raw").Range("A1").CurrentRegion.Name = "data"

'copy the pdt number from raw
Sheets("Raw").Columns("C:C").AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Sheets("list-description").Range("A1"),
Unique:=True

'find last row of list
lastr = ws.Cells(Rows.Count, "A").End(xlUp).Row

' MsgBox lastr

'sort the pdt in ascending order
ws.Range("A:A").Sort key1:=ws.Range("A1"), order1:=xlAscending,
header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortTextAsNumbers

ws.Range("A1").CurrentRegion.EntireColumn.AutoFit

Set rng = ws.Range("A2:A" & lastr)
rng.Name = "pdtlist"

'get the pdt list on result page
With Worksheets("Result").Range("C3").Validation
.Add Type:=xlValidateList, Formula1:="=pdtlist" <--------
still error here
End With

'autofit list column
Worksheets("Result").Range("C2").Columns.AutoFit

'hide sheet 3
' ws.Visible = xlSheetHidden
' Application.ScreenUpdating = True

End Sub

using in excel 2003

 

[Guest]

kiwis

I think I know what might be contributing to the error code 1004.

It appears to be the advanced filtering to another sheet(i.e it appears as
if you are attempting to transfer the data to sheet "list-description").

The Advanced filter technique doesn't work in this direction , i.e from
sheet A TO Sheet B- there is a particular method that must be employed.

You need to select Sheet B (i.e the sheet that you want to transfer the data
to FIRST,then run the Advanced Filter.Therefore the logic is Shhet B FROM
Sheet A

HTH

Regards

SysAccountant

 

[kiwis]

Thanks everyone who replied, i got the error fixed.
Like what sysaccountant said, i make some changes with the logic/order
of advanced filter & got it
to work.

thanks