error handling variables

[woods2007]

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!

 

[steve the fish]

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:

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

 

[Johnny C]

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.

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

 

[Norie]

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?

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

 

[Johnny C]

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

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

 

[woods2007]

Thanks for all the responses!

I'll give them a go!