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!

18742

[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"

18750

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!