DataBodyRange Combine 2 Columns

mar050703 · Jul 26th 2017

Hi There

I am trying to combine the value of 2 Columns in a Table called "Temp".

Specifically the Table is 12 Columns Wide, and in Column 11 ("Desc"), I want to CONCATENATE the value of Column 12 ("Chq No") and the value of Column 7 ("Desc")

The code I have currently is:

Code

Set RNG = .ListObjects("Temp").ListColumns("Desc").DataBodyRange
    
        For Each cell In RNG.Cells
        
            cell.Value = "Chq No " & .ListColumns("Chq Nq").DataBodyRange.Value & .ListColumns("Desc").DataBodyRange.Value
           
        Next cell

However I get an error saying "Object doesn't support property or method"

Please can someone give me the correct code

mar050703 · Jul 26th 2017

Re: DataBodyRange Combine 2 Columns

I have half solved my query.

By using:

Code

Dim RNG As Range
    
    Set RNG = .ListObjects("Temp").ListColumns("Desc").DataBodyRange
    For Each cell In RNG '.Cells
        
        cell.Value = .ListObjects("Temp").ListColumns("Chq No").DataBodyRange.Value
        
        
    Next cell

I am now able to get the 1st value, but this is entered in ALL cells within the preferred databodyrange.

I need different results.

Secondly, as soon as I add

Code

& .ListObjects("Temp").ListColumns("Description").DataBodyRange.Value

I get an error

NoSparks · Jul 27th 2017

Re: DataBodyRange Combine 2 Columns

I think something like this will work

Code

Dim oLo As ListObject
Dim i As Integer
    
Set oLo = ActiveSheet.ListObjects("Temp")


With oLo
    For i = 1 To .ListRows.Count
        .DataBodyRange(i, 11) = .DataBodyRange(i, 12) & .DataBodyRange(i, 7)
    Next i
End With

Display More

but I'd just use a formula in the first data row and double click the drag handle
=[@[Chq No]]&[@Desc]

DataBodyRange Combine 2 Columns

Participate now!

Share