If Statement Not Firing Properly

hillock

New Member
Joined
Jan 14, 2015
Messages
5
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).

Code:
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Code:
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*")
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jan 14, 2015
Messages
5
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""
 

Forum statistics

Threads
1,136,322
Messages
5,675,083
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top