Inserting a Formula into Cell with table reference using VBA

Question

I'm trying to insert a formula into a cell so a variable that is captured with a MsgBox function is inserted into the formula.

TmpPT = "=IF(NOT(ISBLANK(INDEX(" & "Table10[Recvd Date]" & ",MATCH("" * """ & prjNum & """ * "" ," & "Table10[Project Name]" & ",0)))),INDEX(" & "Table10[Recvd Date]" & ",MATCH("" * """ & prjNum & """ * ""," & "Table10[Project Name]" & ",0))," & "Not Yet Received" & ")"

The overall objective for my code below is to unhide another sheet and insert data into a range within that sheet. If irrelevant, please disregard the information.

Private Sub CommandButton1_Click()
Dim SetPT As Variant
Dim TmpStg As Variant
Dim r As Integer
Dim c As Integer
Dim prjNum As Variant
Dim titlerng As Range
Dim outptrng As Range

r = 0
c = 0

SetPT = "A1"

Sheets("MG Rpt").Visible = True
Sheets("MG Rpt").Activate
ActiveSheet.Range(SetPT).Select

r = 8
ActiveCell.Offset(r, c).Activate
ActiveCell.Value = "Project #:"
ActiveCell.Offset(1, c).Activate
ActiveCell.Value = "Received Date:"
ActiveCell.Offset(1, c).Activate
ActiveCell.Value = "Elapsed Time:"
ActiveCell.Offset(1, c).Activate
ActiveCell.Value = "Expected Completion:"
ActiveCell.Offset(1, c).Activate
ActiveCell.Value = "Feedback:"

prjNum = InputBox("Please enter the Project # you are requesting down below.", "Project Search Query")
ActiveCell.Offset(-4, 1).Value = prjNum
ActiveCell.Offset(-4, 0).Activate
TmpStg = "=IF(NOT(ISBLANK(INDEX(" & "Table10[Recvd Date]" & ",MATCH("" * """ & prjNum & """ * "" ," & "Table10[Project Name]" & ",0)))),INDEX(" & "Table10[Recvd Date]" & ",MATCH("" * """ & prjNum & """ * ""," & "Table10[Project Name]" & ",0))," & "Not Yet Received" & ")"

Range("B10").Formula = TmpPT

End Sub

Am I missing an object or am I failing to format a line? Learning VBA as I go :)

The formula should look like this:

=IF(NOT(ISBLANK(INDEX(Table10[Recvd Date],MATCH("*"&prjNum&"*",Table10[Project Name],0)))),INDEX(Table10[Recvd Date],MATCH("*"&prjNum&"*",Table10[Project Name],0)),"Not Yet Received")

Solution:

Got it! Here's the final output that got it to work:

TmpStg = "=IF(NOT(ISBLANK(INDEX(Table10[Recvd Date],MATCH(""*""&" & prjNum & "&""*"",Table10[Project Name],0)))),INDEX(Table10[Recvd Date],MATCH(""*""&" & prjNum & "&""*"",Table10[Project Name],0)),""Not Yet Received"")"

Thanks for the help everyone!

change `""" & prjNum & """` to `" & prjNum & "` or `"" & " & prjNum & " & ""` — Scott Craner, Jun 25 '18 at 14:13
I've changed '""" & prjNum & """' to both options and it only returns to the ActiveCell. The formula isn't being inserted to the cell. Did I miss something? @ScottCraner — Fanfir, Jun 25 '18 at 14:28
and `," & "Not Yet Received" & ")"` to `,""Not Yet Received"")"` but the easiest way is to show us what it is supposed to look like. — , Jun 25 '18 at 14:31
Thanks, @Jeeped It should look like this '=IF(NOT(ISBLANK(INDEX(Table10[Recvd Date],MATCH("*"&prjNum&"*",Table10[Project Name],0)))),INDEX(Table10[Recvd Date],MATCH("*"&prjNum&"*",Table10[Project Name],0)),"Not Yet Received")' — Fanfir, Jun 25 '18 at 14:41
** please edit your question to include the formula prototype in a code block (i.e. with 4 spaces at the left side) — , Jun 25 '18 at 14:45

score 0 · Accepted Answer · answered Jun 25 '18 at 15:26

Try Avoiding Select and Activate while minimizing the string concatenation down to what is absolutely necessary.

Option Explicit

Private Sub CommandButton1_Click()
    Dim tmpStg As Variant, prjNum As Variant

    prjNum = InputBox("Please enter the Project # you are requesting down below.", "Project Search Query")
    tmpStg = "=IF(LEN(INDEX(Table10[Recvd Date], MATCH(""*" & prjNum & "*"", Table10[Project Name], 0))), " & _
                             "INDEX(Table10[Recvd Date], MATCH(""*" & prjNum & "*"", Table10[Project Name], 0)), ""Not Yet Received"")"

    Debug.Print tmpStg

    With Worksheets("MG Rpt")
        .Visible = True

        .Cells(9, "A").Resize(5, 1) = _
          Application.Transpose(Array("Project #:", "Received Date:", "Elapsed Time:", _
                                      "Expected Completion:", "Feedback:"))

        .Cells(9, "B") = prjNum
        .Cells(10, "B").Formula = tmpStg
        .Cells(10, "B").NumberFormat = "mm/dd/yyyy"

        .Activate
    End With

End Sub

Do you really need to bracket wildcard the project number?

Thanks, @Jeeped! Was unaware that I could do more than one method. Unfortunately yes, the array that the function looks up against is alphanumeric. — Fanfir, Jun 25 '18 at 15:47

Inserting a Formula into Cell with table reference using VBA

1 Answers1