If Statement Not Firing Properly

[hillock]

Hi all,

I am trying to correct an error on many spreadsheets (100+) we have located out on a network drive. I had a formula that was incorrectly written and needs to be updated. I've written the code below to be used in a loop macro that opens up each spreadsheet and updates the formulas (if they meet my criteria).

Sub UpdateSalaryFormula()

Dim wb As Workbook
Dim sht As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim myCell As Range
Dim SearchStr As String
Dim NewFormula As String

Set wb = ThisWorkbook
Set sht = Sheets("Salary Edit")
Set tbl = sht.ListObjects("Salary")
Set rng = tbl.ListColumns(14).DataBodyRange
SearchStr = "IF(IFERROR("


With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

On Error Resume Next

For Each myCell In rng
    NewFormula = "=IF(IFERROR(SEARCH(" & _
                    Chr(34) & "On-Call" & Chr(34) & _
                    ",[@Description],1),0)>0,$M$4,IF(IFERROR(SEARCH(" & _
                    Chr(34) & "Overtime" & Chr(34) & _
                    ",[@Description],1),0)>0,INDEX(Salary,MATCH(1,(" & _
                    Chr(34) & "06 Salaries & Wages" & Chr(34) & _
                    "=[Description])*([@[Employee Name]]=[Employee Name])*([@[Period Start]]=[Period Start]),0),14)*$M$5,0))"
    
    If InStr(1, myCell.Formula, SearchStr) > 0 Then
        MsgBox myCell.Address & " has search string starting in position " & InStr(1, myCell.Formula, SearchStr) & vbNewLine & "" _
                & "The new formula is " & vbNewLine & _
                NewFormula
        myCell.FormulaArray = NewFormula
    Else
        'String not found
        GoTo NextStep
    End If


NextStep:
Next myCell


With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With


Set wb = Nothing
Set sht = Nothing
Set tbl = Nothing
Set rng = Nothing


End Sub

The problem I am running into is that I can get the MsgBox code to function properly. That is, when the condition is met (my search string is a part of the cell's formula) the msg box appears. However, I cannot get the NewFormula string to replace the array formula in the cell. I've searched all over and cannot find any other threads on this specific issue. I've gone into the actual cells I'm looping through and even copied the NewFormula string in manually, and it works. I must be doing something wrong but cannot figure out why it will not update the Array Formula in the cell. Any help would be appreciated. Thanks.

 

[Jonmo1]

The formula is too long.
The character length of an array formula entered by VBA is limited (i think 255)

Try this as the formula part

NewFormula = "=IF(COUNTIF([@Description]," & _
                    Chr(34) & "*On-Call*" & Chr(34) & _
                    "),$M$4,IF(COUNTIF([@Description]," & _
                    Chr(34) & "*Overtime*" & Chr(34) & _
                    "),INDEX(Salary,MATCH(1,(" & _
                    Chr(34) & "06 Salaries & Wages" & Chr(34) & _
                    "=[Description])*([@[Employee Name]]=[Employee Name])*([@[Period Start]]=[Period Start]),0),14)*$M$5,0))"

Basically replacing
IF(IFERROR(SEARCH("On-Call",[@Description],1),0)>0
With
IF(COUNTIF([@Description],"*On-Call*")

 

[Jonmo1]

Also just FYI (nothing wrong with the way you did it, but could be much easier)

You don't need to concatenate all those Chr(34) into the formula

Instead of
Chr(34) & "On-Call" & Chr(34)

Just write
""On-Call""

 

[hillock]

Thanks for the help! I didn't realize that the double quotes would work as well.

Also just FYI (nothing wrong with the way you did it, but could be much easier)

You don't need to concatenate all those Chr(34) into the formula

Instead of
Chr(34) & "On-Call" & Chr(34)

Just write
""On-Call""

 

[Jonmo1]

You're welcome.