Macro to run an Array

[p9j123]

I need an experts advise please. I have a formula that needs to be populated on multiple cells of column G, can someone assist me on how to create a macro to accomplish this? below is the macro for your reference.

{=INDEX($O$2:$O$5000,MATCH(1,($A$2:$A$5000=A32)*($E$2:$E$5000=C32)*($C$2:$C$5000=J32),0))}

Those in red should change depending on the row number where the formula is placed, so if the formula is placed in G32 then those in red should be 32, if G50 then it should be 50 and so on and so forth.

I tried running using looping, I was able to populate the formula to the desired cells but those in red are fixed I cant make it change based on the row numbers.

 

[Marcelo Branco]

Maybe this

Select the cell where you want to insert the formula and run the macro below

Sub aTest()
    Dim strFormula As String
    
    strFormula = Replace("=INDEX($O$2:$O$5000,MATCH(1,($A$2:$A$5000=A@)*($E$2:$E$5000=C@)*($C$2:$C$5000=J@),0))", _
                        "@", ActiveCell.Row)
    
    ActiveCell.FormulaArray = strFormula
End Sub

Hope this helps

M.

 

[p9j123]

Hello M,

Thank you for this, however the highlighted were fixed to the active cell, the goal is to change the highlighted text depending on the row number when I run the macro with a loop.

{=INDEX($O$2:$O$5000,MATCH(1,($A$2:$A$5000=A32)*($E$2:$E$5000=C32)*($C$2:$C$5000=J32),0))}


Below is the complete macro that I run using the formula that you provided.


Sub Cleaner_data()


Dim wkb As Workbook, wkb2 As Workbook, wks As Worksheet, wks2 As Worksheet, j As Long, I As Long
Dim wf As WorksheetFunction
Dim strFormula As String

Set wf = Application.WorksheetFunction
Set wkb = ActiveWorkbook
Set wks = ActiveSheet
Set wkb2 = Workbooks("DataCleaner v2.xlsm")
Set wks2 = wkb2.Worksheets("Sheet1")

'copying the date from cleaner

For I = wks.Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
wks.Cells(I, 1) = wks2.Cells(8, 2)

strFormula = Replace("=INDEX($O$2:$O$5000,MATCH(1,($A$2:$A$5000=A@)*($E$2:$E$5000=C@)*($C$2:$C$5000=J@),0))", _
"@", ActiveCell.Row)

Cells(I, 7).FormulaArray = strFormula
Next I
End Sub

 

[Marcelo Branco]

I'm confused about your formula. I think this condition ($A$2:$A$5000=A32) is not correct because A32 is inside the range $A$2:$A$5000

Could you, please, clarify ,or better, post a small data sample and expected results?

M.

 

[p9j123]

$A$2:$A$5000 is actually from different sheet, I just remove the sheet just to simulate if the highlighted text will run.....

Anyway, thanks for your help your formula is a great help I just tweaked it a little to make it work the way I need it to be.

For the reference of other user like me that might be needing this, below is the complete macro.

[/COLOR]Sub multi_ops()
' macro to capture the validated time for multiple ops


Dim wkb As Workbook, wkb2 As Workbook, wks As Worksheet, wks2 As Worksheet, j As Long, I As Long
    Dim wf As WorksheetFunction
     Dim strFormula As String
    
    
    Set wf = Application.WorksheetFunction
    Set wkb = ActiveWorkbook
    Set wks = ActiveSheet
    Set wkb2 = Workbooks("DataCleaner v2.xlsm")
    Set wks2 = wkb2.Worksheets("Sheet1")
        
    'copying the date from cleaner
    
    For I = wks.Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    Cells(I, 5).Select
          
  
       
       
       strFormula = Replace("=INDEX('[ALL-IN-ONE Log.xlsx]T.O. Log'!$O$2:$O$5000,MATCH(1,('[ALL-IN-ONE Log.xlsx]T.O. Log'!$A$2:$A$5000=A@)*('[ALL-IN-ONE Log.xlsx]T.O. Log'!$E$2:$E$5000=C@)*('[ALL-IN-ONE Log.xlsx]T.O. Log'!$C$2:$C$5000=J@),0))", _
                        "@", ActiveCell.Row)
              
    Cells(I, 15).FormulaArray = strFormula
    Next I


End Sub
[COLOR=#333333]

I added

Cells(I, 5).Select

to make it work the way I want it....


Again thank you so much.

 

[Marcelo Branco]

You are welcome.

I'm glad that you solved your problem.

M.