vba - Copy non blank cell in a range and paste to only one column

Question

I am pretty new to vb, please help me if you can. I have a range of data，starting from N5 to R24, all cells contain formulas, but only few of them are non-blank cells.

I want to copy only those non-blank cell to column AK，and add a string "/CA" to them。If cell in column N is ?bulk, add the number in column B first before adding "/CA",like below:

         B          N     O      P     Q     R     S           T
5                 1PLA  2PMC                                 1PLA/CA
6  123-01456789   1BULK                                      2PMC/CA
7                 1AKE                                1BULK/123-01456789/CA
8                                                             1AKE
9
10

The order in column AK doesn't matter that much, as long as it contains all non-blank cell data of range(N5:R24).

The following code is what I've tried, but I don't know how to add string or data in column B after paste:

Sub test()
Dim ws As Worksheet
Set ws = Sheets("Data")
LastRow = ws.Cells(Rows.Count, "AK").End(xlUp).Row
For Each cell In Range("N5:R24")
If cell.Value <> "" Then
cell.Copy
Range("AK" & LastRow + 1).PasteSpecial xlPasteValues
End If
Next
End Sub

Answer 1

The following code will, I think, do what you are after. (I wasn't sure whether column B was appended to the output only if you were processing column N, or whether you were processing any value on a row where column N contained "BULK". My code does the latter, but I also included a commented-out version to do the former.)

Dim cell As Range
Dim lastRow As Long
Dim newValue As String
Dim ws As Worksheet
Set ws = Sheets("Data")

With ws
    'Initialise lastRow to be the last used row in column T
    lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
    'Loop through every cell in the desired range
    For Each cell In .Range("N5:R24")
        'Start by setting the new value to be the old value
        newValue = cell.Value
        'Only process cells that aren't blank
        If newValue <> "" Then
            'Increment row counter for result
            lastRow = lastRow + 1
            'Check if column N contains "BULK"
            If UCase(Mid(.Cells(cell.Row, "N").Value, 2, 4)) = "BULK" Then
                'If it does, append the contents of column B
                newValue = newValue & "/" & .Cells(cell.Row, "B").Value
            End If
            ''Alternate version if ONLY values in column N need to have column B appended
            'If UCase(Mid(newValue, 2, 4)) = "BULK" And cell.Column = 14 Then
            '    'If it does, append the contents of column B
            '    newValue = newValue & "/" & .Cells(cell.Row, "B").Value
            'End If

            'Append "/CA"
            newValue = newValue & "/CA"
            'Store result in column T
            .Cells(lastRow, "T").Value = newValue
        End If
    Next
End With