VBA使用案例1
Dim numb As Integer
Dim SDF As String
Dim XMMC As String
Dim ROW_New As Integer
Dim ROW_Page As Integer
Dim CVB As Integer
Dim ROW_Did As Integer
'********添加工位页***********
Sub Add_Page()
IPage = 21
For LP = 3 To IPage
SPage = Trim(Sheets("Area").Range("C" & LP).Value)
If SPage <> "" Then
Sheets("Seq1").Copy after:=Sheets(Sheets.Count - 2) //复制页
ActiveSheet.Name = "Seq" & LP - 1 //把页设置成当前页
For kW = 2 To 33
ActiveSheet.Range("A" & kW).Value = Replace(ActiveSheet.Range("A" & kW).Value, "1", LP - 1, 4, 1)
ActiveSheet.Range("A" & kW).Value = " + " & ActiveSheet.Range("A" & kW).Value
Next
For kQ = 2 To 65
ActiveSheet.Range("E" & kQ).Value = Replace(ActiveSheet.Range("E" & kQ).Value, "1", LP - 1, 4, 1)
ActiveSheet.Range("E" & kQ).Value = " + " & ActiveSheet.Range("E" & kQ).Value
Next
For kR = 2 To 97
ActiveSheet.Range("H" & kR).Value = Replace(ActiveSheet.Range("H" & kR).Value, "1", LP - 1, 4, 1)
ActiveSheet.Range("H" & kR).Value = " + " & ActiveSheet.Range("H" & kR).Value
Next
For kE = 2 To 97
ActiveSheet.Range("K" & kE).Value = Replace(ActiveSheet.Range("K" & kE).Value, "1", LP - 1, 4, 1)
ActiveSheet.Range("K" & kE).Value = " + " & ActiveSheet.Range("K" & kE).Value
Next
For kT = 2 To 33
ActiveSheet.Range("N" & kT).Value = Replace(ActiveSheet.Range("N" & kT).Value, "1", LP - 1, 4, 1)
ActiveSheet.Range("N" & kT).Value = " + " & ActiveSheet.Range("N" & kT).Value
Next
End If
Next
End Sub
Sub find()
'With ActiveSheet.UsedRange
'iEndRow = .Rows.Count + .Row - 1
'iEndColumn = .Columns.Count + .Column - 1
'End With
'********添加工位元件自带报警***********
Sheets("ForWard").Range("A:FK").NumberFormatLocal = "@"
ROW_Did = 2
CVB = 0
Sheets("ForWard").Range("A" & 1, "FM" & 2).Value = Sheets("BackWard").Range("A" & 1, "FM" & 2).Value
For SPage = 1 To Sheets.Count - 3
For SPage_num = 1 To 65536
If Sheets("ForWard").Range("A" & SPage_num).Value = "" Then
ROW_Page = SPage_num
GoTo pp
End If
Next
pp: iNum = 1000
For LL = 2 To iNum
SEQ = Trim(Sheets("Seq" & SPage).Range("B" & LL).Value)
If SEQ = "" Then
numb = LL - 3 + ROW_Page
GoTo KK
End If
Next
KK: CVB = CVB + 1
CC = 2
DFG = 0
DVP = 0
For HH = ROW_Page To numb
If Sheets("Seq" & SPage).Range("D" & CC).Value = "气缸" Then
'///////////////气缸添加
ROW_Cylder = ROW_Did
For pp = 1 To 16
ROW_New = ROW_Cylder + pp '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & pp + 2, "FM" & pp + 2).Value
DFG = 1 + DVP
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks." & Sheets("Seq" & SPage).Range("C" & CC).Value & ".Alarm" & "[" & DFG & "]" & Sheets("BackWard").Range("H" & pp + 2).Value 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks." & Sheets("Seq" & SPage).Range("C" & CC).Value & ".Alarm" & "[" & DFG & "]" & Sheets("BackWard").Range("H" & pp + 2).Value 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Seq" & SPage).Range("A" & CC).Value & "-" & pp 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & Sheets("Seq" & SPage).Range("B" & CC).Value & "-" & Sheets("BackWard").Range("U" & pp + 2).Value 'U列
Next
Else
'///////////////伺服添加
ROW_Sever = ROW_Did
For pp_S = 1 To 48
ROW_New_S = pp_S + ROW_Sever '实际的每一行
ROW_Did = ROW_New_S
Sheets("ForWard").Range("A" & ROW_New_S, "FM" & ROW_New_S).Value = Sheets("BackWard").Range("A" & pp_S + 23, "FM" & pp_S + 23).Value
Sheets("ForWard").Range("A" & ROW_New_S).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New_S).Value = "PLC.Blocks." & Sheets("Seq" & SPage).Range("C" & CC).Value & ".Alarm." & Sheets("BackWard").Range("H" & pp_S + 23).Value 'H列
Sheets("ForWard").Range("EB" & ROW_New_S).Value = "PLC.Blocks." & Sheets("Seq" & SPage).Range("C" & CC).Value & ".Alarm." & Sheets("BackWard").Range("H" & pp_S + 23).Value 'EB列
Sheets("ForWard").Range("W" & ROW_New_S).Value = Sheets("Seq" & SPage).Range("A" & CC).Value & "-" & pp_S 'W列
Sheets("ForWard").Range("U" & ROW_New_S).Value = Sheets("ForWard").Range("W" & ROW_New_S).Value & Sheets("Seq" & SPage).Range("B" & CC).Value & "-" & Sheets("BackWard").Range("U" & pp_S + 23).Value 'U列
Next
End If
CC = CC + 1
DVP = DVP + 1
Next
'////////////////////////////////////
'********添加工位报警***********
'***添加工位安全报警
ROW_Page = ROW_Did + 1
iNum_Seq_Safe = 1000
For LL = 2 To iNum_Seq_Safe
SEQ = Trim(Sheets("Seq" & SPage).Range("F" & LL).Value)
If SEQ = "" Then
numb_Seq_Safe = LL - 3 + ROW_Page
GoTo KK_Seq_Safe
End If
Next
KK_Seq_Safe: CVB = 1 + CVB
CC = 1
For HH_Seq_Safe = ROW_Page To numb_Seq_Safe
ROW_New = HH_Seq_Safe '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 19, "FM" & 19).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].SafetyAlarm[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].SafetyAlarm[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Seq" & SPage).Range("E" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " Seq" & SPage & Sheets("Seq" & SPage).Range("F" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域故障报警
ROW_Page = ROW_Did + 1
iNum_Seq_Alarm = 1000
For LL = 2 To iNum_Seq_Alarm
SEQ = Trim(Sheets("Seq" & SPage).Range("I" & LL).Value)
If SEQ = "" Then
numb_Seq_Alarm = LL - 3 + ROW_Page
GoTo KK_Seq_Alarm
End If
Next
KK_Seq_Alarm: CVB = 1 + CVB
CC = 1
For HH_Seq_Alarm = ROW_Page To numb_Seq_Alarm
ROW_New = HH_Seq_Alarm '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 20, "FM" & 20).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Alarm[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Alarm[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Seq" & SPage).Range("H" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " Seq" & SPage & Sheets("Seq" & SPage).Range("I" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域警告报警
ROW_Page = ROW_Did + 1
iNum_Seq_Warn = 1000
For LL = 2 To iNum_Seq_Warn
SEQ = Trim(Sheets("Seq" & SPage).Range("L" & LL).Value)
If SEQ = "" Then
numb_Seq_Warn = LL - 3 + ROW_Page
GoTo KK_Seq_Warn
End If
Next
KK_Seq_Warn: CVB = 1 + CVB
CC = 1
For HH_Seq_Warn = ROW_Page To numb_Seq_Warn
ROW_New = HH_Seq_Warn '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 21, "FM" & 21).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Warnning[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Warnning[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Seq" & SPage).Range("K" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " Seq" & SPage & Sheets("Seq" & SPage).Range("L" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域提示报警
ROW_Page = ROW_Did + 1
iNum_Seq_Prompt = 1000
For LL = 2 To iNum_Seq_Prompt
SEQ = Trim(Sheets("Seq" & SPage).Range("O" & LL).Value)
If SEQ = "" Then
numb_Seq_Prompt = LL - 3 + ROW_Page
GoTo KK_Seq_Prompt
End If
Next
KK_Seq_Prompt: CVB = 1 + CVB
CC = 1
For HH_Seq_Prompt = ROW_Page To numb_Seq_Prompt
ROW_New = HH_Seq_Prompt '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 22, "FM" & 22).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Prompt[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Seq[" & SPage & "].Prompt[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Seq" & SPage).Range("N" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " Seq" & SPage & Sheets("Seq" & SPage).Range("O" & CC + 1).Value 'U列
CC = CC + 1
Next
'////////////////////////////////////
Next
'********添加区域报警***********
'***添加区域安全报警
ROW_Page = ROW_Did + 1
iNum_Area_Safe = 1000
For LL = 2 To iNum_Area_Safe
SEQ = Trim(Sheets("Area").Range("F" & LL).Value)
If SEQ = "" Then
numb_Area_Safe = LL - 3 + ROW_Page
GoTo KK_Area1_Safe
End If
Next
KK_Area1_Safe: CVB = 1 + CVB
CC = 1
For HH_Area_Safe = ROW_Page To numb_Area_Safe
ROW_New = HH_Area_Safe '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 19, "FM" & 19).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].SafetyAlarm[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].SafetyAlarm[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Area").Range("E" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " 区域1 " & Sheets("Area").Range("F" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域故障报警
ROW_Page = ROW_Did + 1
iNum_Area_Alarm = 1000
For LL = 2 To iNum_Area_Alarm
SEQ = Trim(Sheets("Area").Range("I" & LL).Value)
If SEQ = "" Then
numb_Area_Alarm = LL - 3 + ROW_Page
GoTo KK_Area1_Alarm
End If
Next
KK_Area1_Alarm: CVB = 1 + CVB
CC = 1
For HH_Area_Alarm = ROW_Page To numb_Area_Alarm
ROW_New = HH_Area_Alarm '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 20, "FM" & 20).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Alarm[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Alarm[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Area").Range("H" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " 区域1 " & Sheets("Area").Range("I" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域警告报警
ROW_Page = ROW_Did + 1
iNum_Area_Warn = 1000
For LL = 2 To iNum_Area_Warn
SEQ = Trim(Sheets("Area").Range("L" & LL).Value)
If SEQ = "" Then
numb_Area_Warn = LL - 3 + ROW_Page
GoTo KK_Area1_Warn
End If
Next
KK_Area1_Warn: CVB = 1 + CVB
CC = 1
For HH_Area_Warn = ROW_Page To numb_Area_Warn
ROW_New = HH_Area_Warn '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 21, "FM" & 21).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Warnning[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Warnning[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Area").Range("K" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " 区域1 " & Sheets("Area").Range("L" & CC + 1).Value 'U列
CC = CC + 1
Next
'***添加区域提示报警
ROW_Page = ROW_Did + 1
iNum_Area_Prompt = 1000
For LL = 2 To iNum_Area_Prompt
SEQ = Trim(Sheets("Area").Range("O" & LL).Value)
If SEQ = "" Then
numb_Area_Prompt = LL - 3 + ROW_Page
GoTo KK_Area1_Prompt
End If
Next
KK_Area1_Prompt: CVB = 1 + CVB
CC = 1
For HH_Area_Prompt = ROW_Page To numb_Area_Prompt
ROW_New = HH_Area_Prompt '实际的每一行
ROW_Did = ROW_New
Sheets("ForWard").Range("A" & ROW_New, "FM" & ROW_New).Value = Sheets("BackWard").Range("A" & 22, "FM" & 22).Value
Sheets("ForWard").Range("A" & ROW_New).Value = CVB & ": Category " & CVB 'A列
Sheets("ForWard").Range("H" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Prompt[" & CC & "]" 'H列
Sheets("ForWard").Range("EB" & ROW_New).Value = "PLC.Blocks.SDP.Area[1].Prompt[" & CC & "]" 'EB列
Sheets("ForWard").Range("W" & ROW_New).Value = Sheets("Area").Range("N" & CC + 1).Value 'W列
Sheets("ForWard").Range("U" & ROW_New).Value = Sheets("ForWard").Range("W" & ROW_New).Value & " 区域1 " & Sheets("Area").Range("O" & CC + 1).Value 'U列
CC = CC + 1
Next
End Sub
Private Sub 刷新_Click()
If Sheets.Count > 3 Then
oPage = Sheets.Count
For lp = 4 To oPage
ePage = Sheets.Count
Application.DisplayAlerts = False //关闭提示框
Sheets(ePage).Delete //删除页
Application.DisplayAlerts = True
Next
End If
IPage = 150
For lp = 3 To IPage
SPage = Trim(Sheets("汇总").Range("G" & lp).Value)
////Range("列" & 行号)
If SPage <> "" Then
Sheets("自动化控制部通用绩效考核新标准-20241219").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = SPage
ActiveSheet.Range("A2").Value = SPage
Sheets("汇总").Hyperlinks.Add anchor:=Sheets("汇总").Cells(lp, 7), Address:="", SubAddress:="'" & SPage & "'!A1" //添加超链接
End If
Next
End Sub
Private Sub CommandButton1_Click()
Dim XMMC As String //添加声名
For LL = 1 To 600
rr = (LL - 1) * 20 + 1
XMMC = Trim(Sheets("Sheet1").Cells(2, rr).Value)
If XMMC = "IO-LINK主站" Then
PP = LL
Else
GoTo la
End If
Next
la:
For ZZ = 1 To PP
kk = (ZZ - 1) * 4 + 1
kp = (ZZ - 1) * 20 + 1
Sheets("Sheet2").Cells(1, kk).Value = Sheets("Sheet1").Cells(3, kp).Value
//CELLS(行，列）
For Zh = 1 To 150
kj = Zh + 2
kl = kp + 11
ky = kp + 13
kf = kk + 1
Sheets("Sheet2").Cells(Zh, kf).Value = Sheets("Sheet1").Cells(kj, kl).Value & Format(Sheets("Sheet1").Cells(kj, ky).Value, ".0")
Sheets("Sheet2").Cells(Zh, kf + 1).Value = Sheets("Sheet1").Cells(kj, kp + 18).Value
Sheets("Sheet2").Cells(Zh, kf + 2).Value = Sheets("Sheet1").Cells(kj, kp + 14).Value
Next
浙公网安备 33010602011771号