Type Mismatch Error Excel VBA - Looping through each column of Used Range

Question

I have an excel spreadsheet with a title row at the top. I am trying to log the column number of each cell in the top row of my excel spreadsheet (title row) that matches a certain string such as "Recommended Action 1"

For Each c In Worksheets("Cost Estimates").Range(Cells(1, 1), Cells(totalRows, totalCols))
    If c.Value = "Recommended Action 1" Then
        recAct1 = c.Column
        Debug.Print CStr(recAct1)
    ElseIf c.Value = "Recommended Action 2" Then
        recAct2 = c.Column
    ElseIf c.Value = "Recommended Action 3" Then
        recAct3 = c.Column
    End If
Next

Where recAct holds the column number and totalRows and totalCols are the total number of rows and columns (respectively on the spreadsheet).

I keep receiving a 'Type Mismatch' error for:

If c.Value = "Recommended Action 1" Then

I put my cursor over the c value during this error and I get an 'Error 2023' message.

I suspected that it was because c was a column number and not an actual range address. I think this error is caused by the fact that I do not know what type of variable 'c' is actually returning -- I thought it was a range object.

Are you trying to find out which column in row 1 has Recommended Action 1, Recommended Action 2 and Recommended Action 3? — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-10-15T18:15:21

I think this is what you are trying?

Option Explicit

Sub Build_Formulas_v2()
    Dim RAOneCol As Long, RATwoCol As Long, RAThreeCol As Long

    RAOneCol = GetCol("Recommended Action 1")
    RATwoCol = GetCol("Recommended Action 2")
    RAThreeCol = GetCol("Recommended Action 3")

    If RAOneCol <> 0 Then _
    MsgBox "Recommended Action 1 found in column " & RAOneCol Else _
    MsgBox "Recommended Action 1 not found"

    If RATwoCol <> 0 Then _
    MsgBox "Recommended Action 2 found in column " & RATwoCol Else _
    MsgBox "Recommended Action 2 not found"

    If RAThreeCol <> 0 Then _
    MsgBox "Recommended Action 3 found in column " & RAThreeCol Else _
    MsgBox "Recommended Action 3 not found"
End Sub

Function GetCol(sString As String) As Long
    Dim aCell As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Cost Estimates")

    Set aCell = ws.Rows(1).Find(What:=sString, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        GetCol = aCell.Column
    End If
End Function

Type Mismatch Error Excel VBA - Looping through each column of Used Range

1 Answers