Adding to existing formula

[kod2th3e]

I am trying to get user input from an input box and add that value to an existing formula. So far, I have been able to get the user input to work but am not sure how to add it to the formula.

Dim strnewassembly As String
strnewassembly = InputBox("Please enter the Assembly Number you would like to add to the " & _
    "stryker list.", "Add New Assembly?")

So far the existing formula looks like:

ActiveCell.FormulaR1C1 = _
        "=IF(OR(RC[-7]=""16102088-041"",RC[-7]=""16101752-051"",RC[-7]=""16102030-081"",RC[-7]=""16102200-021"",RC[-7]=""16103880-021"",RC[-7]=""16101860-051"",RC[-7]=""7566394-011"",RC[-7]=""16104847-011"",RC[-7]=""7566394-011SP"",RC[-7]=""16101157-021"",RC[-7]=""7566139-121"",RC[-7]=""7579575-011"",RC[-7]=""16103822-011"",RC[-7]=""16104813-011"",RC[-7]=""16102218-061"",RC[-7]=""7566131-121"",RC[-7]=""16103867-011"",RC[-7]=""16102218-061SP"",RC[-7]=""16102347-011"",RC[-7]=""16102325-011"",RC[-7]=""16102328-011"",RC[-7]=""16103534-021"",RC[-7]=""7566398-011"",RC[-7]=""16101860-051SP"",RC[-7]=""16102360-011"",RC[-7]=""16103344-011"",RC[-7]=""16102372-011"",RC[-7]=""16101087-031"",RC[-7]=""1234567890"")=TRUE,""Stryker"","""")"

What I would like to do is after the last statement of RC[-7]=""1234567890"" I'd like to add the following:

,RC[-7]strnewassembly

Any thoughts/help/ideas would be greatly appreciated, thanks for looking.

-Cody

 

[SteveO59L]

Try posting a sample of your data, and what you want to do with it.

It will probably be possible to tabulate your data, instead of using a long if statement.

 

[kod2th3e]

Cell J4 is a user input and I want cell J5 to say "Stryker" if J4 gets populated with an assembly number that is within the formula stated above. Because we add new assemblies from time to time I would like to create a macro that will update the formula in cell J5 with an input box that allows the user to click a button to update the formula. I have the sheet protected so it'll unprotect the sheet, update the formula (which I haven't figured out how yet), then re-protect the sheet.

 

[AlphaFrog]

Try something like this...

ActiveCell.FormulaR1C1 = Replace(ActiveCell.FormulaR1C1, ")=TRUE", ",RC[-7]=""" & strnewassembly & """)=TRUE")

 

[kod2th3e]

Works perfect! Thanks everyone for your help, I appreciate it.

 

[AlphaFrog]

Just another thought, it may be easier to have a column of assembly numbers that the user can add to and then have the J5 formula check if the value in J4 is in the list.

Say your assembly numbers list is in column K, put this in J5...

=IF(COUNTIF(K:K, J4)>0, "Stryker", "")