+ Reply to Thread
Results 1 to 5 of 5

Combining Two Target.columns

  1. #1
    Registered User
    Join Date
    06-12-2018
    Location
    Kansas City
    MS-Off Ver
    2016
    Posts
    3

    Combining Two Target.columns

    I am very very new to vba and claim to know nothing.

    I have on sheet "DataEntry" two columns with drop downs Column B and Column E. Each of the drop downs, in column B & E, the text is a combination of two cells "&" from a table from sheet "Codes". First part of the drop down text is the number and then the description (1234-Widget). When the correct description is picked it only displays the first part the part number (1234). This routine happens in both Column B and E.

    I can get the Column B to work but now Column E. I thought I was close and was missing something very minor and am asking for help but after searching thru different threads I am more confused than before.

    Am I on the right track? Can I get help?

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo errHandler

    Dim wsCodes As Worksheet
    Set wsCodes = Worksheets("Codes")

    If Target.Cells.Count > 1 Then GoTo exitHandler

    If Target.Column = 2 Or Target.Column = 5 Then
    If Target.Value = "" Then GoTo exitHandler

    Application.EnableEvents = False

    Target.Value = wsCodes.Range("A1") _
    .Offset(Application. _
    WorksheetFunction _
    .Match(Target.Value, _
    wsCodes.Range("ProdList"), 0), 0)

    Target.Value = wsCodes.Range("F1") _
    .Offset(Application. _
    WorksheetFunction _
    .Match(Target.Value, _
    wsCodes.Range("BucketList"), 0), 0)

    End If

    exitHandler:
    Application.EnableEvents = True
    Exit Sub

    errHandler:
    If Err.Number = 13 Or Err.Number = 1004 Then
    GoTo exitHandler
    Else
    Resume Next
    End If

    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,301

    Re: Combining Two Target.columns

    You are overwriting the value using this

    Please Login or Register  to view this content.
    So, perhaps, combine the two returned values:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-12-2018
    Location
    Kansas City
    MS-Off Ver
    2016
    Posts
    3

    Re: Combining Two Target.columns

    Before your suggestion my Column "B" worked and Column "E" would not display only the part number it would do both part number and description. I have cut and paste your code above and now my Column "B" does the same as Column "E".

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,301

    Re: Combining Two Target.columns

    Then use code that is specific to the column: This does the same thing for B and E, but it gives you the structure you will need:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-12-2018
    Location
    Kansas City
    MS-Off Ver
    2016
    Posts
    3

    Re: Combining Two Target.columns

    This was the direction I was looking for. I took what you gave me and it didn't work however I made a slight tweak and vwala!!! It works perfectly!
    See my changes below. Thanks for all your help this was awsome!!!

    If Target.Column = 2 Then

    Application.EnableEvents = False

    Target.Value = wsCodes.Range("A1").Offset(Application.Match(Target.Value, wsCodes.Range("ProdList"), False), 0)

    End If

    If Target.Column = 5 Then

    Application.EnableEvents = False

    Target.Value = wsCodes.Range("F1").Offset(Application.Match(Target.Value, wsCodes.Range("BucketList"), False), 0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for combining taxed and tax-free payments to get a target net payment
    By ianjemmett in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2017, 06:03 PM
  2. [SOLVED] Count Columns until reach target
    By alexpickup in forum Excel General
    Replies: 3
    Last Post: 01-05-2015, 12:28 PM
  3. Combining two Private Sub Worksheet_Change(ByVal Target As Range)
    By iceblocks in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2014, 06:47 PM
  4. Combining data in multiple columns(all columns have same length)into one column
    By nzi0001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-24-2014, 11:19 AM
  5. [SOLVED] Combining multiple Private Sub Worksheet_Change(ByVal Target As Range)
    By victortan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-21-2014, 11:12 AM
  6. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  7. Copy Paste in a Target Row in Different Columns
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-28-2011, 01:09 PM

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