Results 1 to 11 of 11

Format individual strings out of multiple strings within a cell with VBA

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-15-2012
    Location
    California, USA
    MS-Off Ver
    Microsoft 365 Subscription
    Posts
    120

    Format individual strings out of multiple strings within a cell with VBA

    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
    Last edited by jrtraylor; 09-17-2013 at 11:03 PM. Reason: mixed up some of the cell values in the example paragraph

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel formula to search Multiple strings in several columns and return strings
    By krratna123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 11:20 AM
  2. Replies: 1
    Last Post: 08-13-2013, 08:32 AM
  3. [SOLVED] vlookup individual strings of text within a cell
    By dougdrex in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2013, 03:48 PM
  4. Replies: 3
    Last Post: 05-28-2011, 01:43 PM
  5. Assining individual values to multiple identical strings
    By vivavilla in forum Excel General
    Replies: 1
    Last Post: 03-08-2011, 07:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1