Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find specific text in cell

    Hey guys, This is my sheet:

    Untitled.png
    As you can see I need a macro that search a concatenation of "B1 - B2 - B3" in A4 and put in B4 the percentage relative to the string founded.

    Can you give me some help? Thanks!

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You don't need a macro to do this if you layout your input data in a table and can implement a formula like http://www.exceltactics.com/faster-m...p-concatenate/
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    GM,

    If you decide you still need a macro, this code will do what you need

    Code:
    Public Sub FindText()
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, LastCol As Long, letters As String, s, t
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    letters = ""
    '----------------------------
    'CYCLE THROUGH ROWS AND SPLIT EXPRESSIONS
    For I = 4 To LastRow
        s = Split(Cells(I, 1), "; ")
    '----------------------------
    'REMOVE "-" AND CONCATENATE LETTERS
        For J = 0 To UBound(s)
            t = Split(s(J), " - ")
            For M = 0 To UBound(t) - 1
                letters = letters & t(M)
            Next M
    '----------------------------
    'FIND MATCHING LETTERS AND PLACE VALUES
            For K = 2 To LastCol
                If letters = Cells(1, K) & Cells(2, K) & Cells(3, K) Then
                    Cells(I, K) = t(UBound(t))
                End If
            Next K
            letters = ""
        Next J
    Next I
    End Sub
    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-12-23 at 22:11.

Tags for this Thread

Posting Permissions

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