|
窗体内的代码如下: 详细代码见附件货物清单内
- Private Sub CommandButton1_Click()
-
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
-
- 'Me.OptionButton1.Value = True
- Dim syb As String
- Dim m As Integer
- Dim sh As Workbook
- Dim hwmc As String
- Dim i As Integer
- Dim x As Integer
-
-
- Unload UserForm2 '关闭窗体
- '-------------确定事业部---------------
- If Me.OptionButton1.Value = True Then
- syb = "开关"
- ElseIf Me.OptionButton2.Value = True Then
- syb = "自动化"
- ElseIf Me.OptionButton3.Value = True Then
- syb = "成套"
- Else: syb = "变压器"
- End If
-
-
-
- '-------------取标段名称-----------------
- Range("a1").Select
- Selection.UnMerge
- 'Range("A1").Select
- hwmc = Range("a1").Value
-
- '-------------取行数,并复制需要的列----------------
- m = Range("b65536").End(xlUp).Row
- Range("a3:a" & m & ",g3:g" & m & ",j3:j" & m).Select
-
- Selection.Copy
- '-------------打开汇总表,黏贴复制的值,使F列不自动换行---------
- Set sh = Application.Workbooks.Open("C:\Users\Administrator\Desktop\汇总表.xls")
- n = Range("b65536").End(xlUp).Row
- Range("E" & n + 1).Select
- ActiveSheet.Paste
- Range("F:F").Select
- Selection.WrapText = False
-
- '--------------------------转换为数字--------------------
-
- Range("g" & n + 1, "g" & n + m - 2).Select
- Dim rng As Range
- '遍历每个选择区域
- For Each rng In Selection
- '遍历当前选中区的所有单元格
- For Each cell In rng.Cells
- cell.Value = cell.Value
- Next
- Next
-
-
-
- '--------------------填充标段名和事业部名称----------------------
- Range("b" & n + 1 & ":b" & n + m - 2).Value = syb
- Range("d" & n + 1 & ":d" & n + m - 2).Value = hwmc
-
-
- For i = n + 1 To n + m - 2
- Range("h" & i) = Range("d" & i) & Range("e" & i)
- Range("a" & i) = i - 1
-
-
- Next
-
-
- '--------------------合并单元格-------------------------------
- Call 合并单元格
- 'Range("h" & n + 1 & ":h" & n + m - 2).ClearContents
-
- '------------------求和-----------------------------
- For x = n + 1 To n + m - 2
-
- Cells(x, 8) = Application.WorksheetFunction.SumIfs(Range("G:G"), Range("D:D"), Cells(x, 4), Range("E:E"), Cells(x, 5))
-
- Next
-
- '-----------------设置格式--------------------------
-
-
- Call geshi
-
-
- Range("a" & n + 1, "i" & n + m - 2).Select
-
-
-
- With Selection.Font
- .Name = "宋体"
- .Size = 10
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ColorIndex = xlAutomatic
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
- With Selection.Font
- .Name = "宋体"
- .Size = 10
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ColorIndex = xlAutomatic
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
- Selection.Borders(xlDiagonalDown).LineStyle = xlNone
- Selection.Borders(xlDiagonalUp).LineStyle = xlNone
- With Selection.Borders(xlEdgeLeft)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With Selection.Borders(xlEdgeTop)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With Selection.Borders(xlEdgeBottom)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With Selection.Borders(xlEdgeRight)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With Selection.Borders(xlInsideVertical)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- With Selection.Borders(xlInsideHorizontal)
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
-
- ActiveWorkbook.Save
- ActiveWorkbook.Close
-
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
-
- End Sub
复制代码
|
|