VBA Help - Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range'

[jmbarnes65]

On the Data Entry sheet, I created a drop down displaying county names. The named range that is the source for my drop down is stored on the Dropdowns sheet. When the user selects the county name from the drop-down the following code looks up the county code on the Dropdowns sheet and returns it rather than the county name. For example, when "Autauga" is selected, the value "01" is stored in that cell rather than Autauga.

Everything works fine as long as I don't use the fill handle or copy and paste to copy the county code value. When I use the fill handle or copy/paste I get this error:

VBA Help - Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range'

Here's my VBA code. I'm a novice, so be gentle!



Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice

selectedNa = Target.Value
If Target.Column = 4 Then
selectedNum = Application.VLookup(selectedNa, Worksheets("Dropdowns").Range("counties2"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

[p45cal]

Working fine here, but I may not have correctly reproduced your setup; best attach a workbook with this happening in it.

[jmbarnes65]

Thanks for the quick reply, P45cal. Here's my workbook. Also, in case I wasn't entirely clear, the code works fine until I attempt to copy/paste a value in cell D3 to cells below it in column D.

[SamT]

That Procedure works when only one cell is changed.

My code Style includes the premises,


Don't overload Event Subs.
Never use anything in code that the User can change on the sheet.


although this code is not completely User Safe.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then ColFour Target
End Sub

Private Sub ColFour(ByVal Target As Range)
Dim selectedNum As Long
Dim Cel As Range

For Each Cel In Target
selectedNum = WorksheetFunction.VLookup(Cel, Sheet1.Range("D:E"), 2, False)
If Not IsError(selectedNum) Then
Cel = selectedNum
Else
Cel = "NA"
End If
Next Cel

End Sub

[p45cal]

or:
Private Sub Worksheet_Change(ByVal Target As Range)
Set myRng = Intersect(Columns(4), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("counties2"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If
End Sub

[jmbarnes65]

That did it! Thanks so much for your help!

[jmbarnes65]

Thanks so much for your help on this p45cal. I pasted your code in and it works great. Users are now able to copy, paste, and drag the fill handle without an error. Thanks again for your assistance!