+ Reply to Thread
Results 1 to 2 of 2

Concatenating variable cell values together

Hybrid View

joevan1 Concatenating variable cell... 05-03-2015, 09:27 PM
joevan1 Re: Concatenating variable... 05-03-2015, 09:52 PM
  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    28

    Concatenating variable cell values together

    I'm working with a dataset and I have written a loop to check if the cell two below a found instance of a string contains a certain value. If it doesn't I want to concatenate the value of that cell with the cell one below the found cell. And then I want to loop this until the cell two below the found value contains the required string.

    I'm sorry if this is confusing, but the code should hopefully speak for itself:

        Dim StrSearch As String
        Dim rng1 As Range
        Dim rng2 As Range
            
        StrSearch = "Ship to"
    
        With Worksheets(1).UsedRange
            Set rng1 = .Find(StrSearch, , xlValues, xlWhole)
              If Not rng1 Is Nothing Then
                 strAddress = rng1.Address
                 Set rng2 = rng1
            Do
                Set rng1 = .FindNext(rng1)
                Set rng2 = Union(rng2, rng1)
            Loop While Not rng1 Is Nothing And rng1.Address <> strAddress
        End If
        End With
    
        If Not rng2 Is Nothing Then
        For Each rng3 In rng2
    
        Do Until InStr(1, rng3.Offset(2).Value, "End") > 0
        rng3.Offset(1, 1) = "=" & rng3.Offset(1).Value & "&" & ", " & rng3.Offset(2).Value
        rng3.Offset(1, 1).Copy
        rng3.Offset(1).PasteSpecial xlPasteValues
        rng3.Offset(1, 1).Delete Shift:=xlUp
        rng3.Offset(2).Delete Shift:=xlUp
        Loop
        Next
        End If
    Everything works fine except for the line

     rng3.Offset(1, 1) = "=" & rng3.Offset(1).Value & "&" & ", " & rng3.Offset(2).Value
    which gives a 'run-time error 1004: Application-defined or object-defined error'

    So if the data is:

    Ship to
    UK
    USA

    I want it to concatenate the line two below 'Ship to' (i.e. USA) with the line one below 'Ship to' (i.e. UK) so the cell reads 'UK, USA'.

    So the equivalent of

    =A24&", "&A25
    for example. But variable depending on the location of the found string.

    Again, sorry if this is confusing. Thanks for any help!

  2. #2
    Registered User
    Join Date
    04-02-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Concatenating variable cell values together

    Ok I figured it out myself, just had to change the line to:

    rng3.Offset(1, 1) = rng3.Offset(1) & ", " & rng3.Offset(2)
    Thanks all the same!

    Cheers!

+ 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. Concatenating cell values to create sheet names
    By XLDabbler in forum Excel General
    Replies: 4
    Last Post: 08-27-2019, 11:49 AM
  2. Quote Crazy!! Concatenating filenames and cell values
    By fitzt70 in forum Excel General
    Replies: 2
    Last Post: 07-19-2013, 10:10 PM
  3. [SOLVED] Concatenating cell values
    By tray262 in forum Excel General
    Replies: 20
    Last Post: 11-11-2012, 09:22 AM
  4. Concatenating a Variable Name
    By CrazyFileMaker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2009, 04:35 AM
  5. Concatenating 2 Character to form name Variable
    By mozart in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2005, 12:05 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