Solved Run time error '13 Mismatch Type

August 1, 2011 at 04:39:10
Specs: Windows XP
Hi, Do not know coding at all, but learned a bit from the solved problems. I am getting an error in my macro "Run Type error 13, Mismatch Type" in the code of Second method of loop used. The colum K contains formula i.e. Sum(H:J)/L and answers are in numbers or #DIV/0!. (i have used two different methods of loop for my practice and understanding, but even if I use single mehtod it gives error) The Code of Macro is as follows. I would be happy to share entire Macro with you with the excel file.

'Fifth Stage

For i = LastRow To 2 Step -1
If Range("J" & i).Value 0 Then Range("H" & i).Value = 0
Next i

'second method of loop

Range("K2:K" & LastRow).Select

Dim oCell As Range

For Each oCell In Selection

If oCell.Value > 120 Then oCell.Offset(0, -3) = 0
Next

Need your help.


See More: Run time error 13 Mismatch Type

Report •


✔ Best Answer
August 2, 2011 at 04:00:26
Before I can even consider analyzing your code (that's really not what we do in this forum, so I'm making no promises) I have to ask you to "clean it up".

Rarely do you have to Select a cell or object in order to to have VBA perform an operation on it.

Just about every place where you have a line that Selects a range followed by another line that performs an operation, you can condense that down to one line:

For example:

