Match function error

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Match function error

Post by adeel1 »

Hi All

i am having issue that if value in array exist that code works but when I change any cell value which is not in array it causes error how i deal with it

Adeel

Code: Select all

Sub kkk()
 Dim arr() As Variant
 Dim arr1() As Variant


 arr = Array("ll", 500, 100, 2500, 250)
 arr1 = Array("mm", 15025, 3325, 6565, 3333)
 
k = Cells(Rows.Count, 1).End(3).Row

 For i = 2 To k
 
If Cells(i, 1) <> "" Then
 
 m = Application.Match(Range("A" & i), arr, 0) - 1

 Cells(i, 2) = arr1(m)
 

End If
Next i
End Sub
You do not have the required permissions to view the files attached to this post.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1276
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Match function error

Post by Doc.AElstein »

Hi,
If Application.Match finds a match then it will give you an integer number – you probably know that
So then you will have
m = number - 1
that is OK – No Problem

If Application.Match does not find a match then it will return you a vbError string
So then you will have
m = vbError - 1
that is rubbish – you can’t do that – you get a type mismatch

_._______________________________-

What we can do is test for the error, like this

If IsError(Application.Match(Range("A" & i), arr(), 0)) Then

_._____________________________

IsError(Application.Match(Range("A" & i), arr(), 0)) will return us
False for if a number is returned from Match and
True if an error ( a vbError string constant ) is returned from Match

_._____________________________________

Code: Select all

Option Explicit
Sub kkk()  '    https://eileenslounge.com/viewtopic.php?f=30&t=35966
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, m As Long
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
     For i = 2 To k
        If Worksheets("Sheet1").Cells(i, 1) <> "" Then
            If IsError(Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)) Then '  test for the error
             ' Do what you want to do when no match is found
            Else
             Let m = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0) - 1
             Let Worksheets("Sheet1").Cells(i, 2) = arr1(m)
            End If
        End If
    Next i
End Sub
Alan
Last edited by Doc.AElstein on 13 Jan 2021, 12:18, edited 2 times in total.
\ -_- / :heavy: :jollyroger:

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

thnx Alan sir for your reply and help, its looks good now :clapping:

i was know the match is returning N/A but i was unbale to handle this error in code
i was try iserror but was missed line after else (infact i don't about this)

Code: Select all

Else
 m = Application.Match(Range("A" & i), arr(), 0) - 1
second i don't thing so that two if are required iserror can handle both error one if cell is blank and if match not found!
thnx for your time and help :thankyou:
Adeel

User avatar
rory
5StarLounger
Posts: 707
Joined: 24 Jan 2010, 15:56
Status: Microsoft MVP

Re: Match function error

Post by rory »

There's no point doing the same Match twice. Just do it once, assign the result to m and then test m with IsError.
Regards,
Rory

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

Hi rory sir
thnx for suggestion its also did the trick! :thankyou: :clapping:

Code: Select all

Sub kkk()

Dim arr As Variant
Dim arr1 As Variant


arr = Array(500, 1000, 1500, 2000)
arr1 = Array(15025, 1500, 1600, 17000)

k = Cells(Rows.Count, 1).End(3).Row


For i = 2 To k

m = Application.Match(Cells(i, 1), arr, 0)
If IsError(m) Then
Else
Cells(i, 2) = arr1(m - 1)

End If
Next i
End Sub

User avatar
Doc.AElstein
BronzeLounger
Posts: 1276
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Match function error

Post by Doc.AElstein »

Another variation

Code: Select all

Sub kkk_()
Dim arr As Variant
Dim arr1 As Variant

arr = Array(500, 1000, 1500, 2000)
arr1 = Array(15025, 1500, 1600, 17000)

k = Cells(Rows.Count, 1).End(3).Row

For i = 2 To k
m = Application.Match(Cells(i, 1), arr, 0)
If Not IsError(m) Then Cells(i, 2) = arr1(m - 1)
Next i
End Sub
_.__________________________________________________________-
A few variations of how I might do it....

Code: Select all

Option Explicit
Sub kkk()  '    https://eileenslounge.com/viewtopic.php?f=30&t=35966
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, m As Long
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
     For i = 2 To k
        If Worksheets("Sheet1").Cells(i, 1) <> "" Then
            If IsError(Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)) Then '  test for the error
             ' Do what you want to do when no match is found
            Else
             Let m = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0) - 1
             Let Worksheets("Sheet1").Cells(i, 2) = arr1(m)
            End If
        End If
    Next i
