We receive RFQ’s via email in excel 5.0 format. Each wksheet (42 separate wksheets per wkbook) has approximately 2500 rows of info organized as
COL A = PART NUMBER (one cell)
COL B = DESCRIPTION (from one to 7 cells in the column)
I need to make a VB macro which would allow me to concatenate all the cells in COL B into one cell, while referencing the individual part number in COL A.
The following Macro works some…but not OK
Sub ConcatColumns ()
Do While ActiveCell <> “” ‘Loops until the active cell is blank.
‘The “&” must have a space on both sides or it will be treated
‘as a variable type of long integer.
ActiveCell.Offsett(0, 1).FormulaR1C1 = _
ActiveCell.Offsett(0, -1) & ” ” & ActiveCell.Offsett(0, 0)
ActiveCell.Offsett(1, 0).Select
Loop
End Sub
What happens is that I cannot
a)
get all the cells in COL B (for Description) relating to any ONE PART Number (in COL A) to be specifically concatenated into COL C,in one cell.
b)
ex