Run Error 13

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Dear All,

What seems to be wrong with this code? It is giving me a runtime error 13????
Code:
Private Sub CommandButton3_Click()
 Dim r As Long
    With Sheet1
         r = Application.Match(MRITRBox.Value, .Range("G:G"), False)
        .Cells(r, 20).Value = MRITRUpd.Value
        
        Range("a1").Select
       
    End With
End Sub
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Code:
r = Application.Match(MRITRBox.Value, .Range("G:G"), [COLOR=red]0[/COLOR])
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
The Match function might not be finding a match and returning an error.

Why not try VBA Find?
 

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373

ADVERTISEMENT

This particular line
Code:
r = Application.Match(MRITRBox.Value, .Range("G:G"), False)
:(
:(
 

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi Norie,

Tried replacing Match with Find, but it gave me the same error..
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is untested but perhaps you could use it or something similar instead of Application.Match
Code:
Private Sub CommandButton3_Click()
Dim rngFnd As Range
 
    With Sheet1
    
        Set rngFnd = .Range("G:G").Find(MRITRBox.Value)
        If Not rngFnd Is Nothing Then
            ' if found
            .Cells(rngFnd.Row, 20).Value = MRITRUpd.Value
        End If
    End With
 
End Sub
Note if nothing is found nothing will happen, but you won't get an error.
 

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi shg4421,

I replaced "false" with 0, but still the same error occurs...
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
What is MRITRBox ? Is it a Text Box?
Text boxes contain.....well....Text..
You won't find a match to a text value in a column of numbers (G).

Perhaps

r = Application.Match(Val(MRITRBox.Value), .Range("G:G"), 0)


Hope that helps.


Note, the reason for 0 instead of FALSE is a little nitpicky..but using FALSE is technically incorrect (even though it works).
The Match function is looking for an argument of either -1, 0, or 1 (it is NOT a True or False argument)

Using TRUE = 1, FALSE = 0. But there is no equivelant for -1.
 
Last edited:

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi norie,

Your code worked! Thanks a lot. Now I can rest for a while..been breaking my head for hours. I am just wondering why the previous code didn't work?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top