Now I get a run time error 438 at

..Column("A").AutoFilter field:=1, Criteria1:=companyName

"joel" wrote:


There are lotos of reasons this code can fail

1) Autofilter is not turned on
2) the data you are autofilter isn't found, or the combination of
the 3 conditions
3) You can end up if your criteria isn't met reading from row 0


This code is pretty Idiot proof and you won't get the same errors you
were getting before.


Dim x As Long
Dim y As Long
Dim cell As Range

With Sheets("summary")
x = .Range("c11")
i = .Range("c8")

With Sheets("pyendLAYERS")
'turn off autofilter
If .AutoFilterMode = True Then
.Columns.AutoFilter
End If

LastRow = .Range("a1").End(xlDown).Row

companyName = Range("COMPANY")

.Columns("A:C").AutoFilter

Set c = .Columns("A").Find(what:=companyName, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Company : " & companyName & vbCrLf & _
"Exiting Macro")
Exit Sub
End If
.Column("A").AutoFilter field:=1, Criteria1:=companyName

Tax = Range("TAX")
Set c = .Columns("B").Find(what:=Tax, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Tax : " & Tax & vbCrLf & _
"Exiting Macro")
Exit Sub
End If

.Columns("A:C").AutoFilter field:=2, Criteria1:=Tax

Resale = Range("RESALE")
Set c = .Columns("C").Find(what:=Resale, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Tax : " & Resale & vbCrLf & _
"Exiting Macro")
Exit Sub
End If

.Columns("A:C").AutoFilter field:=3, Criteria1:=Resale

NewRow = LastRow + 1
If x <= 0 Then

.Range("A" & NewRow) = Range("company")
.Range("B" & NewRow) = Range("tax")
.Range("C" & NewRow) = Range("RESALE")
.Range("D" & NewRow) = Range("year")
.Range("E" & NewRow) = x
.Range("F" & NewRow) = i
.Range("G" & NewRow).FormulaR1C1 = "=rc[-2]*rc[-1]"

Else
For RowCount = LastRow To 1 Step -1
If .Rows(RowCount).EntireRow.Hidden = True Then
y = .Range("E" & RowCount)
If -x < y Then
.Range("E" & RowCount) = y + x
End If
x = x + y
End If
Next RowCount
End If
End With
End With


Range("layertotal").Copy
Range("taxdec").PasteSpecial Paste:=xlValues


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169202

Microsoft Office Help

.