通信人家园

标题: 08版预算表格表四材料如何分段 [查看完整版帖子] [打印本页]

时间: 2011-6-19 20:09

作者: 风过漠池 标题: 08版预算表格表四材料如何分段

请问08版预算表格表四材料如何分段？？？

时间: 2011-6-19 20:15

作者: sftxys

不同类别的材料主要是运杂费率不同，可以按类别分段，光缆，电缆，其它，木材，塑料

时间: 2011-6-21 16:26

作者: snwj99860

可以分：光缆类材料、电缆类材料、塑料及塑料制品类材料、木材及木材制品类、水泥及水泥构件类材料、其它类

时间: 2013-5-9 11:20

作者: xp403815

Sub 导入安装设备表()
  Sheet6.Rows("6:65536").RowHeight = 19

   Sheet6.Range("A7:H65536") = ""
'电缆类#################################################################################################################################################

X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "DL" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "电缆类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      dl = Sheet6.Cells(Y + 1, 7).Address
End If
'光缆类#################################################################################################################################################

X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "GL" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "光缆类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      gl = Sheet6.Cells(Y + 1, 7).Address
End If
'钢材及其他#################################################################################################################################################
  X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "GCJQT" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "钢材及其他类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      gc = Sheet6.Cells(Y + 1, 7).Address
End If
'木材及制品#################################################################################################################################################
  X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "MCJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "木材及制品类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      mc = Sheet6.Cells(Y + 1, 7).Address
End If
'塑料及制品#################################################################################################################################################
  X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "SLJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "塑料及制品类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      sl = Sheet6.Cells(Y + 1, 7).Address
End If
'水泥及制品#################################################################################################################################################
X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "安装设备" And Sheet12.Range("B" & I) = "SNJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet6.Range("B" & Y + 1)
Sheet6.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet6.Cells(Y + 1, 2) = "水泥及制品类小计："
      Sheet6.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      sn = Sheet6.Cells(Y + 1, 7).Address
End If
'总价求和#################################################################################################################################################
X = Sheet6.[G65536].End(xlUp).Row '第一次记录表的最后一行
Z = 1
For I = 7 To X
Sheet6.Range("A" & I) = Z
If Sheet6.Range("d" & I) = "" Then
Sum = Sum + Sheet6.Range("G" & I)
Sheet6.Range("A" & I) = "" '编写材料表中序号
Z = Z - 1
End If
Z = Z + 1
Next
      Y = Sheet6.[G65536].End(xlUp).Row '记录表的最后一行
      Sheet6.Cells(Y + 1, 2) = "总计（合计之和）："
      Sheet6.Cells(Y + 1, 7) = "=sum( " & dl & " , " & gl & " , " & gc & " , " & mc & " , " & sl & " , " & sn & ")" '求和导入数据"
      Sheet6.PageSetup.PrintArea = "A1:H" & Y + 1
      Sheet6.Range("A" & Y + 2 & ": H" & 65536).Delete
b4a = Sheet6.UsedRange.Find("总计（合计之和）：").Row '调整安装设备
a = b4a - 1

For I = a To 7 Step -1

If Sheet6.Range("G" & I).Value = "" Then
Sheet6.Rows(I).Delete Shift:=xlUp
End If
Next

b4a = Sheet6.UsedRange.Find("总计（合计之和）：").Row
If b4a <= 24 Then
For I = 1 To 24 - b4a
Sheet6.Rows(b4a).EntireRow.Insert
Next
ElseIf 24 < b4a Then
For I = 1 To 18 - ((b4a - 24) Mod 18)
Sheet6.Rows(b4a).EntireRow.Insert
Next
End If
b4a = Sheet6.UsedRange.Find("总计（合计之和）：").Row
  Sheet6.Select
  Range("A7", "A" & b4a).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlCenter
      .Font.Name = "宋体"
      .Font.Size = 9
End With
Range("B7", "D" & b4a).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlLeft
      .Font.Name = "宋体"
      .Font.Size = 9
End With
Range("E7", "H" & b4a).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlRight
      .Font.Name = "宋体"
      .Font.Size = 9
End With
End Sub
Sub 导入国内主材表()
Sheet7.Rows("6:65536").RowHeight = 19
   Sheet7.Range("A7:H65536") = ""
'电缆类#################################################################################################################################################

X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "DL" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "电缆类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      dl = Sheet7.Cells(Y + 1, 7).Address
End If
'光缆类#################################################################################################################################################

