Run Time Error "13": Type mismatch

[Matt]

I keep getting a run time error when this macro gets to y=ActiveCell:

Any help would be appreciated.

40 ActiveCell.Offset(1, 4).Select
Do Until x > 0
ActiveCell = 0
ActiveCell.Offset(-1, 0).Select
y = ActiveCell.Select
Do Until ActiveCell.EntireRow.Hidden = False
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(-1, 0).Select
End If
Loop
y = ActiveCell
If -x < y Then
ActiveCell = y + x
End If
x = x + y
Loop

 

[JLGWhiz]

Try:

y = ActiveCell.Value

 

[Rick Rothstein]

The ActiveCell is already selected (plus you can't perform a selection as
part of an assignment... selection on one line, assignment on a different
line)... just assign the Value property to y...

y = ActiveCell.Value

 

[JLGWhiz]

Also, I don't understand this line:

y = ActiveCell.Select

What is it supposed to yield? Value? Address? Just move pointer?

 

[Matt]

I didn't put in the whole code, but it is being used to caculate the resale
value of assets for tax purposes based upon their classification. Below is
the complete code.

Dim x As Long
Dim y As Long
Dim cell As Range
Sheets("summary").Select
x = Range("c11")
i = Range("c8")
Sheets("pyendLAYERS").Select
Range("a1").Activate
Selection.End(xlDown).Select
z = Range("COMPANY")
Selection.AutoFilter field:=1, Criteria1:=z
v = Range("TAX")
Selection.AutoFilter field:=2, Criteria1:=v
w = Range("RESALE")
Selection.AutoFilter field:=3, Criteria1:=w
If x > 0 Then GoTo 20
GoTo 40
20 ActiveCell.Offset(1, 0).Select
y = ActiveCell.Select
ActiveCell = Range("company")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("tax")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("RESALE")
ActiveCell.Offset(0, 1).Select
y = ActiveCell.Select
ActiveCell = Range("year")
ActiveCell.Offset(0, 1).Select
ActiveCell = x
ActiveCell.Offset(0, 1).Select
ActiveCell = i
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=rc[-2]*rc[-1]"
40 ActiveCell.Offset(1, 4).Select
Do Until x > 0
ActiveCell = 0
ActiveCell.Offset(-1, 0).Select
y = ActiveCell.Select
Do Until ActiveCell.EntireRow.Hidden = False
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(-1, 0).Select
End If
Loop
y = ActiveCell
If -x < y Then
ActiveCell = y + x
End If
x = x + y
Loop

Range("layertotal").Select
Selection.Copy
Sheets("summary").Select
Range("taxdec").Activate
Selection.PasteSpecial Paste:=xlValues

 

[Matt]

Now I get a run time error 438 at

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

 

[JLGWhiz]

You have a syntax problem:

Columns("A").AutoFilter Field:=1, Criteria1:=companyName

Collections are always plural.

Now I get a run time error 438 at

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

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/showthread.php?t=169202

Microsoft Office Help

.