Help converting a formula into a Macro

[MJA001]

In my worksheet Project, for the populated rows in column C, I use this index match formula to bring the notes from one WS into a consolidated WS: =INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0))

Can someone help me rewrite this in VBA? I'd like to execute this with a quick button instead of manually copying it to every cell.

In terms of my spreadsheet, here's what function I need.

I need to find the matching Project ID numbers from WS "Project" in Column A , with the Project ID numbers in WS "Project2" Column G. Once the match is found, I need the notes from WS "Project2" in column B copied into the corresponding cell in WS "Project" column C .

Thanks in advance for any help. Please let me know if you need more info.

 

[MartyS]

Try this:

Sub EnterFormulaArray()


    Const Sheetname = "Project"


    Dim lastrow As Integer
    
    With Sheets(Sheetname)
    
        lastrow = .Range("A:A").CurrentRegion.Rows.Count
    
        .Range("C1:C" & lastrow).FormulaArray = "=INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0))"
        


    End With


End Sub

 

[MJA001]

Thanks! That worked great for copying the formula to each cell. However, I need the data from the index match to be populated, so I can export it later. Right now when I export it, it gives me an error. How do I get just the result of the formula to be populated?

 

[MartyS]

I'm not sure I follow what you mean by "I need it to be populated"

When you run the code, if the formula is entered into the worksheet, is it returning values?


If so, do you mean when you copy and paste it you're getting errors?

When you copy and paste it, are you pasting using PASTESPECIAL - VALUES??


Alternatively, this will work (if the formula is populating correctly in the first place).

Sub EnterFormulaArray()


    Const Sheetname = "Project"


    Dim lastrow As Integer
    
    With Sheets(Sheetname)

        .Select    
        lastrow = .Range("A:A").CurrentRegion.Rows.Count
        .Range("C1:C" & lastrow).FormulaArray = "=INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0))"
        .Range("C1:C" & lastrow).Copy
        .Range("C1:C" & lastrow).PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False


    End With


End Sub

 

[MJA001]

Thanks for the help. What I mean by "I need it to be populated" is that I need the result of the formula to be populated in the cell, not the formula itself. In my case the result should just be a text answer of 01, 02, 03 etc... Then I use an If, Then look up to compile and export data from multiple worksheets based on that 01,02,03 etc... I need the formula bar of each cell to be: 01, 02, 03 etc..., but what I'm getting in the formula bar is =INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0)) .

Here's the scenario where I'm getting the error: When I run my If /Then macro to populate the 03 report nothing shows up, and the macro is stopped with a Run-time error '13': Type mismatch. I think it's because the macro is searching for 03, but it's only finding the formula.


Also on a side note, because the formula is copied into all the cells, the macro takes several minutes to process while the hundreds of formulas run simultaneously.

 

[MartyS]

Did you try the amended code?

 

[MartyS]

Try this instead:

Sub EnterFormulaArray()


    Const Sheetname = "Project"
    Const Sheetname2 = "Project2"


    Dim lastrow As Integer
    
    With Sheets(Sheetname)
    
        .Select
    
        lastrow = .Range("A:A").CurrentRegion.Rows.Count
    
        For cntr = 1 To lastrow
    
            On Error Resume Next
            x = Application.WorksheetFunction.Index(Sheets(Sheetname2).Range("B:B"), Application.WorksheetFunction.Match(Sheets(Sheetname).Range("A" & cntr), Sheets(Sheetname2).Range("G:G"), 0))
            If Err.Number <> 0 Then x = ""
            On Error GoTo 0
        
            Sheets(Sheetname).Range("D" & cntr).Value = x
        Next cntr
    
    End With


End Sub

 

[MJA001]

You're awesome! That worked perfectly. Thanks for your patience.

 

[MartyS]

Thank you! Glad to have helped.