End Sub


Sub kkk2()  '
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, m As Long, En As Variant ' A variant can be a number or a vbError
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
     For i = 2 To k
        If Cells(i, 1) <> "" Then
         Let En = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)
            If IsError(En) Then '  test for the error
             ' Do what you want to do when no match is found
            Else
             Let m = En - 1
             Let Worksheets("Sheet1").Cells(i, 2) = arr1(m)
            End If
        End If
    Next i
End Sub

Sub kkk3()  '  forgetting the m
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, En As Variant ' A Variant can be a number or a vbError
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
     For i = 2 To k
        If Cells(i, 1) <> "" Then
         Let En = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)
            If IsError(En) Then '  test for the error
             ' Do what you want to do when no match is found
            Else
             Let Worksheets("Sheet1").Cells(i, 2) = arr1(En - 1)
            End If
        End If
    Next i
End Sub

Sub kkk4()  '  ..." .. i don't thing so that two if are required iserror can handle both error one if cell is blank and if match not found!...."...
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, En As Variant ' A Variant can be a number or a vbError
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
    For i = 2 To k
      Let En = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)
       If IsError(En) Then '  test for the error
        ' Do what you want to do when no match is found
       Else
        Let Worksheets("Sheet1").Cells(i, 2) = arr1(En - 1)
       End If
    Next i
End Sub
'
Sub kkk5()  '
Dim arr() As Variant
Dim arr1() As Variant
Dim i As Long, k As Long, En As Variant ' A Variant can be a number or a vbError
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
 Let k = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(3).Row
    For i = 2 To k
      Let En = Application.Match(Worksheets("Sheet1").Range("A" & i), arr(), 0)
       If Not IsError(En) Then Let Worksheets("Sheet1").Cells(i, 2) = arr1(En - 1)
    Next i
End Sub

Sub kkk6()  '
Dim i As Long, k As Long, En As Variant ' A Variant can be a number or a vbError
Dim arr() As Variant, arr1() As Variant
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
 Let k = Ws1.Cells(Ws1.Rows.Count, 1).End(3).Row
    For i = 2 To k
      Let En = Application.Match(Ws1.Range("A" & i), arr(), 0)
       If Not IsError(En) Then Let Ws1.Cells(i, 2) = arr1(En - 1)
    Next i
End Sub
'
Sub kkk7()  '
Dim i As Long, k As Long, En As Variant ' A Variant can be a number or a vbError
Dim arr() As Variant, arr1() As Variant
 Let arr() = Array("ll", 500, 100, 2500, 250)
 Let arr1() = Array("mm", 15025, 3325, 6565, 3333)
    With ThisWorkbook.Worksheets.Item("Sheet1") ' http://web.archive.org/web/20190628052028/http://excelmatters.com/2017/02/28/whos-with-me/
     Let k = .Cells(.Rows.Count, 1).End(3).Row
       For i = 2 To k
         Let En = Application.Match(.Range("A" & i), arr(), 0)
          If Not IsError(En) Then Let .Cells(i, 2) = arr1(En - 1)
       Next i
    End With
End Sub
\ -_- / :heavy: :jollyroger:

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

why end if isn't required in this case, please

Code: Select all

If Not IsError(m) Then Cells(i, 2) = arr1(m - 1)


when i do something like this its do required

Code: Select all

If Not IsError(m) Then
Cells(i, 2) = arr1(m - 1)

User avatar
Doc.AElstein
BronzeLounger
Posts: 1276
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Match function error

