Object Required Error when trying to define a LastRowCount

Narrov

New Member
Joined
Oct 27, 2016
Messages
7
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Rich (BB code):
'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

New Member
Joined
Oct 27, 2016
Messages
7
Rich (BB code):
'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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Why aren't you using LastR here?
Code:
    Set DataRange = s2.Range("B1:B" & LR)
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

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 :)
 
Last edited:

Narrov

New Member
Joined
Oct 27, 2016
Messages
7
Updated the code. Still getting a type Mismatch error

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Where do you get the error?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

New Member
Joined
Oct 27, 2016
Messages
7
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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top