converting formula to macro and apply to entire sheet

I have data in sheet 1 showing my systems and the relevant activities over a calendar day

My question is , if I have more than 1 activities in one day per system this should be flagged as a conflict in sheet 2

so essentially I have 2 criterias that need to match i.e. system and date but it should only display the value if there are multiple activities

multiple activities are denoted by a  comma separator  i.e  test,bounce (test is 1 activity and bounce is another)

the following statement works  for column B

IFERROR(IF(FIND(",",OFFSET(Activities!$A$1,MATCH(B$1,Activities!$A:$A,0)-1,MATCH($A2,Activities!$1:$1,0)-1))>0,OFFSET(Activities!$A$1,MATCH(B$1,Activities!$A:$A,0)-1,MATCH($A2,Activities!$1:$1,0)-1),""),"")

I would like to invoke this via a macro for all columns not limited to column b

how can I convert this formula to get run via a macro

sample sheet attached
Conflicts-Macro.xlsx
rutgermonsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An inbuilt Excel function will always be faster than a macro so what advantage do you expect from using VBA instead?

You certainly don't want tom apply it to the whole sheet just any data entered on the sheet.

Attach an example workbook of what you mean
rutgermonsAuthor Commented:
I have attached the example to the question (conflicts.macro.xls)
Martin LissOlder than dirtCommented:
Can you show a mockup of what sheet2 should look like?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rutgermonsAuthor Commented:
Roy CoxGroup Finance ManagerCommented:
Here's the macro coverted for use in VBA

"=IFERROR(IF(FIND("","",OFFSET(Activities!R1C1,MATCH(R1C,Activities!C1,0)-1,MATCH(RC1,Activities!R1,0)-1))>0,OFFSET(Activities!R1C1,MATCH(R1C,Activities!C1,0)-1,MATCH(RC1,Activities!R1,0)-1),""""),"""")"

Open in new window


I still don't see why you need to apply it with VBA. If you convert the data in Conflicts sheet to an Excel Table, all formulas will copy down as data is added automatically, read

Overview of Excel Tables
rutgermonsAuthor Commented:
note I am planning to fill in the data in the activities sheet based on another macro, due to this, the validation of the formula/function kicks in the same time I call this macro , this then causes a big overhead and takes a long time to process
Roy CoxGroup Finance ManagerCommented:
I still cannot see how using the formula in VBA will speed it up. using the Table that I suggested formulas will autofill.

When populating new data with a macro switch calculation to manual.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you are already using macros in the workbook, why not add the code to replace the formulas also so the sheet will be free of any complex formulas.

See if the following code does what your current formula is doing. The code can be called from another macro also if required.

For details refer to the attached workbook, where I have inserted a button on the Conflicts sheet, which may be clicked to get the desired output.

Sub FindConflicts()
Dim Sws As Worksheet, Dws As Worksheet
Dim Dlr As Long, Dlc As Long
Dim i As Long, j As Long, r As Long, c As Long

Application.ScreenUpdating = False

Set Sws = Sheets("Activities")
Set Dws = Sheets("Conflicts")

Dlr = Dws.Cells(Rows.Count, 1).End(xlUp).Row
Dlc = Dws.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To Dlc
    For j = 2 To Dlr
        If WorksheetFunction.CountIf(Sws.Columns(1), Dws.Cells(1, i)) > 0 Then
            r = WorksheetFunction.Match(Dws.Cells(1, i), Sws.Columns(1), 0)
            If WorksheetFunction.CountIf(Sws.Rows(1), Dws.Cells(j, 1)) > 0 Then
                c = WorksheetFunction.Match(Dws.Cells(j, 1), Sws.Rows(1), 0)
                If InStr(Sws.Cells(r, c), ",") > 0 Then
                    Dws.Cells(j, i) = Sws.Cells(r, c)
                End If
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
MsgBox "Conflicts have been tracked successfully.", vbInformation, "Done!"
End Sub

Open in new window

Conflicts-Macro.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rutgermonsAuthor Commented:
brilliant!, thank you Sktneer! :)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I was able to help. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.