Post by Doc.AElstein »

If is all on one line, then can do it like is

If xxx Then yyy

( VBA it is knowing that it is just one line because is … Then yyy )
_._________________________

If not it is on one line then must do it like

Code: Select all

If xxx Then
yyy
Else

End if
Or

Code: Select all

If xxx Then
yyy
End if
Or

Code: Select all

If Not xxx Then

Else
yyy
End iF
Etc…


( VBA it is knowing that it is not just one line because is … Then )

If not it is on one line, VBA it not knowing where is end of it all?
So End If it do tell VBA that it is and of it
Last edited by Doc.AElstein on 13 Jan 2021, 14:19, edited 1 time in total.
\ -_- / :heavy: :jollyroger:

User avatar
HansV
Administrator
Posts: 69824
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Match function error

Post by HansV »

The first is an example of a one-line If statement. This can have the form

If condition Then action_if_true

or

If condition Then action_if_true Else action_if_false

The other one is an example of a multi-line If block. That needs an End If line:

If condition Then
action_if_true
End If

or

If condition Then
action_if_true
Else
action_if_false
End If

See Using If...Then...Else statements
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1276
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Match function error

Post by Doc.AElstein »

In Excel VBA it is like almost always many many many different ways to do things…

This last one, it do be my favourite. I likes ‘green comments, and is VBA array like as well
Is just personal choice is it
Everyone can do it differently if they like.
All is just personal choice….

Code: Select all

'
Sub Kay()  ' https://eileenslounge.com/viewtopic.php?p=279722#p279722
Rem 1 Worksheets info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Lr As Long: Let Lr = Ws1.Cells(Ws1.Rows.Count, 1).End.Item(3).Row '
Rem 2 Data info
'2a Find Replace data
Dim Cnt As Long, En As Variant ' A Variant can be a number or a vbError
Dim arr() As Variant, arr1() As Variant ' The  VBA Array function used in the next two lines, https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/array-function , returns an field of variant types, so to avoid a type mismatch error we must  Dim  appropriately
 Let arr() = Array(500, 1000, 1500, 2000)
 Let arr1() = Array(15025, 1500, 1600, 17000)
'2b Worksheet data in array I do like it, just personal choice is
Dim arrIn() As Variant: Let arrIn() = Ws1.Range("A1:A" & Lr & "").Value2 '  The  value properties, for example,  .Value2  return an array of variant types, so we must  Dim  here appropriately
Dim arrOut() As Variant: ReDim arrOut(1 To Lr, 1 To 1) ' I know the size of my output array, but  Dim  will only take numbers, so i must use  ReDim  because  ReDim  statement will take numbers or variables. I could use String type or Varaint
'Dim arrOut() As String: ReDim arrOut(1 To Lr, 1 To 1) ' This is slao OK but I may have for numbers a sign telling me that it is like that  http://i.imgur.com/OKj1kNe.jpg   http://i.imgur.com/WrOPY6O.jpg
Rem 3 Do it
    For Cnt = 2 To Lr
      Let En = Application.Match(arrIn(Cnt, 1), arr(), 0) ' This will retunn a number for the position along that the match is found , or a vBError  string constant
       If Not IsError(En) Then Let arrOut(Cnt, 1) = arr1(En - 1)
    Next Cnt
Rem 4  Paste out to worksheet
 Let Ws1.Range("B1:B" & Lr & "").Value2 = arrOut() ' I can paste out  values   from an array to a worksheet range in one go
End Sub
_._____________________--

All is just personal choice …
But be careful: Some things can be dangerous….
If you do it like
Cells(i, 1)

What is it???

Is it Worksheets(“Sheet1”).Cells(i, 1) ??

Or is it maybe

Worksheets(“Sheet2”).Cells(i, 1) ??

