|
有一批文件,要实现以下功能,请高手指点,谢谢!
要实现功能: | 1、如G列有数据,则H=G,I=G-H,L=H*J | 2、如G列无数据,则跳过。 | 3、如C列为设备合计,则L=上面设备合计, | 4、如C列为材料合计,则L=上面材料合计, |
改了一下你的代码,请测试:
- Sub JGC()
- i = 1
- Do Until ThisWorkbook.ActiveSheet.Cells(i, 2) = ""
- Set dx_workbook = Workbooks.Open(ThisWorkbook.Path & "" & ThisWorkbook.ActiveSheet.Cells(i, 1))
- dx_workbook.Sheets("甲供材明细表").Columns("H:H").Hidden = False
- Sheets("甲供材明细表").Activate
- xrow = Sheets("甲供材明细表").Cells(Rows.Count, 2).End(xlUp).Row
- yrow = 5
- For h = 5 To xrow
- If InStr(Range("C" & h).Value, "合计") Then
- Range("L" & h).Value = "=SUM(L" & yrow & ":L" & h - 1 & ")"
- yrow = h + 1
- ElseIf Range("G" & h).Value <> "" Then
- Range("H" & h).Value = "=G" & h
- Range("I" & h).Value = "=G" & h & "-H" & h
- Range("L" & h).Value = "=H" & h & "*J" & h
- End If
- Next h
- i = i + 1
- dx_workbook.Close SaveChanges:=True
- Set dx_workbook = Nothing
- Loop
- End Sub
复制代码
|
|