VBA Code Error Unable to get the Match property of the WorksheetFunction class

[Jiraya_00]

Hi Everyone,

I'm trying to create a macro that will run through a set of column headers and look for a specific header ("CMA - ERP Variance") and calculate a simple formula for all the cells within that column header. However, I'm running into an error with my code as I get the "Run-time error '1004': Unable to get the Match property of the WorksheetFunction class."

Can anyone help me debug this error and let me know what I'm doing wrong? My code is listed below. Thanks for all the help!

Sub Test_MatchReplace()

Dim TestHeader As Long
Dim TestCel As Range, TestCel2 As Range, TestCel3 As Range, TestHeader2 As Range, TestURange As Range
Dim ws As Worksheet

Set ws = ShTest

 With Application
    .StatusBar = "Your macro is running"
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    
 End With
 
' To find and replace certain original headers on test data report
 TestHeader = WorksheetFunction.Match("Amount", ShTest.Rows(1), 0)
    Columns(TestHeader).Select
        Selection.Replace What:="Amount", Replacement:="CMA Amount", lookat:=xlWhole _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
 TestHeader = WorksheetFunction.Match("Balance", ShTest.Rows(1), 0)
    Columns(TestHeader).Select
        Selection.Replace What:="Balance", Replacement:="CMA Balance", lookat:=xlWhole _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
 
' To create a formula for the "CMA - ERP Variance" column
 TestHeader2 = ShTest.Range(Cells(1, 1), Cells(1, ShTest.Cells(1, Columns.Count).End(xlToLeft).Column))
    
    For Each TestCel In TestHeader2
        If TestCel.Value = "CMA - ERP Variance" Then
            For Each TestCel2 In ShTest.Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)
                If Not IsEmpty(TestCel2.Value) Then
                    TestCel2.Offset(, 2).Formula = "=(RC[-2]-RC[-1])"
                Else
                    TestCel2.Offset(, 2).Value = 0
                End If
            Next TestCel2
        End If
    Next TestCel
 
' To highlight any cells within the "CMA - ERP Variance" column that has a value not equal to 0

 With ShTest
    Set TestURange = .UsedRange
    Set TestURange = .Range("A2").Resize(RowSize:=TestURange.Rows.Count - 1, ColumnSize:=TestURange.Columns.Count)
    For Each TestCel3 In TestURange
        If TestCel3.Column = 15 And TestCel3.Value <> 0 Then
            TestCel3.Interior.ColorIndex = 6
        End If
    Next TestCel3
 End With
 
 With Application
    .StatusBar = ""
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = False
    
 End With

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel

I suppose the error is in this line and that is because, according to your image, the data "Amount" does not exist in row 1.

TestHeader = WorksheetFunction.Match("Amount", ShTest.Rows(1), 0)

To avoid this error you can use the find method.
The code can be simplified like this:

Sub Test_MatchReplace()
  Dim f As Range, c As Range
  
  Application.ScreenUpdating = False
  
  ' To find and replace certain original headers on test data report
    Set f = ShTest.Range("1:1").Find("Amount", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then f.Value = "CMA Amount"
    Set f = ShTest.Range("1:1").Find("Balance", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then f.Value = "CMA Balance"
  
  ' To create a formula for the "CMA - ERP Variance" column and highlight cell not equal to 0
    Set f = ShTest.Range("1:1").Find("CMA - ERP Variance", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      With f.Offset(1).Resize(ShTest.Range("M" & Rows.Count).End(3).Row - 1)
        .Formula = "=M2-N2"
        For Each c In .Cells
          If c.Value <> 0 Then
            c.Interior.ColorIndex = 6
          End If
        Next
      End With
    End If

  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Peter_SSs]

Welcome to the MrExcel board!

My code is listed below.

Is that really your code?
If it is, it should never get to the error you mention as it will error at the highlighted line below because ShTest has not been set to anything.

Sub Test_MatchReplace()

Dim TestHeader As Long
Dim TestCel As Range, TestCel2 As Range, TestCel3 As Range, TestHeader2 As Range, TestURange As Range
Dim ws As Worksheet

Set ws = ShTest

BTW, this line is also not a valid line

TestHeader2 = ShTest.Range(Cells(1, 1), Cells(1, ShTest.Cells(1, Columns.Count).End(xlToLeft).Column))

Since TestHeader2 is declared as a Range, the line should be ..

Set TestHeader2 = ShTest.Range(Cells(1, 1), Cells(1, ShTest.Cells(1, Columns.Count).End(xlToLeft).Column))

.. and that line would still only work if ShTest (whatever that is) is the ActiveSheet.

As Dante has said, "Amount" is not found in row 1 of your image - but perhaps your image was taken after the code had already been run or part run and "Amount" had already been replaced by "CMA Amount"?

I have not checked the code any further as I think we need your actual code and confirmation of which line causes the error.

It would also be helpful for our testing if you could provide the sample data with XL2BB rather than as an image that we cannot copy from.