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

 

[joel]

The code soesn't make a lot of sense. Can you explain what you ar
doing and some sample data.

the error is occuring because you are in some looop where you ar
either trying to access a row number less than 1 or greater than th
number of rows on a worksheet (65,536) because one of your condition
aren't being met. There are better ways of writing this type code but
can't figure out what you are really trying to do

 

[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

 

[joel]

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 o
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 yo
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:=xlValue

 

[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

.