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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
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
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

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