VBA Error - 1004 Method of Range Class Failed

clappouk

Board Regular
Joined
Sep 2, 2010
Messages
54
Afternoon All,

I am having problems using autofilter within VBA, I am getting the above error when I used the following code:
Code:
Sub GetIP()
 
Dim DFWB As Workbook  'TM Tool
Dim NewWb As Workbook 'New Data sheet for all linked Ip addresess
Dim DataWb As Workbook 'TM Monitoring Spreadsheet
Dim DataWbs As Worksheet
Dim rtable As Range
Dim Flist As Worksheet
Dim NewWbs As Worksheet
Dim wkb As Workbook
Dim Tb As TextBox
Dim i As Integer
 
Set DFWB = ThisWorkbook
Set NewWb = Workbooks.Add(template:=xlWBATWorksheet)
' you must change the LookIn line of code to the Folder you wish to search
Set fs = Application.FileSearch
With fs
        .LookIn = "[URL="file://\\irf00969\Rst"]\\irf00969\Rst[/URL] Bootle\RST TM\VAT TM SWORD TEMPLATES\VAT TM Alerts From 13082013"
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
Set DataWb = Workbooks.Open(.FoundFiles(i))
        
'Set up data capture sheet - initial
Set NewWbs = NewWb.Worksheets(1)
Lastrow = NewWbs.Cells(Rows.Count, 2).End(xlUp).Row
Headln = Lastrow + 2
Nextrow = Headln + 1

'Open TM Macro Sheet & get Value for DR
DFWB.Activate
DR = "212.137.36.228"

'Go back to Datasheet to get look for data to copy
DataWb.Activate 'Activate Workbook
Set DataWbs = Worksheets(1) ' Assign variable to Sheet1
DataWbs.Activate
Col = DataWbs.Application.Match("IPADDR", Rows("6:6"), 0)
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False [COLOR=#008000]' if autofilter is on switch off[/COLOR]
[COLOR=#ff0000]DataWbs.Range("A6:CW6").AutoFilter Field:=Col, Criteria1:=DR, Operator:= _
        xlAnd[/COLOR]
        
Set rtable = DataWbs.Range("A6").CurrentRegion

The macro works fine upto where I want to apply the autofilter (get the above Error) - could the problem be its 100 columns wide?

Col = Supplies the Field Ref by using match to find which column "IPADDR" is in
DR = is the IP Address I am looking for within the spreadsheet

rtable = all the cells provided by the working autofilter



Any help will be apprciated

Ray Excel 2003
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,020
Members
415,946
Latest member
bellerom

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