+ Reply to Thread
Results 1 to 9 of 9

VBA: Look for partial text in Column A from Column B and paste text offset Column A

  1. 10-04-2018, 09:23 PM #1
    coryspeth
    coryspeth is offline
    Registered User
    Join Date
    10-04-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Hi,

    I'm new to VBA and trying to figure this out.

    I have a data set in Column A(Sheet1) and another set of data in Column B(Sheet2). Column B has parts of text in Column A. If any text in Column B matches any partial text in Column A, I would like it to paste "TEXT" offset (0,1)(lets call this Column C) to Column A. I hope below makes sense.

    Sheet1
    Column A
    AAAAAA
    BBBBBB Paste "TEXT" to right cell
    CCCCCC
    DDDDD
    AABBCC Paste "TEXT" to right cell
    ABCDEF

    Sheet2
    Column B
    LKLP
    BB
    PLMH


    I can make it work with one cell from Column B but I cant make it search the entire selection of Column B.

    Thanks
    Last edited by coryspeth; 10-04-2018 at 09:25 PM. Reason: formatting
    Reply With QuoteRegister To Reply
  2. 10-05-2018, 02:21 AM #2
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Hi, welcome to the forum.
    If it works with one column, what is the code you're using
    You want us to play guessing games?
    A little more information (with the used code or even a sample file with dummy data) will get you answers (faster)
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  3. 10-05-2018, 03:49 AM #3
    coryspeth
    coryspeth is offline
    Registered User
    Join Date
    10-04-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Hi, i have below. I cannot do A1:A2 as it errors . Sorry i am new to this.

    For Each r In Intersect(ActiveSheet.UsedRange, Range("K:K"))
    If r.Text = sheets("SHEET2").range("A1") Then
    r.Offset(0, 1) = "TEST"
    End If
    Next r
    Reply With QuoteRegister To Reply
  4. 10-05-2018, 04:08 AM #4
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    What text are you looking for?
    What is the error?
    With the information you offer , well. No go
    Reply With QuoteRegister To Reply
  5. 10-05-2018, 04:11 AM #5
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    This ?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 10-05-2018, 05:12 AM #6
    coryspeth
    coryspeth is offline
    Registered User
    Join Date
    10-04-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Hi - Thank you but unfortunately it does not. The text I am looking for is the text in column b in column a. So something like this

    Sub Test()

    Dim r As Range
    For Each r In Intersect(ActiveSheet.UsedRange, Range("K1:K10000"))
    If r.Text = Sheets("SHEET2").Range("A1:A25") Then
    r.Offset(0, 1) = "TEST"
    End If
    Next r
    End Sub

    But unfortunately If r.Text = Sheets("SHEET2").Range("A1:A25") comes with a type mismatch error.
    Reply With QuoteRegister To Reply
  7. 10-05-2018, 05:38 AM #7
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Well, we can start playing ping-pong with questions.
    Just post a sample file and THEN we can make progress
    Reply With QuoteRegister To Reply
  8. 10-06-2018, 09:51 PM #8
    coryspeth
    coryspeth is offline
    Registered User
    Join Date
    10-04-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    16

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Hi Keebellah,

    I have the following at the moment. 


    Please Login or Register  to view this content.
    This formula only works with exact matches as with A4 SHEET4 however I require a partial match. I cannot find a way to add a wildcard(*) to Range("A1:A3") or the text itself. Sample attached...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-07-2018, 05:53 AM #9
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: VBA: Look for partial text in Column A from Column B and paste text offset Column A

    Your sample does not show what you have, you keep mentioning Sheet 4 and column B but none of that is there.
    Which is the sheet wher you want the result?
    Sheet1 has nothing in column B nor dus Sheet2.
    Update the sampel with what you HAVE and waht you would like to SEE as result.
    Assumptions; I no longer work with those so please try and explain it in a way that someone who has no idea of what you want can understand it.
    BTW if you look for partial text (case sensitive) use Instr
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to Copy and Paste Specific Text in Column A on the last line with Text in Column B?
    By D2S in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2017, 04:08 PM
  2. Remove partial text from 1 column?
    By IsabellaLearning in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2014, 04:06 AM
  3. search column for text, copy and paste into new column
    By adammss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2013, 07:12 PM
  4. Looking up partial text from a column
    By bronkista in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2013, 10:31 PM
  5. [SOLVED] search column for text, if found, paste value in a different column in new sheet
    By wkarnadi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-17-2012, 10:29 AM
  6. code to find text, offset 1 column and paste to new workbook not working
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-10-2011, 07:55 AM
  7. Make new column using partial text in another cell
    By lmrippey in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2005, 10:15 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1