How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1964
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I need someone to create a VBA macro in excel to merge the

Customer Question

I need someone to create a VBA macro in excel to merge the following:
Cell A6: Cell A7, Cell B6: Cell B7, Cell C6: Cell C7, etc....
I need to maintain the formatting of the bold headings in row 6. I need to add the colon, comma and space. The heading row 6 is fixed but there are several sheets within the workbook. Each has several rows that need to be combined with the fixed headings in the column.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 2 years ago.
Thanks for using JustAnswer. My name is***** will do whatever I can to answer your question

can you provide an example sheet
Customer: replied 2 years ago.

Attachment: 2015-05-08_202626_fac85_new_items_template_-_md_stetson_5-8-15.xlsx

Hi, You actually did this last time for us. But, this time it is a little different and does not work. Also, I'm not sure they have not done something with the format.

Thanks.

Expert:  The-PC-Guy replied 2 years ago.
lemme take a look, ill let u know if i have any questions
Expert:  The-PC-Guy replied 2 years ago.
is it possible you sent the wrong sheet, because I am really not following what you want
Customer: replied 2 years ago.

Yes. It is the right one. The 'T' column is in the 'New Catalog Items'. Here is the code you wrote previously for another spreadsheet. I just don't know enough about VB to get it to work with the new spreadsheet.

Sub populate()

Dim wks As Worksheet
Dim array1(), array2()

Dim cval As String
Dim i, j, lr, a, strt, eend As Long

Set wks = Worksheets("LI")

lr = wks.Cells(Rows.Count, "A").End(xlUp).Row
a = 0
For Each rcell In wks.Range("A1:O1")
ReDim Preserve array1(a)
array1(a) = rcell.Value
a = a + 1
Next rcell

If a > 0 Then
Erase array2()
For i = 2 To lr
a = 0
cval = ""
For Each rcell In wks.Range("A" & i & ":O" & i)
ReDim Preserve array2(a)
array2(a) = rcell.Value
a = a + 1
Next rcell
If a > 0 Then
For j = LBound(array1) To UBound(array1)
cval = cval & array1(j) & ": " & array2(j) & ", "
Next j
End If
cval = Left(cval, Len(cval) - 2)
wks.Range("T" & i).Value = cval
wks.Range("T" & i).Font.FontStyle = "Normal"
eend = 0
For j = LBound(array1) To UBound(array1)
strt = InStr(eend + 1, cval, array1(j))
eend = InStr(strt, cval, ":")
With wks.Range("T" & i).Characters(Start:=strt, Length:=eend - strt).Font
.FontStyle = "Bold"
End With
Next j
Next i

Else
Exit Sub
End If

End Sub

Expert:  The-PC-Guy replied 2 years ago.
but i dont understand what you want to do with this one
Customer: replied 2 years ago.

The old VB code does not work with this spreadsheet. It has been changed by our customer. When executed the old macro does not result in the desired contents of column T. The desired content of column T throughout the spreadsheet is the sum of all columns for each row using column heading: column row content, column heading: column row content, etc. A-O columns populating in column T.:

Expert:  The-PC-Guy replied 2 years ago.

let me try to see what is going on

if there is any chance to send a few rows example of the desired output, that would be helpful.

Expert:  The-PC-Guy replied 2 years ago.
https://www.sendspace.com/file/7oiqwr

let me know if this works for you
Customer: replied 2 years ago.

The populate2 macro has the same results as populate macro. It does not process all the way through and crashed excel. I'm thinking it has something to do with the workbook itself. Can you try to create another a) workbook as a result of running the macro or b) the result on a different sheet. That may be cleaner, but I don't really know how to modify the macro.

Expert:  The-PC-Guy replied 2 years ago.
how many rows are there?

if there are a lot of rows it may appear to crash.

I can modify the macro.
Customer: replied 2 years ago.

Currently there are 6,670 rows.

Thanks.

Expert:  The-PC-Guy replied 2 years ago.
how shal i modify the macro, do you want the macro to create a new workbook each time it runs?
Customer: replied 2 years ago.

If that is the best option. We can cut and paste the result into column T.

Expert:  The-PC-Guy replied 2 years ago.
i dont think going out to another workbook is going to solve the problem

here is the latest version I seams to time out after about row 150 or so, but it is still running just takes time.

I limited it to 1000 rows for now so you can see that it runs.

https://www.sendspace.com/file/sr1k8m

let me know if you still have problems running it with the limited 1000 rows
Customer: replied 2 years ago.

Still doesn't work. I get only Product Category in T. Nothing else.

Expert:  The-PC-Guy replied 2 years ago.
did you try double clicking in one of the cells, the data is there, the cell just may not be big enough to display it all
Expert:  The-PC-Guy replied 2 years ago.
did you have a chance to try to double click one of the cells to see it fhe data is there.

Let me know if this resolves the problem so I can set it to go thtough all the rows
Expert:  The-PC-Guy replied 2 years ago.
hey there its been about a week since we last spoke. I am just trying to follow up to see if you need me further on this issue?
Customer: replied 2 years ago.

Hi,

That did not work either. It may have been something to do with the cell and the length. I copied out 1000 rows at a time and performed the macro. Not fun, but it worked. Thatnks for the efforts.

Andrea

Expert:  The-PC-Guy replied 2 years ago.
im goint to try something else Im going to try to make it auto adjust the cell size.
Customer: replied 2 years ago.

That would be great for our future issues with this spreadsheet.

Thanks.

Expert:  The-PC-Guy replied 2 years ago.
ok, please rate so im reimbursed. I will make the changes in the next day or so

Related Microsoft Office Questions