Range("K2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/RC[1]"

can become:

Range("K2").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/RC[1]"

Imagine how much easier your code will be to read once you have condensed all of those lines. In addition the code will much more efficient since you won't be asking Excel to "physically" Select all of those cells.

You also have wasted instructions like this:

Sheets(1).Range("H2:H" & LastRow).Select
    ActiveCell.Range("H2").Select

Why Select a range and then Select a single cell?

You should look through your code and eliminate all of the wasted instructions.

You should also review the following tutorial. It's something I wrote many years ago.

It's a list of tips that I picked up along the way while teaching myself how to use VBA.

Single Stepping and Watches are 2 very powerful tools to use when writing and debugging code.

Debugging Visual Basic Code 101- A Tutorial
=============================

Lesson 1 - Single Stepping

- Open the VBA editor.
- Place the cursor anywhere within the code.
- Hit F8 to begin Single Stepping through the code.

The next line to be executed will be highlighted, and execution will take place the next time you hit F8.

You can make changes to the code while in Single Step mode. Some changes will alert you that the Debugger will stop if you make that change, other changes will be accepted without interruption to the code.

You can quickly Single Step through the code by holding down the F8 key.

You can drag the step arrow down to an executable line and begin Single Stepping from there. You can also skip lines by dragging the arrow down at any time and re-run lines by dragging the arrow up. Keep in mind that you might get errors if the line you are trying to execute needs a value from a line that you skipped. If you skipped a line that set x = 8 but you don't want to run all the rest of the code around that line, simply type in a line that says x = 8 above the section that needs that value, place the Step arrow next to that line and begin stepping.

You can exit Single Step mode by clicking on the blue square in the tool bar.

You can click on the Run arrow at any time to let the macro finish on its own.

TIP: If you size and place your VBA editor window so that you can see your spreadsheet behind it you should be able to see your spreadsheet change as the code is executing. Fun and entertaining, yes...but also very valuable for
troubleshooting.

Lesson 2 - Pop Up Values

As you single step through your code, hold your cursor over a variable. The current value of that variable should pop up. Hold your cursor over things like Range("A1").Value. The current value that is in that cell should pop up. There are certain items that will not pop up a value and the method used to obtain those values will be covered later in the next section. Looking
at the Pop Up values is great way to see what your variables are getting set to as the code is running.

Lesson 3 - Adding Watches

- Highlight a variable or any other value-producing entity that you are interested in "watching" as you single step through the code.
- Hit Shift-F9.
- Hit Enter or click OK.
The name of the item will appear in a window in the bottom portion of the VBA editor. Add as many items as you would like. As you Single Step through your code, the current value of those items will appear next to the name.

Note: You can also highlight and drag the item into the Watch window if the window is already open.

In many cases, an item that would have produced an error if executed will show that error in the Watch window as soon as you enter Single Step mode.

There are items that won't pop up their values with the cursor held over them, but they will show their value in the Watch window.

TIP: If you are single stepping through a long loop, add a Watch on the counting variable (e.g. the x in For x = 1 to 100). Hold down the F8 key and watch as the x in the Watch window increments. Release the F8 key as the x approaches 100 and then use F8 at your leisure to finish the loop and continue stepping through your code.

Lesson 4 - Run To Cursor

A particularly useful item found under the Debug menu is Run To Cursor.

Let's say you know the first half of your code works fine, but something in the last half is giving you an error. This is where Run To Cursor comes in very handy.

- Click on any executable line in the code.
- Pull down the Debug menu and choose Run To Cursor, or press Ctrl-F8.

The macro will run at full speed until it reaches the line with the cursor.

It will then highlight that line and you can begin Single Stepping from there. Very useful for getting past long loops that you know are OK.

Lesson 5 - The Debug Menu

Pull down the Debug menu and investigate the other items found there.

For example, Toggle Breakpoint. If you set a breakpoint at any line, the code will run until it reaches the breakpoint and then exit the macro. There are many other items under the Debug menu to help debug your code.

I'm sure others will offer more methods for debugging code, but Single Stepping and Watches are two very powerful Debugging tools.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 1, 2011 at 07:31:07
That can't be the code you are using since this line throws up a syntax error:

 If Range("J" & i).Value 0 Then Range("H" & i).Value = 0 

There has to be an operator between Range("J" & i).Value and 0

As far as your Type Mismatch error, it's caused by the #DIV/0 error. When VBA sees that error, it can't evaluate it to be greater/ less than 120 since it's not a valid piece of data.

If you think that you'll have errors in your data, either check for the error in the code (read up on IsError in the VBA Help files) or use an error handling routine within the code itself. (read up on On Error)

BTW...before you post any more code in this forum, please click on the following line and read the instructions on how to use the 'pre' tags to format your code to make it easier for us to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 1, 2011 at 09:26:50
Thanks so much for your prompt and informative reply. Will do some changes to the code to remove #DIV/0 from data sheet. I would request you to go through my files and the code written. Though it gives me the desired result, but still I want to know if I (rather you) could have done it differently. I do not have the files and code handy with me right now. but will provide all the material ASAP for your reference. Thanks So much.

Report •

#3
August 2, 2011 at 00:04:09
Sub Macro7()
'
' Macro7 Macro
'

'
'First STAGE
LastRow = _
  Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  Sheets(1).Range("A1:A" & LastRow).EntireRow.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Rows("1:8").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Delete shift:=xlUp
    LastRow1 = _
  Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    
    
    
    Range("A3").FormulaR1C1 = "=R[-1]C"
    Range("A3").Select
    Selection.Copy
    Sheets(1).Range("A2:A" & LastRow1).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    ActiveCell.Columns("A:A").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'Second Stage

    Columns("G:G").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Insert shift:=xlToRight
    Selection.Insert shift:=xlToRight
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "DUMP Qty"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "PO Qty"
    ActiveCell.Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(CONCATENATE(RC[-6],RC[-5]),'[Dump Comilation.xlsx]Sheet1 (2)'!C5:C9,5,0)"
    ActiveCell.Select
    Selection.Copy
    Sheets(1).Range("G2:G" & LastRow).Select
    ActiveSheet.Paste
    Sheets(1).Range("G2:G" & LastRow).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(1).Range("G2:G" & LastRow).Select
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets(1).Range("H2:H" & LastRow).Select
    ActiveCell.Range("H2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    ActiveCell.Select
    Selection.Copy
    Sheets(1).Range("H2:H" & LastRow).Select
    ActiveSheet.Paste

'Third Stage

    Range("K1").Select
    ActiveCell.FormulaR1C1 = "DOS"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/RC[1]"
    Range("K2").Select
    Selection.Copy
    Sheets(1).Range("K2:K" & LastRow).Select
    ActiveSheet.Paste
    Sheets(1).Range("K2:K" & LastRow).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Initial Inv"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Final Inv"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "MOV"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "TO Do"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=RC[-10]*RC[-4]"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=RC[-10]*RC[-5]"
    Range("Q2:R2").Select
    Selection.Copy
    Sheets(1).Range("Q2:R" & LastRow).Select
    ActiveSheet.Paste

'Forth Stage

    Range("S2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'[MOV File.xlsx]Sheet1'!C1:C2,2,0)"
    Range("S2").Select
    Selection.Copy
    Sheets(1).Range("S2:S" & LastRow).Select
    ActiveSheet.Paste
    Sheets(1).Range("S2:S" & LastRow).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
'Fifth Stage



For i = LastRow To 2 Step -1
   If Range("J" & i).Value <> 0 Then Range("H" & i).Value = 0
Next i



'another method of loop

Range("K2:K" & LastRow).Select

Dim oCell As Object

For Each oCell In Selection

If oCell.Value > 120 Then oCell.Offset(0, -3) = 0

Next
    
'Range("K2:K" & LastRow).Select

'Dim nCell As Range


'For Each nCell In Selection
'If nCell.Value < 20 Then nCell.Offset(0, -3) = nCell.Offset(0, 5).Value

'Next
    
    
End Sub


Report •

Related Solutions

#4
August 2, 2011 at 00:21:15
Category Manager	(Multiple Items)												
Department	40												
Active	Item Active												
Financial Month	05-August		9066										
Financial Year	2011-2012												
Imported	Non Imported												
													
						Values							
Store Name	Item No	Item Description	Vendor No	Vendor Name	Replenishment Item	Inventory Quantity	Qty On Order	Item Count	ROS	Cost	MIN	MAX	MOQ
Aurangabad	8000989	maha.saver Tope Cover 22 Gauge No.7	100006	Silver Steel	Yes	45.0	0	1	0.16	15.4	8	13	1
	8000990	maha.saver Tope Cover 22 Gauge No.8	100006	Silver Steel	Yes	20.0	0	1	0.64	17.6	8	13	1
	8000991	maha.saver Tope Cover 22 Gauge No.9	100006	Silver Steel	Yes	40.0	0	1	0.29	22	8	13	1
	8000992	maha.saver Tope Cover 22 Gauge No.10	100006	Silver Steel	Yes	26.0	0	1	0.45	28.35	6	10	1
	8000993	maha.saver Tope Cover 22 Gauge No.11	100006	Silver Steel	Yes	30.0	0	1	0.29	32.54	15	23	1
	8001052	maha.saver Halwa Plate 5.5 inch 22 Gauge	100006	Silver Steel	Yes	60.0	0	1	0.25	20.59	28	42	1
	8001053	maha.saver Halwa Plate 6 inch 22 Gauge	100006	Silver Steel	Yes	64.0	0	1	0.83	22.88	28	42	1
	8001107	maha.saver Tiffin Size 7 x 2	100006	Silver Steel	Yes	24.0	0	1	0.83	88.66	13	20	1


Report •

#5
August 2, 2011 at 04:00:26
✔ Best Answer
Before I can even consider analyzing your code (that's really not what we do in this forum, so I'm making no promises) I have to ask you to "clean it up".

Rarely do you have to Select a cell or object in order to to have VBA perform an operation on it.

Just about every place where you have a line that Selects a range followed by another line that performs an operation, you can condense that down to one line:

For example:

Range("K2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/RC[1]"

can become:

Range("K2").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/RC[1]"

Imagine how much easier your code will be to read once you have condensed all of those lines. In addition the code will much more efficient since you won't be asking Excel to "physically" Select all of those cells.

You also have wasted instructions like this:

Sheets(1).Range("H2:H" & LastRow).Select
    ActiveCell.Range("H2").Select

Why Select a range and then Select a single cell?

You should look through your code and eliminate all of the wasted instructions.

You should also review the following tutorial. It's something I wrote many years ago.

It's a list of tips that I picked up along the way while teaching myself how to use VBA.

Single Stepping and Watches are 2 very powerful tools to use when writing and debugging code.

Debugging Visual Basic Code 101- A Tutorial
=============================

Lesson 1 - Single Stepping

- Open the VBA editor.
- Place the cursor anywhere within the code.
- Hit F8 to begin Single Stepping through the code.

The next line to be executed will be highlighted, and execution will take place the next time you hit F8.

You can make changes to the code while in Single Step mode. Some changes will alert you that the Debugger will stop if you make that change, other changes will be accepted without interruption to the code.

You can quickly Single Step through the code by holding down the F8 key.

You can drag the step arrow down to an executable line and begin Single Stepping from there. You can also skip lines by dragging the arrow down at any time and re-run lines by dragging the arrow up. Keep in mind that you might get errors if the line you are trying to execute needs a value from a line that you skipped. If you skipped a line that set x = 8 but you don't want to run all the rest of the code around that line, simply type in a line that says x = 8 above the section that needs that value, place the Step arrow next to that line and begin stepping.

You can exit Single Step mode by clicking on the blue square in the tool bar.

You can click on the Run arrow at any time to let the macro finish on its own.

TIP: If you size and place your VBA editor window so that you can see your spreadsheet behind it you should be able to see your spreadsheet change as the code is executing. Fun and entertaining, yes...but also very valuable for
troubleshooting.

Lesson 2 - Pop Up Values

As you single step through your code, hold your cursor over a variable. The current value of that variable should pop up. Hold your cursor over things like Range("A1").Value. The current value that is in that cell should pop up. There are certain items that will not pop up a value and the method used to obtain those values will be covered later in the next section. Looking
at the Pop Up values is great way to see what your variables are getting set to as the code is running.

Lesson 3 - Adding Watches

- Highlight a variable or any other value-producing entity that you are interested in "watching" as you single step through the code.
- Hit Shift-F9.
- Hit Enter or click OK.
The name of the item will appear in a window in the bottom portion of the VBA editor. Add as many items as you would like. As you Single Step through your code, the current value of those items will appear next to the name.

Note: You can also highlight and drag the item into the Watch window if the window is already open.

In many cases, an item that would have produced an error if executed will show that error in the Watch window as soon as you enter Single Step mode.

There are items that won't pop up their values with the cursor held over them, but they will show their value in the Watch window.

TIP: If you are single stepping through a long loop, add a Watch on the counting variable (e.g. the x in For x = 1 to 100). Hold down the F8 key and watch as the x in the Watch window increments. Release the F8 key as the x approaches 100 and then use F8 at your leisure to finish the loop and continue stepping through your code.

Lesson 4 - Run To Cursor

A particularly useful item found under the Debug menu is Run To Cursor.

Let's say you know the first half of your code works fine, but something in the last half is giving you an error. This is where Run To Cursor comes in very handy.

- Click on any executable line in the code.
- Pull down the Debug menu and choose Run To Cursor, or press Ctrl-F8.

The macro will run at full speed until it reaches the line with the cursor.

It will then highlight that line and you can begin Single Stepping from there. Very useful for getting past long loops that you know are OK.

Lesson 5 - The Debug Menu

Pull down the Debug menu and investigate the other items found there.

For example, Toggle Breakpoint. If you set a breakpoint at any line, the code will run until it reaches the breakpoint and then exit the macro. There are many other items under the Debug menu to help debug your code.

I'm sure others will offer more methods for debugging code, but Single Stepping and Watches are two very powerful Debugging tools.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 2, 2011 at 20:26:53
This was my first ever written code. I will use all the suggestion/instructions for my next macro code writting. And fact remains that you are such a help. Will go through Tutorial and apply the same further. Thanks again.

Report •

Ask Question