VBA must guess. VBA is not knowing what it is that you want
_ If VBA it do guess correctly you are lucky. It will work :)
_ If VBA it do guess wrong, then you have bad luck. It won’t work :(

But it is also your choice. You can live dangerously if you want to. This can be fun to live dangerously.

Everyone can do it differently if they like to



Alan
You do not have the required permissions to view the files attached to this post.
\ -_- / :heavy: :jollyroger:

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

thank you so much Alan and Hans sir for explanation of one liner if statement :thankyou: :thumbup:

this is working!

Code: Select all

m = Application.Match(Cells(i, 1), arr, 0)
If IsError(m) Then Else Cells(i, 2) = arr1(m - 1)

User avatar
HansV
Administrator
Posts: 69824
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Match function error

Post by HansV »

In that line, nothing is done if the condition IsError(m) is true.
Regards,
Hans

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

yes when m is true its means there is error it should go to next i thnx

one more question, in formula normally we used F9 key to see result or error etc or do evaluate formula from ribbon.

how we can do same thing in coding? practically
local window doesn't show or tell error

sorry about many questions as i am just beginner not know much about it!

Adeel

User avatar
Doc.AElstein
BronzeLounger
Posts: 1276
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Match function error

Post by Doc.AElstein »

I am not too sure what your question is??

But just some more info may help


_._______________________

Application.Match
Application.Match dose not error if no match is found. If no match is found then a vBError string constant is returned
Run this macro

Code: Select all

 Sub ApplicationMatchDoseNotErrorForNoMatch()
Dim vTemp As Variant ' A Variant can be a number or a vBError strinbg constant
 Let vTemp = Application.Match(1, Array(2, 3), 0)
Stop
End Sub
Now
_hover with mouse over vTemp : http://i.imgur.com/T8sgKZZ.jpg
Or
_select vTemp in macro and hit keys Shift+F9 : http://i.imgur.com/Ah9Kv8V.jpg
Or
Run this macro, it will tell you about it

Code: Select all

 Sub ShowApplicationMatchDoseNotErrorForNoMatch()
Dim vTemp As Variant ' A Variant can be a number or a vBError string constant
 Let vTemp = Application.Match(1, Array(2, 3), 0)
 MsgBox Prompt:=CStr(vTemp)

 Let vTemp = Application.Match(3, Array(2, 3), 0)
 MsgBox Prompt:=vTemp

End Sub
_.________________________

Application.WorksheetFunction.Match
Application.WorksheetFunction.Match does error if no match is found
Run this macro:

Code: Select all

 Sub ApplicationWorksheetFunctionMatchDoseErrorForNoMatch()
Dim vTemp As Variant ' A Variant can be a number or a vBError strinbg constant
 Let vTemp = Application.WorksheetFunction.Match(1, Array(2, 3), 0)
End Sub
It will error: http://i.imgur.com/g7Yfo5k.jpg

Or run this macro, it will tell you about it

Code: Select all

 Sub ShowErroringApplicationWorksheetFunctionMatchDoseErrorForNoMatch()
Dim vTemp As Variant ' A Variant can be a number or a vBError strinbg constant
 On Error GoTo Bed
 Let vTemp = Application.WorksheetFunction.Match(1, Array(2, 3), 0)
 On Error GoTo 0
Exit Sub

Bed:
 MsgBox prompt:=Err & vbCr & vbLf & Error ' http://i.imgur.com/N5jpUSc.jpg
End Sub
_.________________________________________


For more info on Debugging, then see here: https://eileenslounge.com/viewtopic.php ... 21#p247121
\ -_- / :heavy: :jollyroger:

User avatar
HansV
Administrator
Posts: 69824
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Match function error

Post by HansV »

You can run a macro step by step:
- Place the insertion point anywhere in the macro.
- Each time you press F8, one statement will be executed.
- While code execution is paused, you can inspect the value of variables by hovering the mouse pointer over them.
Regards,
Hans

adeel1
2StarLounger
Posts: 123
Joined: 04 Oct 2017, 15:47

Re: Match function error

Post by adeel1 »

thnx Hans and Alan sir for your help!

Adeel