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!
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,792
Office Version
365
Platform
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,003
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
75,362
Office Version
365
Platform
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,003
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:

Forum statistics

Threads
1,081,904
Messages
5,361,996
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top