Today we are utilizing a formula in SharePoint that we go update by adding to it and wanted to see if VBA in an excel sheet could help out. The formula is as follows:=OR(ISNUMBER(FIND("PartA",[Item Number])),ISNUMBER(FIND("PartB",[Item Number])),ISNUMBER(FIND("PartC",[Item Number]))). If possible i would like to make an excel chart that has a column called Part that lists PartA, PartB... PartXX. Then have VBA take this column and spit out into a cell or onto the clipboard the updated formula that we can paste into sharepoint.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Maybe something like this? I have the script below set up to insert the formula in the column directly, but you could use a MsgBox or something else to show the PartsFormula.
I named the main table with the formula "Main", but you can adjust below. I also created an ItemListings table with an ItemListings column with the parts listed.
I tried to comment what might need to be changed, but make sure all the listobject and sheet names have been changed accordingly.
I named the main table with the formula "Main", but you can adjust below. I also created an ItemListings table with an ItemListings column with the parts listed.
I tried to comment what might need to be changed, but make sure all the listobject and sheet names have been changed accordingly.
VBA Code:
Sub ItemListingsFormulaUpdate()
Dim MainWorksheet As Worksheet, MainTable As ListObject
Dim MainTableFormulaColumn As ListColumn
Dim ItemListingsTable As ListObject, ItemListingsRange As Range
Dim ItemListing As Range
Dim PartsFormula As String, PartsFormulaOrList As String
' Change "johd917" below to the name of your main sheet
Set MainWorksheet = ThisWorkbook.Sheets("jhod917")
With MainWorksheet
Set MainTable = .ListObjects("Main")
Set ItemListingsTable = .ListObjects("ItemListings")
Set ItemListingsRange = ItemListingsTable.ListColumns(1).DataBodyRange
End With
PartsFormula = "=OR(ISNUMBER(FIND(""" & _
WorksheetFunction.TextJoin(""",[Item Number])),ISNUMBER(FIND(""", _
True, ItemListingsRange) & """,[Item Number])))"
Debug.Print PartsFormula
' Change "Formula" to name of column that uses the formula
Set MainTableFormulaColumn = MainTable.ListColumns("Formula")
MainTableFormulaColumn.DataBodyRange.Formula2 = PartsFormula
End Sub
Upvote
0
Thank you for this! as i was able to get it to work.. how would i go about setting it up to display in a MsgBox as you mentioned above? Figure i will make the file be the list with a button to hit to run the macro and it display the formula in a msgbox.
Thanks again
Upvote
0
I will add the MsgBox to the same code below, and comment out the lines that post the formula into a table column.
VBA Code:
Sub ItemListingsFormulaUpdate()
Dim MainWorksheet As Worksheet, MainTable As ListObject
Dim MainTableFormulaColumn As ListColumn
Dim ItemListingsTable As ListObject, ItemListingsRange As Range
Dim ItemListing As Range
Dim PartsFormula As String, PartsFormulaOrList As String
' Change "johd917" below to the name of your main sheet
Set MainWorksheet = ThisWorkbook.Sheets("jhod917")
With MainWorksheet
Set MainTable = .ListObjects("Main")
Set ItemListingsTable = .ListObjects("ItemListings")
Set ItemListingsRange = ItemListingsTable.ListColumns(1).DataBodyRange
End With
PartsFormula = "=OR(ISNUMBER(FIND(""" & _
WorksheetFunction.TextJoin(""",[Item Number])),ISNUMBER(FIND(""", _
True, ItemListingsRange) & """,[Item Number])))"
Debug.Print PartsFormula
' Remove one apostrophe from each line below to post into column
'' Change "Formula" to name of column that uses the formula
'Set MainTableFormulaColumn = MainTable.ListColumns("Formula")
'MainTableFormulaColumn.DataBodyRange.Formula2 = PartsFormula
' Use MsgBox to display the formula
MsgBox PartsFormula
End SubFeel free to ask more questions, or if one of these works for you, you can mark one of them as a solution if you like.
Upvote
0
Ahh i see.. yeah i need the ability to copy the formula to paste into sharepoint so the msgbox, which looks nice, would not work..
Upvote
0
This feels backwards, because normally an InputBox is how we get some input from the user, but we can also use the InputBox to display the formula as the default value:
VBA Code:
Sub ItemListingsFormulaUpdate()
Dim MainWorksheet As Worksheet, MainTable As ListObject
Dim MainTableFormulaColumn As ListColumn
Dim ItemListingsTable As ListObject, ItemListingsRange As Range
Dim ItemListing As Range
Dim PartsFormula As String, PartsFormulaOrList As String
Set MainWorksheet = ThisWorkbook.Sheets("jhod917") ' Change this
With MainWorksheet
Set MainTable = .ListObjects("Main")
Set ItemListingsTable = .ListObjects("ItemListings")
Set ItemListingsRange = ItemListingsTable.ListColumns(1).DataBodyRange
End With
PartsFormula = "=OR(ISNUMBER(FIND(""" & _
WorksheetFunction.TextJoin(""",[Item Number])),ISNUMBER(FIND(""", _
True, ItemListingsRange) & """,[Item Number])))"
Debug.Print PartsFormula
'' Change "Formula" to name of column that uses the formula
'Set MainTableFormulaColumn = MainTable.ListColumns("Formula")
'MainTableFormulaColumn.DataBodyRange.Formula2 = PartsFormula
' This feels backwards, but show in InputBox for copying
Dim FormulaDisplay As String
FormulaDisplay = InputBox("Please copy formula from box below.", _
"Copy Formula", PartsFormula)
End Sub
Upvote
0
Thank you for this as it is thinking outside the box, i did noticed the issue as the inputbox must be character limited as when you have 29 entries for itemlistings it will not output the full string. Thinking i can use the previous where it is put into a cell and just simply copy and paste out of that though.
Thanks for all the help!!
Upvote
0
