Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run Time Error "13": Type mismatch
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 516
Default Run Time Error "13": Type mismatch
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Run Time Error "13": Type mismatch
Try:

y = ActiveCell.Value


"Matt" wrote in message
...
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Run Time Error "13": Type mismatch

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

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


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

Microsoft Office Help

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Run Time Error "13": Type mismatch
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

--
Rick (MVP - Excel)


"Matt" wrote in message
...
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Run Time Error "13": Type mismatch
Also, I don't understand this line:

y = ActiveCell.Select

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



"Matt" wrote in message
...
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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 516
Default Run Time Error "13": Type mismatch
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" wrote:


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

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


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

Microsoft Office Help

.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Run Time Error "13": Type mismatch

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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 516
Default Run Time Error "13": Type mismatch
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

.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Run Time Error "13": Type mismatch
You have a syntax problem:

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

Collections are always plural.



"Matt" wrote in message
...
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

.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reasons for "Run-time error '13':Type mismatch"? BagaAzul Excel Programming 2 March 20th 06 11:08 AM
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch" ExcelMonkey Excel Programming 3 October 16th 05 01:56 PM
Run-time Error "13" - File Type Mismatch brentm Excel Programming 1 February 10th 05 05:09 PM
Help with Run-time error: "Type Mismatch" Metin Excel Programming 2 January 26th 05 02:11 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement