Matching String from an Array to a Range Excel VBA

Question

I have the following Public sub:

Public Sub HowToSort()
Dim i As Long, j As Long, h As Long, curCell As Range, cellBelow(1 To 10) As Variant
Dim sortOrder(1 To 10), colIsString(1 To 10) As Variant

For i = 1 To hdrCount
    'Find location of a cell
    Set curCell = Application.WorksheetFunction.Match(headRow(i), Range("a1:z1"))    ' Eventually extend outwards?
    cellBelow(i) = curCell.Offset(0, 1).Value
    If IsNumeric(cellBelow(i)) = False Then
        colIsString(i) = True
        sortOrder(i) = Application.InputBox(prompt:="Alphabetical = 'True' or Reverse Alphabetical = 'False' sorting for " & headRow(i), Type:=4)
    ElseIf IsNumeric(cellBelow(i)) = True Then
        colIsString(i) = False
        sortOrder(i) = Application.InputBox(prompt:="Ascending = True or Descending = False for " & headRow(i), Type:=4)
    Else
        MsgBox ("Program does not recognize value contained in column" & headRow(i))
        End
    End If
Next i
End Sub

Which uses a global variable named headRow, containing an array of strings of names of the header row at the top of the worksheet. I am trying to use the match function to find the address of the cell where the header is located:

Set curCell = Application.WorksheetFunction.Match(headRow(i), Range("a1:z1"))
cellBelow(i) = curCell.Offset(0, 1).Value

I then want to use this address, offset it downwards one cell to find what type of data is entered, this data will be entered in array colIsString. However, the .Match function is not working, citing a 'Type Mismatch' error. I do not know how this could be? From my previous research it appears that the .Match command takes in a range, then searches that range to match a cell value. I have tried several incarnations of the .Match command with no success. Your thoughts appreciated...

H3lue

Tim Williams · Accepted Answer · 2011-08-05 16:57:07Z

3

Use Find() instead:

Set curCell = Range("a1:z1").Find(headRow(i), , xlValues, xlWhole)
If Not curCell Is Nothing Then
    'found the header
    cellBelow(i) = curCell.Offset(0, 1).Value
    'etc etc
Else
    MsgBox "Header '" & headRow(i) & "' not found!"
End If

sortOrder and colIsString will go out of scope as soon as your sub exits though...

answered Aug 5, 2011 at 16:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Matching String from an Array to a Range Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related