PDA

View Full Version : Merging cells and suppress excel's popup



sebas1102
06-08-2006, 01:58 AM
Hi all,
I have a workbook that pulls out data from a master list.
from column A onwards, it pulls out the item's name("A"), Date of Production("B") and total quantity("C").
For an item with different production dates, the total quantity are the same. So, I first sort out items according to their names, and merge the total quantities together to 1 cell. ( for example"A1" to "A5" are of the same item apple, and "A6" to "A11" are all bananas) So I need to merge the cells of total quantities from C1 to C5 and B6 to B11 respectively.

2 problems cropped out: 1) I used record macro, but it cannot dynamically merge the cells if the number of items change.

Range("C1:C5").Select
Selection.Merge
Range("C6:C11").Select
Selection.Merge


2) When I run the macro, a msgbox keeps popping out:
"selection contains multiple data values. merging cells will keep the left uppermost data only."

so i tried using on error resume next , but it still does not suppress the popup.

Please help!

johnske
06-08-2006, 03:34 AM
Without testing - try Application.DisplayAlerts = False before the merge (don't forget to put it back to True afterwards)

sebas1102
06-11-2006, 05:39 AM
Without testing - try Application.DisplayAlerts = False
before the merge (don't forget to put it back to True afterwards)

ok thanks! but i still cannot figure out how to merge the quantities together for the same items... is it not possible?

mdmackillop
06-11-2006, 06:00 AM
Can you post a sample showing the outcome that you are after?

sebas1102
06-19-2006, 08:15 PM
iItem = Cells(irow, StkCodeProdnFC)
For iirow = iirow To llrow
jItem = Cells(iirow, StkCodeProdnFC)
If jItem = iItem Then
jrow = jrow + 1
End If

Next iirow
Sheets("by materials").Activate
Range(Cells(irow, StkDescProdnFC), Cells(jrow, StkDescProdnFC)).Merge
Range(Cells(irow, ETAProdnFC), Cells(jrow, ETAProdnFC)).Merge
Range(Cells(irow, TWIPDrawnProdnFC), Cells(jrow, TWIPDrawnProdnFC)).Merge
Range(Cells(irow, AwaitingDeliveryProdnFC), Cells(jrow, AwaitingDeliveryProdnFC)).Merge
Range(Cells(irow, PendingFinInstrnProdnFC), Cells(jrow, PendingFinInstrnProdnFC)).Merge
Range(Cells(irow, ShrtPrcheProdnFC), Cells(jrow, ShrtPrcheProdnFC)).Merge
Range(Cells(irow, PhyStkQtyProdnFC), Cells(jrow, PhyStkQtyProdnFC)).Merge
Range(Cells(irow, StkCodeProdnFC), Cells(jrow, StkCodeProdnFC)).Merge


i figured it out to b something like this...thanks anyway!