Object Required Error when trying to define a LastRowCount

[Narrov]

I am constantly getting a Object Required Error on "LastR = " and have no idea why. It appears to be something wrong with the argument I'm using to try to define LastR but I dont understand what is going wrong please can someone help.

Sub CopyUniqueValidation()

Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet

'Defines S1 as a Worksheet with the same name as the contents of Customer Trend Sheet Cell B2
Set s1 = Worksheets(Worksheets("Customer Trend").Range("B2").Value)

'Defines S2 as Working Sheet
Set s2 = Sheets("Workings")

'Defines S3 as Customer Trend Sheet
Set s3 = Sheets("Customer Trend")


Dim LastR As Long, DataRange As Range

'Finds Number of the last row cell in column B of Workings sheet
Set LastR = s2.Range("B1").CurrentRegion.Rows.Count

'Creates a "DataRange" from B1 to last row cell in Column B
Set DataRange = s2.Range("B1:B" & LR)


'Takes Data from Customer Column of defined PPA Sheet (based on Customer Trend B2) and pastes it to Working Sheet Column B
s1.Range("H6:H500").Copy s2.Range("b1")

'Removes Duplicates from Column B Working sheet
s2.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo


'Sorts Column B Working Sheet Ascending
s2.Range("B:B").Sort Key1:=s2.Range("B1"), Order1:=xlAscending, Header:=xlNo

'Creates DataValidation List in Customer Trend B3 based on DataRange above
With s3.Range("B3")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DataRange
End With
End With


End Sub

 

[GTO]

'Finds Number of the last row cell in column B of Workings sheet
Set LastR = s2.Range("B1").CurrentRegion.Rows.Count 

Ditch the Set statement, as Set is to Set a reference to an Object. LastR is defined in your Dim statement as a Long, so you just need to Let LastR = [some range].Rows.Count. Let is inferred/default, so you just need the part in red.

Hope that helps,

Mark

 

[Narrov]

'Finds Number of the last row cell in column B of Workings sheet
Set LastR = s2.Range("B1").CurrentRegion.Rows.Count 

Ditch the Set statement, as Set is to Set a reference to an Object. LastR is defined in your Dim statement as a Long, so you just need to Let LastR = [some range].Rows.Count. Let is inferred/default, so you just need the part in red.

Hope that helps,

Mark

Ditching the Set now gives me a type mismatch error :S

 

[Norie]

Why aren't you using LastR here?

    Set DataRange = s2.Range("B1:B" & LR)

 

[GTO]

Can you use code tags like:

[code]...your code here[/code]

and post the code as you now have it?

Thanks,

Mark

PS. Norie has sharper eyes than mine

 

[Narrov]

Updated the code. Still getting a type Mismatch error

Sub CopyUniqueValidation()      
 Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
 
    'Defines S1 as a Worksheet with the same name as the contents of Customer Trend Sheet Cell B2
    Set s1 = Worksheets(Worksheets("Customer Trend").Range("B2").Value)
    
    'Defines S2 as Working Sheet
    Set s2 = Sheets("Workings")
    
    'Defines S3 as Customer Trend Sheet
    Set s3 = Sheets("Customer Trend")


Dim LastR As Long, DataRange As Range
    
    'Finds last row cell in column B of working sheet
    LastR = s2.Range("B1").CurrentRegion.Rows.Count
    
    'creates a "DataRange" from B1 to last row cell in Column B
    Set DataRange = s2.Range("B1:B" & LastR)
  
    
    'Takes Data from Customer Column of defined PPA Sheet (based on Customer Trend B2) and pastes it to Working Sheet Column B
    s1.Range("H6:H500").Copy s2.Range("b1")
    
    'Removes Duplicates from Column B Working sheet
    s2.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
    
 
 'Sorts Column B Working Sheet Ascending
 s2.Range("B:B").Sort Key1:=s2.Range("B1"), Order1:=xlAscending, Header:=xlNo
 


'Data Validation List Based on DataRange
With s3.Range("B3")
   With .Validation
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:="=" & DataRange
    End With
 End With


End Sub

 

[Norie]

Where do you get the error?

 

[Narrov]

Where do you get the error?

The debug doesnt point to anywhere specific. When I run the macro it appears to run as far as the end of the sort data and no further.

 

[GTO]

With the cursor anyplace in CopyUniqueValidation(), press the F8 key repeatedly to execute the code line-by-line. Pay attention to what line you are on when it goes KABOOM...

 

[Narrov]

With the cursor anyplace in CopyUniqueValidation(), press the F8 key repeatedly to execute the code line-by-line. Pay attention to what line you are on when it goes KABOOM...

It errors at this point

[/COLOR] 
With .Validation     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:="=" & DataRange
    End With