ComboBox1 Run-Time error '1004'

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Hello all,
I am using a combo box on sheet1 with a defined 'listfillrange' that refers to a column in a range of data on sheet2.
Everything works as it should until I either sort or filter the data on sheet2 whereupon I get - "Run-Time error '1004': Select method of Range class failed" on line 3 of code below.

Sheet1 code
Code:
Option Explicit

Private Sub ComboBox1_Change()
       Worksheets("Sheet1").Range("C8:C54").Select
       Selection.Interior.ColorIndex = xlNone
       Worksheets("Sheet1").Range("G8:G54").ClearContents

Application.ScreenUpdating = False
 
Sheets("Sheet1").Activate
      ActiveSheet.Range("D8").Select
      ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C3,'Data Sheet'!R15C6:R141C162,13)"
      ActiveSheet.Range("D9").Select
      ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C3,'Data Sheet'!R15C6:R141C162,16)"
      ActiveSheet.Range("D10").Select
      ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C3,'Data Sheet'!R15C6:R141C162,19)"
  'etc
End Sub

Can anyone tell me what can do to prevent this run-time error occurring?
Thanks.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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