I am using the macro below to transfer data from a form to a matrix. Some of the fields on the form are being combined into one cell within the matrix this all works great. Now I need to try and format specific words within the combined cell. Some will be formatted as text and some will be formatted as bold. For example Cells B1, B2, and B5 are being combined into once cell. I need to italicize B1 and Bold B5 within the one cell of the matrix so that they look like this in the matrix.
Value of B1
Value of B2
Value of B5
I was trying to store the values of the cells that I need to have formatted as variables and then use the Find function to locate the word in the cell after the transfer but had no luck figuring it out. I also tried to format the value of the cell and store it as a variable with the formatting but that was not working either. Copy and Paste wont work because the Form does not contain any formatting.
The following Macro is set to work from the Personal Macro Workbook. Comments are included so that co workers no what is going on and how.
Sub Transfer_Click()
'This Macro is designed to work on the CSRS (Form) and the Client Matrices (Databases). However
'It will work on any 2 workbooks that are open so it is important to ensure that the formatting
'of the open workbooks will in fact work with this code. Once the Macro is Run, you will not be
'able to undo any of the changes.
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim wb1 As Workbook, wb2 As Workbook 'ALWAYS OPEN THE MATRIX WORKBOOK FIRST
Dim ws1 As Worksheet, ws2 As Worksheet 'ALWAYS OPEN THE FORM LAST. ONLY OPEN 1 FORM AT A TIME.
Dim cl As Range
Dim r As Long, lr As Long
Set wb1 = Workbooks(2) 'Because this macro is set in the Personal Macro Workbook,
'the form will actually be openes as indexed workbook 2.
'This Workbook must be opened first
Set wb2 = Workbooks(Workbooks.Count) 'This sets the last opened workbook as the
'workbook we want to transfer our data to from the form.
'Open this workbook after you open the matrix
Set ws1 = wb1.Sheets(1) 'WE HAVE TO USE THE NAME OF THIS SHEET SO EXCEL CAN DIFFERENTIATE BETWEEN THE 2
'WORKSHEETS WE ARE WORKING ON. THE MATRIX WILL ALWAYS HAVE TO BE IN
'THIS SHEET FOR THE CODE TO WORK. WE CAN
'NAME IT SOMETHING ELSE WHICH WILL ALLOW US TO USE ANY SHEET WITHIN
'THE WORKBOOK NOT JUST SHEET 1
Set ws2 = wb2.ActiveSheet 'BECAUSE THE FORM WILL BE WHAT WE USE TO TRANSFER THE DATA TO THE MATRIX,
'WHEN WE PRESS THE BUTTON TO RUN THE MACRO IT WILL BE FROM THE FORM NOT THE MATRIX.
'Find the last used row in the matrix (the Total Row) and insert a row above it to create a blank row
'in the table.
r = ws1.Range("A" & Rows.Count).End(xlUp).Row
ws1.Cells(r, 1).EntireRow.Insert
With ws1
.Cells(r, 1).Value = ws2.Cells(1, 2).Value & Chr(10) & ws2.Cells(2, 2).Value & Chr(10) & "Masco No." & " " & ws2.Cells(5, 2).Value
.Cells(r, 2).Value = ws2.Cells(6, 2).Value
.Cells(r, 3).Value = ws2.Cells(13, 2).Value
.Cells(r, 4).Value = ws2.Cells(9, 2).Value 'Firm Name Only Need to trim firm name. This is not done yet
.Cells(r, 5).Value = ws2.Cells(14, 2).Value
.Cells(r, 6).Value = ws2.Cells(15, 2).Value
.Cells(r, 7).Value = ws2.Cells(18, 2).Value & Chr(10) & ws2.Cells(16, 2).Value & Chr(10) & ws2.Cells(17, 2)
.Cells(r, 8).Value = ws2.Cells(19, 2).Value
.Cells(r, 10).Value = ws2.Cells(27, 2).Value
.Cells(r, 11).Value = ws2.Cells(29, 2).Value
.Cells(r, 12).Value = ws2.Cells(36, 2).Value
.Cells(r, 13).Value = ws2.Cells(31, 2).Value & Chr(10) & ws2.Cells(33, 2).Value & Chr(10) & ws2.Cells(34, 2)
.Cells(r, 15).Value = ws2.Cells(38, 2).Value
End With
wb1.Save 'SAVE TEH MATRIX IMMEDIATELY AFTER THE TRANSFER IS COMPLETE
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Bookmarks