新手,自己写的VBA代码,运行效率太慢了。能不能缩减代码?或者用数组提高效率。- Sub head01()
- Dim b, c, d
- For i = 1 To [a65536].End(3).Row
- For j = 2 To 33
- If Mid(Cells(i, 1), 1, 1) = Cells(j, 6) Then
-
- Cells(i, 2) = Cells(j, 7) & Mid(Cells(i, 1), 2, 100)
- Else
-
- End If
-
- Next j
- Next i
-
- End Sub
- Sub transmdd01()
-
- For i = 1 To [a65536].End(3).Row
- For j = 2 To 25
-
- Select Case Mid(Cells(i, 1), j, 1)
-
-
- Case Cells(2, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(2, 8))
-
- Case Cells(3, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(3, 8))
-
- Case Cells(4, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(4, 8))
-
- Case Cells(5, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(5, 8))
-
- Case Cells(6, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(6, 8))
-
- Case Cells(7, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(7, 8))
-
- Case Cells(8, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(8, 8))
-
- Case Cells(9, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(9, 8))
-
- Case Cells(10, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(10, 8))
-
- Case Cells(11, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(11, 8))
- Case Cells(12, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(12, 8))
- Case Cells(13, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(13, 8))
-
- Case Cells(14, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(14, 8))
-
- Case Cells(15, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(15, 8))
-
- Case Cells(16, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(16, 8))
-
- Case Cells(17, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(17, 8))
-
- Case Cells(18, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(18, 8))
- Case Cells(19, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(19, 8))
-
- Case Cells(20, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(20, 8))
-
- Case Cells(21, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(21, 8))
-
- Case Cells(22, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(22, 8))
-
- Case Cells(23, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(23, 8))
-
- Case Cells(24, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(24, 8))
-
- Case Cells(25, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(25, 8))
-
- Case Cells(26, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(26, 8))
-
- Case Cells(27, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(27, 8))
-
- Case Cells(28, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(28, 8))
-
- Case Cells(29, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(29, 8))
-
- Case Cells(30, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(30, 8))
-
- Case Cells(31, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(31, 8))
- Case Cells(32, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(32, 8))
-
- Case Cells(33, 6)
-
- Cells(i, 2).Value = Replace(Cells(i, 2).Value, Mid(Cells(i, 2), j, 1), Cells(33, 8))
-
-
- End Select
- Next j
- Next i
- End Sub
- Sub spellcheck01()
- For i = 1 To 5200
- For j = 1 To 4200
- If Mid(Sheet1.Cells(i, 2), 1, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 1, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 2, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 2, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 3, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 3, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 4, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 4, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 5, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 5, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 6, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 6, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 7, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 7, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 8, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 8, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 9, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 9, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 10, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 10, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 11, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 11, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 12, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 12, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 13, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 13, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 14, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 14, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 15, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 15, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 16, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 16, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 17, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 17, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 18, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 18, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 19, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 19, 2), Cells(j, 13))
- Else
- End If
- If Mid(Sheet1.Cells(i, 2), 20, 2) = Sheet1.Cells(j, 12) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Mid(Sheet1.Cells(i, 2), 20, 2), Cells(j, 13))
- Else
- End If
- Next j
- Next i
- End Sub
- Sub spellcheck02()
- For i = 1 To [a65536].End(3).Row
- For j = 1 To [o65536].End(3).Row
-
- If Right(Sheet1.Cells(i, 2), 2) = Sheet1.Cells(j, 15) Then
-
- Sheet1.Cells(i, 2).Value = Replace(Sheet1.Cells(i, 2).Value, Right(Sheet1.Cells(i, 2), 2), Cells(j, 16))
- Else
- End If
-
- Next j
- Next i
- End Sub
复制代码 |