合并单元格的宏 [英] Macro for merging cells
问题描述
我在B列(B2:B14987
)中有一个具有发票编号的Excel文件,在C列中我具有项目ID,在D列中我具有出售价值,在E列中我具有发票折扣值.
I have an Excel file with Invoice number in Column B, (B2:B14987
), in Column C I have Item ID's, in Column D I have Sold Value, in Column E I have Invoice-Discount Value.
我需要一个宏来合并基于发票编号"列的发票折扣"值单元格,由于一张发票中存在不同的项目ID,因此重复了发票编号.
I need a macro to merge the Invoice Discount value cells based on Invoice number column, invoice numbers are repeated as there are different item ID's in one invoice.
例如:B1:B3
是相同的发票编号,E1
是B1:B3
中的发票的通用折扣值,E2:E3
是空白单元格.所以我想将E1:E3
与E1
中的值合并.
For example: B1:B3
are the same invoice number, E1
is the common discount value for the invoices which are in B1:B3
, E2:E3
are blank cells. So I want E1:E3
to be merged, with the value that was in E1
.
推荐答案
以下代码符合我的要求.和往常一样,如果我误解了,请澄清问题,我们会到达那里...
The following code does what I think you are asking for; as always, if I misunderstood, please clarify the question and we'll get there...
在电子表格中创建一个模块,然后粘贴以下代码:
Create a Module in your spreadsheet, and paste in the following code:
Private Sub mergeAndAlign(r As Range)
With r
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
End Sub
Sub mergeAll()
' step through column E
' merge all cells that have the same invoice number
' they are already sorted - and the value we need is in the first cell
' of the block to be merged
Dim r As Range
Dim prevItem As Range
Dim nextItem As Range
Dim lastRow, thisRow, rCount As Integer
lastRow = [B2].End(xlDown).Row
Set prevItem = [E2]
Set nextItem = prevItem.End(xlDown)
While nextItem.Row <= lastRow
Set r = Range(prevItem, nextItem.Offset(-1, 0))
mergeAndAlign r
Set prevItem = nextItem
Set nextItem = nextItem.End(xlDown)
Wend
' do the last item:
Set nextItem = Cells(lastRow, 5) ' last valid cell in column E
Set r = Range(prevItem, nextItem)
mergeAndAlign r
End Sub
运行感兴趣的工作表中的代码.单击Alt-F8弹出宏"对话框-您应该在列表中看到"MergeAll"项(可能是唯一的一项).它将带您离开这里:
Run the code from the sheet of interest. Click Alt-F8 to bring up the "macro" dialog - you should see the item "MergeAll" in the list (probably the only one). It will take you from this:
对此:
这篇关于合并单元格的宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!