jmbarnes65
03-19-2021, 09:36 AM
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
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