Loop to concatenate cells

[wongsh]

Hi, I am trying to write a program which gets data from a table, where column A contains names and column B contains qty. It should check for any cell under column B that is not zero (and skip when zero), then capture the value and the corresponding value in column A in the format "X units of ABC / ", and then go on to check for other non-zero cells.
The result should look like 2 units of ABC / 3 units of ASDF...

However, below code only return the last item with non-zero value in the table. i.e. 3 units of ASDF in the case of above example.

Can anyway enlighten me on this? Thanks!!!

For m = 1 To Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count If ActiveCell.Value = 0 Then ActiveCell.Offset(1).Select Else ship = ship & ActiveCell.Value & " units of " & ActiveCell.Offset(, -1).Value & " / " End If ActiveCell.Offset(1).Select Next m

[Jan Karel Pieterse]

The second activecell.offset(1) is in the wrong place.

For m = 1 To Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

 If ActiveCell.Value = 0 Then
 ActiveCell.Offset(1).Select

 Else

 ship = ship & ActiveCell.Value & " units of " & ActiveCell.Offset(, -1).Value & " / "
ActiveCell.Offset(1).Select

 End If

 
 Next m

This is a lot more efficient:

For Each oCell in Range("A:A").Cells.SpecialCells(xlCellTypeConstants)

If oCell.Value <> 0 Then
    ship = ship & oCell.Value & " units of " & oCell.Offset(, -1).Value & " / "
End If 
Next oCell

One odditiy: The loop loops through column A. HOw come you don't get a runtime error due to the offset(,-1) (one column to the left of column A is impossible)?

[wongsh]

Thanks for your reply!
Sorry I was not clear on that. The activecell was pointed to Range("B2") initially, so that it runs down the table from there.
Therefore the offset(,-1) is to point to the corresponding cell in column A.

However, I just tried out the code, but it returned "Qty units of Name / ".
Could you please advise if I missed out anything on that? Thanks!

Picture1.jpg

[Jan Karel Pieterse]

So it is probably better to do it like so:

For Each oCell In Range("B:B").Cells.SpecialCells(xlCellTypeConstants) 
     
    If oCell.Value <> 0 Then 
        ship = ship & oCell.Offset(,1).Value & " units of " & oCell.Value & " / " 
    End If 
Next oCell

[wongsh]

not working as well

should this be working with activecell at Range("B2")?
I tried that but it returns "Qty unit of Name / "
and when I tried with activecell at Range("A2"), it returns "units of Name / "

[Jan Karel Pieterse]

Can you paste a sample of your data of columns A and B?

[jolivanes]

Like this?
This assumes that Column C is free to use.

Sub wongsh()
Dim c As Range
    For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        If c.Value <> 0 Then c.Offset(, 1).Value = c.Value & " units of " & c.Offset(, -1).Value
    Next c
End Sub

[wongsh]

Hi Jan,

Please refer to below sample of the data. So the results for this set of data should be "2 units of ASDF / 5 units of UIOP"

Picture1.jpg

Hi Jolivanes,

Thanks but this code does not concatenate the values in the form "2 units of ASDF / 5 units of UIOP"

[jolivanes]

Re: "Thanks but this code does not concatenate the values in the form "2 units of ASDF / 5 units of UIOP""
I hope not because it is not meant to do that.


Maybe this is more to your liking.
Make sure to change the Cell reference of A30 to the cell where you want the result.

Sub wongsh_version2()
    Dim c As Range, ship As String
    For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        If c.Value <> 0 Then
        ship = ship & c.Value & " units of " & c.Offset(, -1).Value & " / "
        End If
    Next c
    Range("A30").Value = Left(ship, Len(ship) - 3)    '<---- Change cell reference
End Sub

[snb]

Sub M_snb()
    [c2:C200] = [if(B2:B200="","",if(B2:B200=0,"",B2:B200 & " units of " & A2:A200))]

    [K1] = Join(Filter([transpose(if(B2:B200="","",if(B2:B200=0,"",B2:B200 & " units of " & A2:A200)))], " "), " / ")
End Sub

[Jan Karel Pieterse]

Of course not, I got the code wrong. This is the corrected code:

    For Each oCell In Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Offset(1)
        If oCell.Value <> 0 Then
            ship = ship & oCell.Value & " units of " & oCell.Offset(, -1).Value & " / "
        End If
    Next oCell

[wongsh]

Hi Jolivanes and snb, Both codes work perfectly!! Thanks so much for your help and guidance!!!

[wongsh]

Hi Jan, sorry I am not sure why, but above code returns a blank value, but thanks anyway!