error handling variables

woods2007

Board Regular
Joined
Aug 29, 2007
Messages
57
Hi,

Can anyone help with the following?

I have some code below that doesn't seem to work. My first question is why? and my second question is- is there a better way of solving this problem as the client seems to alternate between values for 'Forename' eg 'Firstname', 'First name', 'InitialsForename', 'FN'?


If IsError(Application.WorksheetFunction.Match("Forename", HeaderRange, 0)) = True Then

FN = Application.WorksheetFunction.Match("First Name", HeaderRange, 0)

Else: FN = Application.WorksheetFunction.Match("Forename", HeaderRange, 0)

End If

Any help would be great

...still learning!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Easiest way is to tell the client if they want to avoid mistakes then to be consistent. Failing that this will loop through the various names in the array you will have to build and see if any matches are made:

Code:
NameArr = Array("Forename", "Firstname", "First name", "InitialsForename", "FN")
'Set HeaderRange = Range("HeaderRange")

For i = LBound(NameArr) To UBound(NameArr)
    If IsNumeric(Application.Match(NameArr(i), HeaderRange, 0)) Then
        MsgBox "found it"
        Exit For
    End If
Next
 
Upvote 0
I don't know why that wouldn't work, you could try avoiding the use of the application worksheet function match.
Dim a variable as a range, and another as a long.
Then Set a range as follows.

Code:
Dim rngFindFN as Range, lngFNRow&
Set rngFindFN = Range("HeaderRange").Find("Forename")
If rngFindFN is Nothing Then
    Set rngFindFN = Range("HeaderRange").Find("First Name")
    If rngFindFN is Nothing then
        Set rngFindFN = Range("HeaderRange").Find("InitialsForename")
        ... keep on nesting your tests
    End If
End IF
lngFNRow = rngFindFN.Row
 
Upvote 0
One thing you could do is use Application.Match instead of Application.WorksheetFunction.Match.

If you do that if there is no match the code won't error but you'll be able to check.

As for dealing with alternate values, perhaps you could use an array of possible values or try a wildcard match?
Code:
Dim Res As Variant
Dim arrPossHeads As Variant

    arrPossHeads = Array("Forename", "Firstname", "First name", "FN")

    For I = LBound(arrPossHeads) To UBound(arrPossHeads)

        Res = Application.Match(arrPossHeads(I), HeaderRange, 0)

        If Not IsError(Res) Then
            FN = Res
            Exit For
        End If

    Next I
 
Upvote 0
Easiest way is to tell the client if they want to avoid mistakes then to be consistent.
Lol, if only suggestions like that ever worked :)
The source data might come from different systems. in which case it might be better to put the possibilities into a dynamic range and intersect that, so any additional possibilities that crop up could be catered for in the future by adding them to the list.
Say the dynamic range is called FNNames
Code:
Dim rngFN As Range
Set rngFN = Application.Intersect(Range("FNNames"),Range("HeaderRange"))
If rngFN is Nothing Then FN = "" Else FN = rngFN.Cells(1, 1).value
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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
Back
Top