X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "GL" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "光缆类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      gl = Sheet7.Cells(Y + 1, 7).Address
End If
'钢材及其他#################################################################################################################################################
  X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "GCJQT" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "钢材及其他类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      gc = Sheet7.Cells(Y + 1, 7).Address
End If
'木材及制品#################################################################################################################################################
  X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "MCJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "木材及制品类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      mc = Sheet7.Cells(Y + 1, 7).Address
End If
'塑料及制品#################################################################################################################################################
  X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "SLJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "塑料及制品类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      sl = Sheet7.Cells(Y + 1, 7).Address
End If
'水泥及制品#################################################################################################################################################
X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Y = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
For I = 2 To Sheet12.[C65536].End(xlUp).Row
If Sheet12.Range("H" & I) = "国内主材" And Sheet12.Range("B" & I) = "SNJZP" Then
Sheet12.Range("C" & I & ":I" & I).Copy Sheet7.Range("B" & Y + 1)
Sheet7.Range("G" & Y + 1).Formula = "=E" & Y + 1 & "*F" & Y + 1
   Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
End If
Next
Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
If Val(Y - X) <> 0 Then
      Sheet7.Cells(Y + 1, 2) = "水泥及制品类小计："
      Sheet7.Cells(Y + 1, 7) = "=sum(G" & X + 1 & ":G" & Y & ")" '求和导入数据
      sn = Sheet7.Cells(Y + 1, 7).Address
End If
'总价求和#################################################################################################################################################
X = Sheet7.[G65536].End(xlUp).Row '第一次记录表的最后一行
Z = 1
For I = 7 To X
Sheet7.Range("A" & I) = Z
If Sheet7.Range("d" & I) = "" Then
Sum = Sum + Sheet7.Range("G" & I)
Sheet7.Range("A" & I) = ""
Z = Z - 1
End If
Z = Z + 1
Next
      Y = Sheet7.[G65536].End(xlUp).Row '记录表的最后一行
      Sheet7.Cells(Y + 1, 2) = "材料总计："
      Sheet7.Cells(Y + 1, 7) = "=sum( " & dl & " , " & gl & " , " & gc & " , " & mc & " , " & sl & " , " & sn & ")"  '求和导入数据


      Sheet7.PageSetup.PrintArea = "A1:H" & Y + 1
      Sheet7.Range("A" & Y + 2 & ":H" & 65536).Delete
b4c = Sheet7.UsedRange.Find("材料总计：").Row '调整国内主材表
a = b4c - 1

For I = a To 7 Step -1

If Sheet7.Range("G" & I).Value = "" Then
Sheet7.Rows(I).Delete Shift:=xlUp
End If
Next

b4c = Sheet7.UsedRange.Find("材料总计：").Row
If b4c <= 24 Then
For I = 1 To 24 - b4c
Sheet7.Rows(b4c).EntireRow.Insert
Next
ElseIf 24 < b4c Then
For I = 1 To 18 - ((b4c - 24) Mod 18)
Sheet7.Rows(b4c).EntireRow.Insert
Next
End If
b4c = Sheet7.UsedRange.Find("材料总计：").Row
Sheet7.Select
  Range("A7", "A" & b4c).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlCenter
      .Font.Name = "宋体"
      .Font.Size = 9
End With
Range("B7", "D" & b4c).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlLeft
      .Font.Name = "宋体"
      .Font.Size = 9
End With
Range("E7", "H" & b4c).Select
With Selection
   .Borders.LineStyle = xlContinuous
      .Borders.Weight = xlThin
      .Borders.ColorIndex = xlAutomatic
      .HorizontalAlignment = xlRight
      .Font.Name = "宋体"
      .Font.Size = 9
End With
End Sub

时间: 2013-5-9 11:29

作者: xp403815

因为我们是不加那个运杂费，保管费什么的，所以我就没有加，那个，如果你要加的话，也是可以的。加多两行代码就是了。

附件: 1.jpg (2013-5-9 11:29, 143.77 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=MjA5MzAzfDZiNjIxYjM5fDE3NTY3MzQ2NTN8MHww

附件: 2.jpg (2013-5-9 11:29, 89.34 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=MjA5MzA0fGU5NTllNGMxfDE3NTY3MzQ2NTN8MHww

附件: 3.jpg (2013-5-9 11:29, 119.38 KB) / 下载次数 0
https://www.txrjy.com/forum.php?mod=attachment&aid=MjA5MzA1fDBkNTZiZmY5fDE3NTY3MzQ2NTN8MHww

通信人家园 (https://www.txrjy.com/)