I have a macro enabled document that contains a table. In cell A7 of the table it…
- User
I have a macro enabled document that contains a table. In cell A7 of the table it captures a sentence. I want the last word in the sentence to appear in cell B8 of the table. How can I do this. Here is the Word Document VBA: Sub AppendCellValuesToTable() 'Get a reference to the Excel Application object Set xlApp = GetObject(, "Excel.Application")
'Get a reference to the active Workbook object Set xlWB = xlApp.ActiveWorkbook
'Get the values of cells A1, B1, C1, and D1 from the active Excel sheet using the Excel object model cellA1 = xlApp.ActiveWorkbook.ActiveSheet.Range("B1").Value cellB1 = xlApp.ActiveWorkbook.ActiveSheet.Range("M1").Value cellA2 = xlApp.ActiveWorkbook.ActiveSheet.Range("H2").Value cellB2 = xlApp.ActiveWorkbook.ActiveSheet.Range("M3").Value cellA3 = xlApp.ActiveWorkbook.ActiveSheet.Range("H1").Value cellB3 = xlApp.ActiveWorkbook.ActiveSheet.Range("M5").Value cellA4 = xlApp.ActiveWorkbook.ActiveSheet.Range("I2").Value cellB4 = xlApp.ActiveWorkbook.ActiveSheet.Range("M2").Value cellA5 = xlApp.ActiveWorkbook.ActiveSheet.Range("M9").Value 'cellB5 = xlApp.ActiveWorkbook.ActiveSheet.Range("C1").Value cellA6 = "" cellB6 = "" cellA7 = xlApp.ActiveWorkbook.ActiveSheet.Range("M11").Value 'cellB7 = xlApp.ActiveWorkbook.ActiveSheet.Range("C1").Value cellA8 = "Purchase Request No.:" cellB8 = Trim(Right(SUBSTITUTE(A7, " ", "#", Len(A7) - Len(SUBSTITUTE(A7, " ", ""))), Len(A7) - Find("#", SUBSTITUTE(A7, " ", "#", Len(A7) - Len(SUBSTITUTE(A7, " ", ""))))))
'Get the Word table and append the values to the corresponding cells With ActiveDocument.Tables(1) 'Row 1 .Cell(1, 1).Range.Text = cellA1 .Cell(1, 2).Range.Text = cellB1
'Row 2 .Cell(2, 1).Range.Text = cellA2 .Cell(2, 2).Range.Text = cellB2
'Row 3 .Cell(3, 1).Range.Text = cellA3 .Cell(3, 2).Range.Text = cellB3
'Row 4 .Cell(4, 1).Range.Text = cellA4 .Cell(4, 2).Range.Text = cellB4
'Row 5 .Cell(5, 1).Range.Text = cellA5 '.Cell(5, 2).Range.Text = cellB5
'Row 6 .Cell(6, 1).Range.Text = cellA6 .Cell(6, 2).Range.Text = cellB6
'Row 7 .Cell(7, 1).Range.Text = cellA7 '.Cell(7, 2).Range.Text = cellB7
'Row 8 .Cell(8, 1).Range.Text = cellA8 .Cell(8, 2).Range.Text = cellB8
End With
End Sub
- Assistant
In order to get the last word in the sentence in cell A7 and display it in cell B8 of the table, you can modify the existing code as follows:
Replace the line:
cellB8 = Trim(Right(SUBSTITUTE(A7, " ", "#", Len(A7) - Len(SUBSTITUTE(A7, " ", ""))), Len(A7) - Find("#", SUBSTITUTE(A7, " ", "#", Len(A7) - Len(SUBSTITUTE(A7, " ", ""))))))with:
Dim words() As String words = Split(cellA7, " ") cellB8 = words(UBound(words))This code snippet splits the sentence in cell A7 using space as the delimiter and stores the words in an array. Then, it assigns the last word from the array to cell B8.
After making this change, when you run the macro, the last word from the sentence in cell A7 will be displayed in cell B8 of the table.