VBA to copy cell value into another cell text string

0
votes

I am managing inventory in Excel. I have 2 columns to work with:

  1. product name
  2. packaging info

The table looks like this

product name             packaging info
BLISE FACEWASH           50GM
IVREA CREAM              60GM
IVREA CREAM              30GM
IVREA SHAMPOO            30ML

I want to add the value in packaging info into the product name, but I want to add it before the last word in product name.

Desired result:

product name                 packaging info
BLISE 50GM FACEWASH          50GM
IVREA 60GM CREAM             60GM
IVREA 30GM CREAM             30GM
IVREA 30ML SHAMPOO           30ML

I tried concatenate but it does not help and makes it a lot more complicated.

I have been trying this with macros as it seems doable.

  1. copy value in column 2
  2. move to left cell
  3. skip 1 word from right (Ctrl+Left arrow)
  4. paste value
  5. add a space
  6. go to next row

All the cells in product name end up with same values. Where I am going wrong?

If not with macros, what would be the easiest way to do this?

By the way: the sheet contains 10 columns, 4500+ rows.

1
vbaexcel

1 Answers

0
votes

VBA; Assumes headers are in A1/B1:

Sub MergeCols
    Dim cell As Range, pos As Long

    Set cell = Range("A2")

    Do Until cell.Value = ""
        pos = InStrRev(cell.Value, " ")
        If (pos > 0) Then
            cell.Value = Left$(cell.Value, pos) & cell.Offset(0, 1).Value & Mid$(cell.Value, pos)
        Else
            cell.Value = cell.Value & " " & cell.Offset(0, 1).Value
        End If
        Set cell = cell.Offset(1, 0)
    Loop
End Sub